การทำงานกับข้อความประเภทต่างๆ ใน Excel เป็นเรื่องปกติ แต่บางครั้งต้องแยกเพื่อความเข้าใจที่ดีกว่า บทความนี้จะเรียนรู้การแยกข้อความใน Excel ด้วย 8 วิธีประยุกต์
ดาวน์โหลดไฟล์ตัวอย่างมาลองทำเอง
8 วิธีประยุกต์แยกข้อความใน Excel
เพื่อการสาธิต นี่คือชุดข้อมูลข้อมูลชื่อและที่อยู่ (Name & Address)ของ 5 คนในประเทศต่างๆ
ติดตามตัวอย่างด้านล่างเพื่อแยกข้อความในชุดข้อมูลนี้
1. ใช้ Text to Column Wizard แยกข้อความ
วิธีแรก แนะนำการแยกข้อความด้วยText to Column WizardระบุDelimiter ทำตามขั้นตอน:
- ไปแท็บDataเลือกText to Columnsภายใต้กลุ่มData Tools
- เลือกตัวเลือกDelimitedในขั้นตอนแรกของText to Column WizardกดNext
- ขั้นตอนที่สอง เลือกDelimitersเป็นCommaกดNext
- ขั้นตอนสุดท้าย ระบุDestinationเป็นเซลล์ C5กดFinish
- เสร็จแล้ว ได้ข้อความที่แยกแล้ว
2. แยกข้อความด้วย Flash Fill
Flash Fillเป็นตัวเสริมที่ดีของColumn Wizardสำหรับแยกข้อความ ขั้นตอน:
- พิมพ์ส่วนแรกของข้อความในเซลล์ C5
- ไปแท็บDataเลือกFlash Fillภายใต้Data Tools
- ตามนั้น ช่วงเซลล์ C6:C9จะเติมอัตโนมัติด้วยส่วนแรกของข้อความจากแหล่งที่มาแต่ละอัน
- ทำขั้นตอนคล้ายกัน จะได้ผลลัพธ์สุดท้าย
3. รวม LEFT, RIGHT, FIND & LEN แยกข้อความ
อีกวิธีช่วยแยกข้อความคือรวมLEFT, RIGHT, FIND & LEN functions ทำงานยังไง:
- ใส่สูตรในเซลล์ C5
=LEFT(B5,FIND(",",B5)-1)
- กดEnter
- ใช้AutoFillได้ผลลัพธ์
ที่นี่ใช้LEFTดึงข้อมูลจากด้านซ้ายสุดของเซลล์ ตามด้วยFINDหาตำแหน่งข้อความในเซลล์ B5
- ใส่สูตรในเซลล์ D5
=RIGHT(B5,LEN(B5)-FIND(",",B5))
- กดEnter
- ใช้AutoFillได้ข้อความแยกในช่วง D5:D9
ที่นี่ใช้RIGHTดึงข้อความจากด้านขวาของเซลล์ B5 ใช้LENกำหนดความยาวสตริง สุดท้ายFINDหาสตริงเฉพาะ
4. ใช้ฟังก์ชัน TRANSPOSE แยกข้อความ
ฟังก์ชัน TRANSPOSEช่วยแยกข้อความได้ ขั้นตอน:
- ใส่สูตรในเซลล์ C5
=TRANSPOSE(FILTERXML(""&SUBSTITUTE(B5,",","") & "","//s"))
- กดEnter
- ข้อความจะแบ่งอัตโนมัติเป็นเซลล์ C5, D5, E5
ที่นี่ใช้TRANSPOSEสร้างอาร์เรย์สำหรับแยกข้อความ ใส่FILTERXMLดึงรายการจากเซลล์ B5 ใช้SUBSTITUTEแทนที่ข้อความตามชุดข้อมูล
- ใช้Fill Handleไปเซลล์ถัดไป
=FILTERXML(""&SUBSTITUTE(B3,",","")&"", "//s")
5. ใช้ Excel VBA แยกข้อความ
Excel VBAเป็นโซลูชันยอดเยี่ยมสำหรับแยกข้อความ ขั้นตอนง่าย:
- ไปแท็บDeveloperเลือกVisual BasicจากCode
- เลือกModuleจากInsert
- ใส่โค้ดในหน้าว่าง:
Sub SplitCellText() Dim MyAry() As String, Cnt As Long, j As Variant For n = 5 To 9 MyAry = Split(Cells(n, 2), ",") Cnt = 3 For Each j In MyAry Cells(n, Cnt) = j Cnt = Cnt + 1 Next j Next n End Sub
- คลิกRun SubหรือกดF5
- คลิกRunในหน้าต่างMacros
- สุดท้าย แยกข้อความใน Excel สำเร็จ
6. ใช้ Power Query แยกข้อความ
อีกวิธีมีประโยชน์คือใช้Power Queryใน Excel เครื่องมือนี้ช่วยนำเข้าและเชื่อมต่อสมุดงานภายนอก และปรับรูปตามต้องการ ทำตามขั้นตอน:
- ไปแท็บDataเลือกGet Data
- เลือกFrom Fileแล้วFrom Excel Workbookจากเมนูบริบท
- เลือกเวิร์กชีตPower QueryในรายการNavigator
- กดTransform Data
- ในหน้าต่าง ไปแถบTransformคลิกSplit Column
- เลือกBy Delimiterจากdrop-down
- จะไปกล่องโต้ตอบSplit Column by Delimiter
- เลือก delimiter เป็นComma
- เลือกตัวเลือกEach occurrence of the delimiterเพื่อSplit at
- กดOK
- กดClose & Load
- สุดท้าย จะเห็นเวิร์กชีตใหม่ที่ข้อความถูกแยกแล้ว
- คุณสามารถปรับตารางนี้ตามต้องการภายหลัง
7. แยกข้อความด้วย TEXTBEFORE & TEXTAFTER
วิธีสุดท้าย อธิบายกระบวนการด้วยTEXTBEFOREและTEXTAFTERในชุดข้อมูลใหม่ ที่นี่แสดงข้อมูลชื่อเต็ม 5 คนในช่วงเซลล์ B5:B9 ทำตามขั้นตอน:
- ใส่สูตรในเซลล์ C5กดEnter
=TEXTBEFORE(B5," ")
ที่นี่TEXTBEFOREใช้คืนข้อความในเซลล์ B5 ก่อน delimiter
- ใส่สูตรในเซลล์ D5กดEnter
=IFERROR(TEXTBEFORE(TEXTAFTER(B5," ")," ",-1),"")
ที่นี่IFERRORคืนค่าเมื่อสูตรมีข้อผิดพลาด ตามด้วยTEXTBEFOREและTEXTAFTERคืนข้อความก่อน/หลัง delimiter
- ใส่สูตรในเซลล์ E5
=TEXTAFTER(B5," ",-1)
ที่นี่TEXTAFTERคืนข้อความในเซลล์ B5 หลัง delimiter
- จะได้ผลลัพธ์ของเซลล์ B5
- ใช้Fill Handleได้ผลลัพธ์สุดท้าย
8. แยกข้อความจาก Line Break
ส่วนนี้อภิปรายมุมมองใหม่ของการแยกข้อความ: แยกจาก line break ขั้นตอน:
- ใส่สูตรในเซลล์ C5
=LEFT(B5, SEARCH(CHAR(10),B5,1)-1)
ที่นี่ใช้LEFTดึงค่าด้านซ้าย SEARCHหาตำแหน่งเป็นตัวเลขในสตริง CHARคืนอักขระจากตัวเลขในเซลล์ B5
- ใส่สูตรในเซลล์ D5
=MID(B5, SEARCH(CHAR(10),B5) + 1, SEARCH(CHAR(10),B5,SEARCH(CHAR(10),B5)+1) - SEARCH(CHAR(10),B5) - 1)
ที่นี่MIDดึงค่าจากบรรทัดกลางในเซลล์ B5 SEARCHหาตำแหน่ง CHARคืนอักขระจากตัวเลข
- ใส่สูตรในเซลล์ E5
=RIGHT(B5,LEN(B5) - SEARCH(CHAR(10), B5, SEARCH(CHAR(10),B5) + 1))
ที่นี่RIGHTดึงค่าด้านขวา SEARCHหาตำแหน่ง LENกำหนดความยาว CHARคืนอักขระจากตัวเลขในเซลล์ B5
- จนถึงตอนนี้ ผลลัพธ์ของเซลล์ B5
- ทำขั้นตอนเดียวกัน จะได้ชุดข้อความแยกสุดท้าย
วิธีแยกตัวเลขจากข้อความใน Excel
ภาพต่อไปแสดงเวิร์กชีทมีตัวเลขและข้อความในคอลัมน์เดียว เป้าหมายดึงส่วนตัวเลขจากแต่ละเซลล์ใส่คอลัมน์แยกด้วยFlash Fill
- ใส่ตัวเลขของเซลล์ B5ในเซลล์ C5
- คลิกเซลล์ถัดไป
- ไปแท็บDataเลือกFlash Fill
- เสร็จแล้ว ได้ตัวเลขทั้งหมดทีเดียว
สรุป
จนถึงตอนนี้ อภิปรายกระบวนการแยกข้อความใน Excel ด้วย 8 การประยุกต์ ลองทำและแจ้งความคิดเห็น ติดตามExcelDemyสำหรับบล็อก Excel เพิ่มเติม
