วิธีแยกข้อมูลตัวอักษร และตัวเลขที่อยู่ Cell เดียวกันออกจากกัน ​​​​​​​ในโปรแกรม Excel

วิธีแยกข้อมูลตัวอักษร และตัวเลขที่อยู่ Cell เดียวกันออกจากกัน
ในโปรแกรม Excel

ก่อนหน้านี้เราเคยนำเสนอเทคนิค วิธีแยกชื่อ-นามสกุล ที่อยู่ใน Cell เดียวกัน ออกเป็น 2 Cells ได้อย่างรวดเร็ว ใน โปรแกรม Excel ที่เป็น โปรแกรมสเปรดชีต (Spreadsheet Software) ชื่อดัง กันไปก่อนแล้ว ครั้งนี้ เราก็มีเทคนิคในการแยกข้อมูลที่มีรูปแบบเป็นอีกลักษณะมาแนะนำเช่นกัน โดยในบทความนี้จะเป็นเทคนิคการแยกข้อมูลที่มีตัวอักษร และตัวเลข รวมอยู่ภายในเซลล์ (Cell) ออกจากกัน เช่นข้อมูลสินค้า กับรหัสสินค้า, ชื่อสถานที่ และรหัสไปรษณีย์ ฯลฯ

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

1. ใช้คุณสมบัติ Text to Columns

สำหรับวิธีการแรก เราจะใช้ "คุณสมบัติ Text to Columns" คล้ายกับการ แยกชื่อ-นามสกุล ที่อยู่ใน Cell เดียวกัน ออกเป็น 2 Cells ที่เราเคยนำเสนอไปแล้ว แต่จะเปลี่ยนรูปแบบการตั้งค่าเล็กน้อย

ก่อนอื่น ให้เราคลิกเลือก Cell ข้อมูลที่ต้องการจะแยกให้เรียบร้อย จากนั้นคลิกที่ "เมนู Data" ในแถบเมนูด้านบนสุด แล้วคลิกที่ "ไอคอน Text to Columns"

โดย "หน้าต่าง Convert Text to Columns Wizard" จะปรากฏขึ้นมา ให้เราคลิกเลือก "O Delimited" แล้วตามด้วย "ปุ่ม Next"

หน้าจอถัดมา เราจะเลือกหลักเกณฑ์ที่ใช้ในการแยกข้อมูล จุดนี้เราต้องพิจารณาว่า "ตัวอักษร" และ "ตัวเลข" มีอะไรที่เป็นตัวคั่นข้อมูลอยู่ อย่างในกรณีของไฟล์ที่เราใช้สาธิตในบทความนี้ จะมีการแยกข้อมูลด้วยเครื่องหมายยัติภังค์ (-) ซึ่งไม่มีในตัวเลือก ดังนั้น ให้เราคลิกเลือกที่ "☑ Other:" แล้วพิมพ์เครื่องหมายยัติภังค์ลงไปในช่องด้านหลัง แล้วคลิก "ปุ่ม Next"

ตรง "เมนู Destination" เราสามารถเปลี่ยนตำแหน่ง Cell ที่จะวางข้อมูลได้ ซึ่งถ้าไม่เลือก ข้อมูลจะถูกวางให้ใน Column ถัดไป ซึ่งผู้เขียนเลือกเปลี่ยนเป็นให้เริ่มจาก คอลัมน์ (Column) B ที่อยู่แถว (Row) 2 แทน ก็เลยตั้งค่าเป็น "=$B$2" แล้วคลิกที่ "ปุ่ม Finish" ก็จะได้ผลลัพธ์ออกมาดังภาพด้านล่างนี้

2. ใช้คุณสมบัติ Flash Fill 

วิธีนี้เป็นวิธีที่ค่อนข้างง่าย และอาจจะเป็นวิธีการที่เร็วที่สุดในการแยกข้อมูลตัวอักษร และตัวเลขออกจากกัน โดย Flash Fill จะเติมข้อมูลลงใน Column ให้อัตโนมัติ โดยอ้างอิงจากตัวอย่างข้อมูลที่คุณเลือกไว้ให้อย่างชาญฉลาด มาอ่านวิธีการใช้ "คุณสมบัติ Flash Fill" กัน

ให้คลิกที่ Column ถัดจาก Column ที่มีข้อมูลอยู่ จากนั้นให้พิมพ์ "ข้อความส่วนแรก" ของข้อมูลที่คุณต้องการจะแยกออกมา แล้วกด "ปุ่ม Enter" จากไฟล์ตัวอย่างที่เรานำมาสาธิต ก็จะเป็นคำว่า "คลองเตย คลองตัน" และ "10110" คลิกที่ "เมนู Home" ในแถบเมนูด้านบนสุด แล้วคลิกที่ "ไอคอน Fill" ตามด้วย "เมนู Flash Fill"

เท่านั้นเองเลย ง่ายใช่ไหมล่ะ ^_^

3. ใช้ฟังก์ชัน LEFT, RIGHT และ SEARCH ในการแยกข้อมูล

การแยกข้อมูลภายใน Cell หากคุณเป็นผู้ชำนาญการใช้สูตรฟังก์ชันก็เป็นเรื่องง่าย แต่ถ้าไม่ค่อยได้ใช้ ไม่รู้สูตรก็ไม่เป็นไร Copy สูตรที่เราแจกไปใช้เลยก็ได้เหมือนกัน แต่เราจะขออนุญาตการใช้สูตรไว้สักเล็กน้อย เพื่อให้ง่ายต่อการนำไปใช้งาน

