ข้อมูลเบอร์โทรศัพท์เละเทะมากเลย อยากปรับฟอร์แมตให้ถูกตามหลักสากล ต้องเขียนสูตรยังไงคะ? จู่ๆน้องแนนก็โพล่งถามขึ้น

ที่ว่าเละ มันเละยังไงหรือครับ? ผมถามกลับ

คือบางเบอร์มีเลขศูนย์นำหน้า บางเบอร์ก็ไม่มีเลขศูนย์นำ บางเบอร์มีขีดกลางคั่น บางเบอร์ก็ไม่มีขีดกลางคั่น แถมบางเบอร์มีช่องว่างคั่นด้วยค่ะ?

ข้อมูลที่น้องแนนพูดถึง หน้าตาประมาณนี้ครับ

ผมเห็นแล้วแอบสูดลมหายใจด้วยความหนาวเหน็บ เพราะข้อมูลเละเทะจริงๆ แถมมีหลายแพทเทิร์นมากๆ
ถ้าลองคิดความเป็นไปได้ของแพทเทิร์นทั้งหมด แล้วเขียนสูตรโดยใช้ IF จะมี IF ซ้อนกันไม่รู้กี่ชั้น
ปัญหาของการเขียน IF ซ้อน IF หลายๆชั้นก็คือ ถ้าต้องแก้ไขสูตร บอกได้คำเดียว..
ร้องไห้หนักมาก T_T

แล้วจะเขียนสูตรยังไงดีน่ะหรือครับ?

ในมุมมองของผม การเขียนสูตรสำหรับเคสนี้มี 2 ขั้นตอน นั่นคือ
1. ปรับให้เหลือเฉพาะตัวเลข
2 ปรับฟอร์แมตตัวเลขให้ถูกตามหลักสากล

1 ปรับให้เหลือเฉพาะตัวเลข

แนวทางของวิธีนี้คือ ตัดขีดกลาง ( – , hyphen) กับช่องว่าง (space) ระหว่างตัวเลขทิ้งไป
ถ้าเขียนสูตรโดยใช้ฟังก์ชัน FIND หาตำแหน่งของขีดกลางกับช่องว่าง แล้วใช้ฟังก์ชัน MID ตัดจำนวนอักขระที่ต้องการ สูตรจะยาวมากกกก
วิธีที่ง่ายกว่าคือใช้ฟังก์ชัน SUBSTITUTE !

ฟังก์ชัน SUBSTITUTE ใช้ทำอะไร?
ฟังก์ชัน SUBSTITUTE มีความหมายตรงตามชื่อเลย นั่นคือ ใช้แทนที่ข้อความเก่า ด้วย ข้อความใหม่
หรือใช้แทนที่ ตัวอักษรเก่า ด้วย ตัวอักษรใหม่

แล้วจะใช้ SUBSTITUTE ตัดขีดกลางกับช่องว่างได้ยังไง?
ได้แน่นอนครับ เพราะจะใช้ SUBSTITUTE แทนที่ ขีดกลาง (“-“) กับช่องว่าง (” “) ด้วย ค่าว่าง (“”) นั่นเอง !

หมายเหตุ: อักขระ “” ไม่ใช่ค่าว่าง (null) แต่คือ ค่าเสมือนว่าง
อันที่จริงแล้ว “” คือข้อความ (text) ที่มีความยาว 0 อักขระ
เพื่อป้องกันความสับสน ผมขอเรียก “” ว่า ค่าเสมือนว่าง ครับ ^__^
(ค่าว่างจริงๆคือต้องว่างแบบไม่มีอะไรเลย)

มารู้จักโครงสร้างของฟังก์ชัน SUBSTITUTE กัน โครงสร้าง คือ

SUBSTITUTE( text, old_text, new_text, [instance_num] ) 

text คือ ข้อความหรือเซลล์ที่ต้องการเปลี่ยน เช่น เคสนี้คือ เซลล์ A2
old_text คือ คือ ข้อความ (หรืออักขระ) ที่ต้องถูกแทนที่ เช่น เคสนี้คือ ขีดกลาง (“-“) และ ช่องว่าง (” “)
new_text คือ ข้อความ (หรืออักขระ) ที่นำมาแทนที่ เช่น เคสนี้คือ ค่าเสมือนว่าง (“”)
[instance_num] คือ จำนวนครั้งที่ต้องการแทนที่ สามารถระบุได้ว่าต้องการแทนที่ old_text ด้วย new_text กี่ครั้ง (ในเซลล์ A2 อาจมีขีดกลางมากกว่า 1 ตัว)
ถ้าไม่ระบุ [instance_num] โปรแกรมจะแทนที่ old_text ด้วย new_text ทั้งหมด ซึ่งเคสส่วนใหญ่มักเป็นแบบนี้ และเคสนี้ก็เช่นกัน

มาแทนที่ ขีดกลาง ด้วย ค่าเสมือนว่าง กัน
เขียนสูตรได้เป็น

=SUBSTITUTE( A2, “-” ,”” )

ได้ผลลัพธ์หน้าตาแบบนี้

