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

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

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

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

ผมเห็นแล้วสูดลมหายใจด้วยความหนาวเหน็บ เพราะเละเทะจริง ๆ แถมมีหลายแพทเทิร์น

ถ้าลองคิดความเป็นไปได้ของแพทเทิร์นทั้งหมด แล้วเขียนสูตรโดยใช้ 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