สูตรที่เราจะใช้ในการแยกชื่อเขต และแขวงออกมา คือ "=LEFT(A2,SEARCH("-",A2)-2)" โดยจาก ข้อความที่อยู่ใน Cell A2 ซึ่งเป็น Cell ที่อยู่ด้านซ้าย (LEFT) ของ Cell ที่เราจะใส่สูตร

หากข้อมูลของคุณอยู่ใน Cell ตำแหน่งอื่น ก็ปรับสูตรเปลี่ยน "A2" เป็นตำแหน่งที่มีข้อมูลอยู่แทน และหากสัญลักษณ์ที่ใช้แบ่งแยกข้อความไม่ใช้เครื่องหมายยัติภังค์ก็อย่าลืมเปลี่ยนมันด้วยล่ะ

ในส่วนของรหัสไปรษณีย์ เราจะใช้สูตร "=RIGHT(A2,LEN(A2)-SEARCH("-",A2)-1)" เช่นเดียวกับเขต และแขวง หากข้อมูลของคุณอยู่ใน Cell ตำแหน่งอื่น ก็ปรับสูตรเปลี่ยน "A2" เป็นตำแหน่งที่มีข้อมูลอยู่แทน

ผลลัพธ์ที่ได้ก็จะเป็นดังภาพด้านล่างนี้ ที่เหลือก็แค่ลากสูตรไว้ให้ครบทุก Cell ที่เราต้องการ เป็นอันเรียบร้อย

4. ใช้เครื่องมือ VBA (Visual Basic for Applications)

Visual Basic for Applications (VBA) เป็นเครื่องมือที่ช่วยสนับสนุนการพัฒนาโปรแกรม (Programming Tool) ตัวหนึ่งที่ทาง Microsoft ใส่เข้ามาใน Excel สำหรับใช้เขียนฟังก์ชันที่ต้องการขึ้นมาใช้งานตามความต้องการได้ แน่นอนว่าการแยกข้อมูลก็สามารถใช้ VBA ช่วยได้เช่นกัน

ก่อนอื่นเราจะลบตัวคั่น (Delimiter) ออกจากข้อมูลออกเสียก่อน เพื่อให้ฟังก์ชันสามารถทำงานได้ผลลัพธ์ตรงกับการคำนวณของฟังก์ชัน โดยให้เราคลิกเลือก Cell ที่มีข้อมูลอยู่ แล้วกด "ปุ่ม Ctrl + H" คลิกที่ "เมนู Replace" ค้นหา "-" ใน "ช่อง Replace with" ก็ให้พิมพ์เว้นวรรค (เคาะ "ปุ่ม Spacebar" จำนวน 1 ครั้ง) แล้วคลิก "ปุ่ม Replace All"

เราจะได้ผลลัพธ์ดังภาพด้านล่างนี้ เครื่องหมายยัติภังค์หายไปแล้ว เพราะถูกแทนที่ด้วยเว้นวรรค

ในแถบเมนูด้านบนคลิกที่ "เมนู File" → ตามด้วย "เมนู More..." แล้วเลือก "เมนู Options" จากนั้นในพาเนลด้านซ้ายให้เราคลิกที่ "เมนู Customize Ribbon" คลิก "☑ Developer" แล้วคลิก "ปุ่ม OK" เพื่อยืนยันการตั้งค่า

ในแถบเมนูด้านบนคลิกที่ "แท็บเมนู Developer" แล้วคลิกที่ "ไอคอน Visual Basic"

โดย "หน้าต่าง Microsoft Visual Basic for Applications" จะถูกเปิดขึ้นมา คลิกที่ "เมนู Insert" ตามด้วย "เมนู Module" คัดลอกโค้ด้านล่างนี้ไปวางไว้ใน "หน้าต่าง Module"

Public Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String Dim xLen As Long Dim xStr As String xLen = VBA.Len(pWorkRng.Value) For i = 1 To xLen xStr = VBA.Mid(pWorkRng.Value, i, 1) If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then SplitText = SplitText + xStr End If Next End Function

จะได้ผลลัพธ์ดังภาพด้านล่างนี้ เสร็จแล้วให้กดปิด "หน้าต่าง VBA" ไปได้เลย เพื่อกลับสู่ Sheet ที่เรากำลังทำงานอยู่

ใส่สูตรลงใน Cell หลังข้อมูลที่ต้องการแยกว่า "=SplitText(A2,FALSE)" เราจะได้ชื่อเขต และแขวงมา ในส่วนของรหัสไปรษณีย์ให้ใช้สูตร "=SplitText(A2,TRUE)" ก็จะได้ผลลัพธ์ดังภาพด้านล่างนี้

ทั้งหมดนี่ก็เป็น 4 วิธี ที่สามารถใช้ในการแยกข้อมูลตัวอักษร และตัวเลขที่รวมกันอยู่ภายใน Cell เดียวออกจากกัน หวังว่าจะมีสักวิธีที่ช่วยให้คุณผู้อ่านสามารถทำงานบน โปรแกรม Excel ได้ง่ายขึ้นนะครับ