ไม่เลวเลยใช่ไหมครับ ^__^

จะพบว่ามีปัญหาเล็กน้อยคือ
บางเซลล์ยังไม่เป็นตัวเลขทั้งหมด ยังคงมีช่องว่าง (space) ปนอยู่ด้วย
เช่น เซลล์ B5, B6, B7, B10, B11, B12
การกำจัดช่องว่าง ก็คือการแทนที่ช่องว่าง (” “) ด้วย ค่าเสมือนว่าง (“”) นั่นเอง
หรือเขียนสูตรเป็น
=SUBSTITUTE( text , ” ” ,”” )

สำหรับเคสนี้ให้มองเป็นการใช้ SUBSTITUTE 2 รอบ
รอบแรกคือ แทนที่ “-” ด้วย “” ซึ่งทำเสร็จแล้ว ( SUBSTITUTE( A2, “-” ,”” ) )
รอบสองคือ แทนที่ ” ” ด้วย “”
ตอนทำรอบสอง ต้องนำผลลัพธ์จากรอบแรกมาใช้ด้วย จึงต้องใช้ SUBSTITUTE ซ้อนเข้าไป หรือเขียนสูตรเป็น

=SUBSTITUTE( SUBSTITUTE( A2, “-“, “” ), ” ” , “” )

ได้ผลลัพธ์หน้าตาแบบนี้

เหลือเฉพาะตัวเลขตามต้องการแล้ว ^__^

2 ปรับฟอร์แมตตัวเลขให้ถูกตามหลักสากล

ขั้นตอนนี้ง่ายมาก เพราะใช้ฟังก์ชันเดียวแบบตรงๆเลย นั่นคือ ฟังก์ชัน TEXT
ฟังก์ชัน TEXT ใช้ทำอะไร?
ฟังก์ชัน TEXT ใช้เปลี่ยนฟอร์แมตของตัวเลขให้เป็นตัวหนังสือ (text) ความเจ๋งคือกำหนดรูปแบบของตัวหนังสือตามต้องการได้

มารู้จักฟังก์ชัน TEXT กันนิดนึงครับ โครงสร้างคือ

TEXT( value, format_text )

value คือ ตัวเลขที่ต้องการถูกเปลี่ยนฟอร์แมต
format_text คือ รูปแบบตัวหนังสือที่ต้องการ

ถ้ารูปแบบของเบอร์โทรที่ต้องการคือ 08 5345 9812 ให้กำหนด format_text เป็น “00 0000 0000”
หรืออาจเขียนสูตรเป็น
=TEXT( value, “00 0000 0000” )

value ในที่นี้คือก็คือ SUBSTITUTE( SUBSTITUTE( A2, “-“, “” ), ” “, “” ) พอเขียนสูตรแบบเต็มๆจะได้เป็น

=TEXT( SUBSTITUTE(SUBSTITUTE(A2,”-“,””), ” “,””),”00 0000 0000″)

“ได้จริงๆด้วย !! ขอบคุณมากๆเลยค่า” น้องแนนโห่ร้องด้วยความดีใจ

แล้วถ้าข้อมูลมีเบอร์โทรศัพท์บ้านด้วย เช่น 02-247-9841 จะปรับสูตรยังไงดีคะ? น้องแนนเริ่มเกิดความคิดสร้างสรรค์

แล้วถ้าข้อมูลมีเบอร์โทรศัพท์ของต่างจังหวัดด้วย เช่น 038-213-445 จะปรับสูตรยังไงดีคะ? ความคิดสร้างสรรค์เริ่มหลั่งไหล

แล้วถ้าต้องการปรับฟอร์แมตเบอร์โทรศัพท์ให้ขึ้นต้นด้วยรหัสประเทศไทย (+66) จะได้ใช้ในนามบัตร ต้องปรับสูตรยังไงคะ? ความต้องการที่แท้จริงเริ่มพรั่งพรู

แนวทางเขียนสูตรยังคงเป็น 2 ขั้นตอนครับ
1 ปรับให้เหลือเฉพาะตัวเลขและไม่มี 0 นำ
2 ปรับฟอร์แมตตัวเลข

1 ปรับให้เหลือเฉพาะตัวเลขและไม่มี 0 นำ

ถ้าข้อมูลขึ้นต้นด้วย 0 (เช่น 089-347-7987) เมื่อใช้สูตร =SUBSTITUTE( SUBSTITUTE( A2, “-“, “” ), ” “, “” ) จะพบว่าผลลัพธ์ยังคงมีศูนย์นำ
การปรับให้เหลือเฉพาะตัวเลขแบบไม่มีศูนย์นำนั้นง่ายมาก เทคนิคคือ เปลี่ยนข้อมูลให้เป็นตัวเลขนั่นเอง !
และเทคนิคการเปลี่ยนข้อมูลให้เป็นตัวเลขที่ง่ายที่สุดก็คือ การใส่เครื่องหมาย — (เครื่องหมายลบติดกันสองอัน) นำหน้าสูตร
หรือเขียนสูตรเป็น

=–SUBSTITUTE( SUBSTITUTE( A2,”-“,””), ” “, “”)

ง่ายสุดๆใช่ไหมล่ะ ^^

2 ปรับฟอร์แมตตัวเลข

ถ้าข้อมูลมีเบอร์บ้านหรือเบอร์ต่างจังหวัด จะมีความซับซ้อนเล็กน้อย
เพราะฟอร์แมตของเบอร์บ้านหรือเบอร์ต่างจังหวัดตามหลักสากลคือ +66 XXXX XXXX
เช่น +66 2247 9841, +66 3821 3445

แต่ฟอร์แมตของเบอร์มือถือตามหลักสากลคือ +66X XXXX XXXX เช่น +668 9345 7421

แล้วจะแยกได้ยังไงว่า ข้อมูลไหนเป็นเบอร์บ้าน ข้อมูลไหนเป็นเบอร์มือถือ?
จำนวน
ไงครับ ^__^

ถ้าเบอร์ไหนมี 9 ตัว เบอร์นั้นคือเบอร์มือถือ ถ้าไม่ใช่ คือเบอร์บ้าน เช่น
=IF( LEN(B2) = 9, ฟอร์แมตเบอร์มือถือ, ฟอร์แมตเบอร์บ้าน )

หรือเขียนสูตรเป็น

=”+66″&IF( LEN(B2)=9, TEXT( B2, “0 0000 0000″), TEXT( B2, ” 0000 0000″) )

ง่ายกว่าที่คิดใช่ไหมล่ะ ^__^

“ง่ายจริงๆด้วยค่ะ” น้องแนนดีใจหนักมาก

“แล้วถ้าข้อมูลมีเบอร์ภายในที่ต้องกดด้วย เช่น 02-634-7821 Ext 123 จะเขียนสูตรยังไงคะ?”
เป็นคำถามที่ดีมากครับ และถ้าลองคิดให้ดี จะมีเคสอื่นๆด้วย เช่น

ถ้ากรอกเบอร์โทรศัพท์ไม่ครบ เช่น จาก 081-345-6789 แต่กรอกเป็น 081-345-689 จะทำยังไง?
– ถ้ามีข้อมูลขึ้นต้นด้วยรหัสประเทศไทย เช่น +66-2457-3419 จะเขียนสูตรยังไง?
– ถ้ามีข้อมูลเบอร์ต่างประเทศด้วย เช่น +61 439 392 368 จะเขียนสูตรยังไง?
ฯลฯ

เคสเหล่านี้มีเกิดโอกาสขึ้นได้ทั้งหมด และสูตรที่ดีต้องคำนึงถึงเคสหรือเงื่อนไขให้ครอบคลุม
แน่นอนว่ายิ่งมีเงื่อนไขมาก สูตรก็ยิ่งซับซ้อนขึ้นเป็นเงาตามตัว
ผมคงไม่สามารถอธิบายสูตรสำหรับทุกเคสได้ แต่ผมจะบอก “อาวุธ” สำหรับการเขียนสูตรที่ใช้ได้กับทุกเคส ไม่ว่าเคสนั้นจะซับซ้อนเพียงใดก็ตาม

“อาวุธ” ที่ว่า ก็คือ
1 ตรรกะ
2 ความเข้าใจฟังก์ชัน Excel
(รวมทั้งรู้จักฟังก์ชันมากพอ)

อาวุธทั้งสองนี้ต้องไปด้วยกัน ถ้าตรรกะดีแต่ไม่เข้าใจฟังกชัน ก็เขียนสูตรไม่ได้
ถ้าเข้าใจฟังก์ชัน แต่ตรรกะไม่ดี ก็เขียนสูตรไม่ได้

แต่ถ้าตรรกะดี และเข้าใจฟังก์ชัน ต่อให้เคสยากแค่ไหน ซับซ้อนเพียงใด ก็ไม่ใช่ปัญหา
ยิ่งเจอเคสยากๆ ยิ่งดี เพราะได้ลับอาวุธให้แหลมคมขึ้นเรื่อยๆ

ยิ่งถ้าเจอเคสยากมาก และเขียนสูตรได้เมื่อใดล่ะก็ …
มัน “ฟิน” แบบสุดๆเลยล่ะครับ

อยากรู้ว่าอารมณ์ “ฟิน” แบบนี้มันเป็นยังไง
ต้องลองถึงจะรู้ครับ ^__^

สำหรับใครที่สนใจไฟล์ตัวอย่างของบทความนี้ สามารถดาวน์โหลดเพื่อศึกษาเพิ่มเติมได้เลยครับ

.
หากคุณชอบบทความแนวนี้ สามารถอัพเดตบทความใหม่ๆโดยคลิก Like เฟสบุ๊คแฟนเพจ วิศวกรรีพอร์ต หรือคลิก ที่นี่
อย่าลืมแชร์ให้เพื่อนอ่านเพื่อเป็นกำลังใจให้คนเขียนด้วยนะครับ ^__^

อ้างอิงหลักการเขียนเบอร์โทรศัพท์แบบสากลจาก:
http://oknation.nationtv.tv/blog/Pasakorn/2010/11/04/entry-1