การแปลงข้อความเป็นคอลัมน์ เป็นงานทั่วไปในชีวิตประจำวันของเราอย่างแน่นอน ในสถานการณ์นับไม่ถ้วน เราต้องแยกข้อความออกเป็นคอลัมน์ และนี่คือจุดที่ Microsoft Excel เป็นเลิศ
ในบทความนี้ เราจะสำรวจสาระสำคัญทั้งหมดของวิธีแปลงข้อความเป็นคอลัมน์ที่มีตัวคั่นหลายตัวใน Excel .
6 วิธีในการแปลงข้อความเป็นคอลัมน์ด้วยตัวคั่นหลายตัวใน Excel
ในโอกาสนี้ สมมติว่า รายการขายดี ชุดข้อมูลที่แสดงใน B4:B13 เซลล์ที่มี ชื่อหนังสือ ผู้แต่ง และประเภท คอลัมน์. ในสถานการณ์นี้ เราต้องการแยก ชื่อหนังสือ , ผู้เขียน , และ ประเภท ลงในคอลัมน์ที่แยกจากกัน ดังนั้น เรามาสำรวจแต่ละวิธีกันโดยไม่ชักช้า
ที่นี่ เราได้ใช้ Microsoft Excel 365 คุณสามารถใช้เวอร์ชันอื่นได้ตามสะดวก
วิธีที่ 1 :การใช้คุณลักษณะข้อความเป็นคอลัมน์
ก่อนอื่น เราจะเริ่มด้วย Text to Column . ของ Excel คุณลักษณะซึ่งเป็นเครื่องมือที่สะดวกสำหรับการแปลงข้อความเป็นคอลัมน์ที่มีตัวคั่นหลายตัว ดังนั้นเรามาสังเกตและเรียนรู้เกี่ยวกับขั้นตอนในขั้นตอนที่แสดงด้านล่าง
📌 ขั้นตอน :
- ที่จุดเริ่มต้น เลือก B5:B13 เซลล์>> ไปที่ ข้อมูล แท็บ>> คลิก ข้อความเป็นคอลัมน์ ตัวเลือก
ทันที แปลงข้อความเป็นคอลัมน์ วิซาร์ดปรากฏขึ้น
- จากนั้น เลือก ตัวคั่น ตัวเลือก>> กด ถัดไป ปุ่ม.
- ในขณะนี้ ให้ใส่เครื่องหมายถูกสำหรับ จุลภาค ตัวคั่น>> กดปุ่ม ถัดไป ปุ่ม.
- ในทางกลับกัน ให้ป้อน ปลายทาง เซลล์ตามที่คุณต้องการ นี่คือ C5 เซลล์>> คลิกปุ่ม เสร็จสิ้น ปุ่ม.
- ตอนนี้ คำเตือนอาจปรากฏขึ้น ในกรณีนี้ ให้คลิกปุ่ม ตกลง ปุ่ม.
ต่อมา ผลลัพธ์สุดท้ายควรมีลักษณะเหมือนภาพหน้าจอด้านล่าง
อ่านเพิ่มเติม: วิธีใช้ฟีเจอร์ข้อความเป็นคอลัมน์พร้อม Carriage Return ใน Excel
วิธีที่ 2 :การใช้ฟังก์ชัน TRIM, MID, SUBSTITUTE, REPT และ LEN
ประการหนึ่ง ฟังก์ชันคือส่วนสำคัญของสเปรดชีต Excel และที่นี่เราจะรวม TRIM , กลาง , ทดแทน , REPT และ LEN ทำหน้าที่แยกข้อความที่มีตัวคั่นหลายตัวเป็นคอลัมน์ ที่นี่ LEN ฟังก์ชันส่งคืนความยาวของสตริง และ REPT ฟังก์ชั่นทำซ้ำข้อความ ต่อไป แทนที่ ฟังก์ชันแทนที่ข้อความเก่าด้วยข้อความใหม่ในขณะที่ MID ฟังก์ชั่นส่งคืนอักขระภายในข้อความ สุดท้าย TRIM ฟังก์ชันลบช่องว่างเพิ่มเติม
📌 ขั้นตอน :
- ขั้นแรก ย้ายไปที่ C6 เซลล์>> ป้อนสมการที่กำหนดด้านล่าง
=TRIM(MID(SUBSTITUTE($B6,",",REPT(" ",LEN($B6))),(C$5-1)*LEN($B6)+1,LEN($B6)))
ที่นี่ B6 และ C5 เซลล์อ้างอิงถึง ชื่อหนังสือ ผู้แต่ง ประเภท คอลัมน์ และตัวเลข 1 .
รายละเอียดสูตร:
- LEN($B6) → ส่งกลับจำนวนอักขระในสตริงข้อความ ที่นี่ เซลล์ B6 เป็น ข้อความ อาร์กิวเมนต์ที่ให้ค่า 43 .
- เอาต์พุต → 43 ” “
- REPT(” “,LEN($B6)) → กลายเป็น
- REPT(” “,43) → ทำซ้ำข้อความตามจำนวนที่กำหนด ที่นี่ ” “ เป็น ข้อความ อาร์กิวเมนต์ที่อ้างถึง ว่างเปล่า พื้นที่ในขณะที่ 43 เป็นอาร์กิวเมนต์ number_times ที่สั่งให้ฟังก์ชันแทรก 43 ว่างเปล่าซ้ำๆ
- ผลลัพธ์ → ” “
- SUBSTITUTE($B6,”,”,REPT(” “,LEN($B6))) → แทนที่ข้อความที่มีอยู่ด้วยข้อความใหม่ในสตริงข้อความ ที่นี่ B6 หมายถึง ข้อความ อาร์กิวเมนต์ในขณะที่ถัดไป “,” แสดงถึง old_text อาร์กิวเมนต์ และ REPT(” “,LEN($B6)) ชี้ไปที่ new_text อาร์กิวเมนต์ซึ่งแทนที่เครื่องหมายจุลภาคด้วยช่องว่าง
- ผลลัพธ์ → “เศรษฐกิจไม่ดี อภิจิตต์ บานารจี เศรษฐกิจ
- MID(SUBSTITUTE($B6,”,”,REPT(” “,LEN($B6))),(C$5-1)*LEN($B6)+1,LEN($B6) ) → ส่งคืนอักขระจากตรงกลางของสตริงข้อความ โดยกำหนดตำแหน่งเริ่มต้นและความยาว ที่นี่ SUBSTITUTE($B6,”,”,REPT(” “,LEN($B6))) เซลล์คือ ข้อความ อาร์กิวเมนต์ (C$5-1)*LEN($B6)+1 คือ start_num อาร์กิวเมนต์ และ LEN($B6) คือ num_chars อาร์กิวเมนต์เพื่อให้ฟังก์ชันส่งคืนอักขระตัวแรกจากด้านซ้าย
- ผลลัพธ์ → “เศรษฐกิจแย่ “
- TRIM(MID(SUBSTITUTE($B6,”,”REPT(” “,LEN($B6))),(C$5-1)*LEN($B6)+1,LEN($ B6))) → กลายเป็น
- TRIM(“เศรษฐกิจไม่ดี “) → ลบทั้งหมดยกเว้นช่องว่างเดียวออกจากข้อความ ที่นี่ “เศรษฐกิจไม่ดี ” เซลล์คือ ข้อความ อาร์กิวเมนต์และฟังก์ชันจะกำจัดช่องว่างส่วนเกินหลังข้อความ
- ผลลัพธ์ → “เศรษฐกิจแย่”
- ขั้นที่สอง ใช้ Fill Handle Tool เพื่อคัดลอกสูตรข้ามแถว
- ประการที่สาม เลือก C6:E6 เซลล์>> ลาก เติมที่จับ เครื่องมือสำหรับนำสูตรไปใช้กับเซลล์ด้านล่าง
สุดท้าย ผลลัพธ์ของคุณควรมีลักษณะเหมือนภาพที่แสดงด้านล่าง
อ่านเพิ่มเติม: วิธีแยกข้อความเป็นคอลัมน์โดยอัตโนมัติด้วยสูตรใน Excel
วิธีที่ 3 :การรวมฟังก์ชัน LEFT, RIGHT, MID, LEN และ FIND
หรือใช้คำสั่งผสมของ LEFT , ขวา , กลาง , เลน และ ค้นหา ทำหน้าที่แบ่งข้อความที่มีตัวคั่นหลายตัวเป็นคอลัมน์ต่างๆ ในกรณีนี้ FIND ฟังก์ชั่นค้นหาตัวเลขภายในอาร์เรย์ที่กำหนด และ LEN ฟังก์ชั่นส่งกลับความยาวของสตริงข้อความ ในทางกลับกัน MID ฟังก์ชั่นส่งคืนอักขระจากตรงกลางของสตริงข้อความในขณะที่ LEFT และ ขวา ฟังก์ชันดึงข้อความจากปลายด้านซ้ายและขวาของสตริงตามลำดับ
สมมติว่าเรามี รายชื่อลูกค้า ชุดข้อมูลที่แสดงใน B4:B12 เซลล์ซึ่งมี ชื่อ ประเทศ และเมือง คอลัมน์ที่มีข้อความคั่นด้วยเครื่องหมายอัฒภาค ในที่นี้ เราต้องการแบ่ง ชื่อ ประเทศ และเมือง ในคอลัมน์ต่างๆ กัน เรามาดูรายละเอียดขั้นตอนกันเลย
📌 ขั้นตอน :
ก่อนอื่น ให้ไปที่ C5 เซลล์>> แทรกนิพจน์ต่อไปนี้ลงใน แถบสูตร .
=LEFT(B5,FIND(";",B5)-1)
ในนิพจน์ข้างต้น B5 เซลล์แสดงถึง ชื่อ ประเทศ และเมือง คอลัมน์
รายละเอียดสูตร:
- FIND(“;”,B5) → ส่งกลับตำแหน่งเริ่มต้นของสตริงข้อความหนึ่งภายในสตริงข้อความอื่น ที่นี่ “;” เป็น find_text อาร์กิวเมนต์ในขณะที่ B5 คือ within_text การโต้แย้ง. โดยเฉพาะ FIND ฟังก์ชั่นส่งคืนตำแหน่งของ อัฒภาค (; ) อักขระในสตริงข้อความ
- เอาต์พุต → 7
- LEFT(B5,FIND(“;”,B5)-1) → กลายเป็น
- LEFT(B5,7) → ส่งกลับจำนวนอักขระที่ระบุตั้งแต่เริ่มต้นสตริง ที่นี่ B5 เซลล์คือ ข้อความ อาร์กิวเมนต์ในขณะที่ 7 คือ num_chars อาร์กิวเมนต์เพื่อให้ฟังก์ชันส่งคืน 7 อักขระจากด้านซ้าย
- ผลลัพธ์ → เทรเวอร์
- หลังจากนั้น ข้ามไปที่ D5 เซลล์>> พิมพ์นิพจน์ต่อไปนี้
=MID(B5,FIND(";",B5)+1,FIND("@",B5)-FIND(";",B5)-1)
คำอธิบายสูตร:
- FIND(“@”,B5)-FIND(“;”,B5)-1 → ที่นี่ FIND ฟังก์ชั่นส่งคืนตำแหน่งของ อัฒภาค (; ) และ ตามอัตรา (“@” ) อักขระภายในสตริงข้อความ
- 16 – 7 – 1 → 8
- FIND(“;”,B5)+1 → ตัวอย่างเช่น FIND ฟังก์ชันระบุตำแหน่ง อัฒภาค (; ) อักขระภายในสตริงข้อความ
- 7 + 1 → 8
- MID(B5,FIND(“;”,B5)+1,FIND(“@”,B5)-FIND(“;”,B5)-1) → กลายเป็น
- MID(B5,8,8) → ที่นี่ B5 เซลล์คือ ข้อความ อาร์กิวเมนต์ 8 คือ start_num อาร์กิวเมนต์ และ 8 คือ num_chars อาร์กิวเมนต์ที่ฟังก์ชันส่งคืน 8 อักขระหลัง 8ตัวแรก ตัวอักษร
- ผลลัพธ์ → ไอซ์แลนด์
- ต่อมา ให้ใส่สูตรด้านล่างลงใน E5 เซลล์
=RIGHT(B5,LEN(B5)-FIND("@",B5))
วิธีการทำงานของสูตรนี้:
- LEN(B5)-FIND(“@”,B5) → LEN ฟังก์ชั่นส่งคืนความยาวของสตริงใน B5 ในทางตรงกันข้าม FIND ฟังก์ชั่นส่งคืนตำแหน่งของ ตามอัตรา (“@” ) ตัวอักษร
- 26 – 16 → 10
- ขวา(B5,LEN(B5)-FIND(“@”,B5)) → กลายเป็น
- ขวา(B5,10) → ส่งกลับจำนวนอักขระที่ระบุจากจุดสิ้นสุดของสตริง ที่นี่ B5 เซลล์คือ ข้อความ อาร์กิวเมนต์ในขณะที่ 10 คือ num_chars อาร์กิวเมนต์เพื่อให้ฟังก์ชันส่งคืน 10 อักขระจากด้านขวา
- ผลลัพธ์ → เรคยาวิก
ในที่สุด ผลลัพธ์ควรมีลักษณะเหมือนภาพหน้าจอด้านล่าง
วิธีที่ 4 :การใช้ Flash Fill
หากการใช้สูตรที่ซับซ้อนไม่เหมาะกับคุณ วิธีต่อไปของเราอาจเป็นคำตอบที่คุณต้องการ เราจะใช้คุณลักษณะการเติมแบบรวดเร็ว ของ Excel เพื่อแปลงข้อความเป็นคอลัมน์ที่มีตัวคั่นหลายตัว
📌 ขั้นตอน :
- ในตอนเริ่มต้น ให้พิมพ์ Name Trevor . ด้วยตนเอง ใน C5 เซลล์>> ใน หน้าแรก ให้คลิกปุ่ม กรอก เมนูแบบเลื่อนลง>> เลือก เติมแฟลช ตัวเลือก
ตอนนี้ Excel จะเติมส่วนที่เหลือของเซลล์โดยอัตโนมัติ
ในทำนองเดียวกัน นำเทคนิคไปใช้กับ Country และ เมือง คอลัมน์และผลลัพธ์สุดท้ายควรมีลักษณะเหมือนภาพด้านล่าง
วิธีที่ 5 :การใช้ Power Query
เป็นที่ยอมรับ Power Query เป็นคุณลักษณะที่ถูกมองข้ามของ Excel แม้ว่าจะมีความสามารถในการจัดระเบียบและวิเคราะห์ข้อมูลได้อย่างรวดเร็วและมีประสิทธิภาพ ในส่วนต่อไปนี้ เราจะพูดถึงวิธีที่เราสามารถแปลงข้อความเป็นคอลัมน์ที่มีตัวคั่นหลายตัวด้วยการคลิกเพียงไม่กี่ครั้ง ไปดูกันเลยดีกว่า
📌 ขั้นตอน :
- ก่อนอื่น ย้ายไปที่ B4 เซลล์>> กดแป้นพิมพ์ลัด CTRL + T เพื่อ แทรกตาราง Excel>> กด ตกลง .
- ถัดไป ไปที่ ข้อมูล แท็บ>> คลิก จากตาราง/ช่วง ตัวเลือก
หลังจากนั้นไม่นาน ตัวแก้ไข Power Query ปรากฏขึ้น
- จากจุดนี้ ให้กด แยกคอลัมน์ เมนูแบบเลื่อนลง>> เลือก ตามตัวคั่น ตัวเลือก
- ต่อมา เลือก อัฒภาค ตัวเลือก>> ทำเครื่องหมายที่ ตัวคั่นแต่ละตัว ตัวเลือก>> กด ตกลง ปุ่ม.
- ตามนี้ ดับเบิลคลิก ส่วนหัวของคอลัมน์เพื่อเปลี่ยนชื่อ>> กดปุ่ม ปิด &โหลด ตัวเลือกเพื่อออกจาก Power Query หน้าต่าง
สุดท้าย การทำตามขั้นตอนทั้งหมดให้เสร็จสิ้นจะได้ผลลัพธ์ดังต่อไปนี้
อ่านเพิ่มเติม: วิธีใช้ตัวแบ่งบรรทัดเป็นตัวคั่นในข้อความ Excel เป็นคอลัมน์
วิธีที่ 6 :การใช้รหัส VBA
หากคุณต้องการแปลงข้อความเป็นคอลัมน์ที่มีตัวคั่นหลายตัวใน Excel บ่อยครั้ง คุณอาจลองใช้โค้ด VBA ด้านล่าง. ง่ายและสะดวก เพียงทำตาม
📌 ขั้นตอน :
- เริ่มต้นโดยไปที่ นักพัฒนา แท็บ>> คลิก Visual Basic ปุ่ม.
ในทันที ตัวแก้ไข Visual Basic เปิดในหน้าต่างใหม่
- ถัดไป ไปที่ แทรก แท็บ>> เลือก โมดูล .
ตอนนี้ เพื่อความสะดวกในการอ้างอิง คุณสามารถคัดลอกโค้ดจากที่นี่แล้ววางลงในหน้าต่างดังที่แสดงด้านล่าง
Sub Separate_Text_String()
Dim Arr() As String, _
cnt As Long, _
j As Variant
For k = 5 To 13
Arr = Split(Cells(k, 2), ";")
cnt = 3
For Each j In Arr
Cells(k, cnt) = j
cnt = cnt + 1
Next j
Next k
End Sub
⚡ รายละเอียดโค้ด:
ฉันจะอธิบาย VBA รหัสที่ใช้ในการแปลงข้อความเป็นคอลัมน์ที่มีตัวคั่นหลายตัว ในกรณีนี้โค้ดจะแบ่งเป็น 2 ขั้นตอน
- ในส่วนแรก รูทีนย่อยจะได้รับชื่อ นี่คือ Separate_Text_String() .
- ถัดไป กำหนดตัวแปร Arr, cnt, และ เจ เป็น สตริง , ยาว และ ตัวแปร .
- ในยาที่สอง ใช้ For Loop ผ่านแต่ละเซลล์และแบ่งข้อความโดยคั่นด้วยเครื่องหมายอัฒภาค
- ตอนนี้ ในโค้ด คำสั่ง “สำหรับ k =5 ถึง 13” หมายถึงหมายเลขแถวเริ่มต้นและสิ้นสุดของข้อมูล นี่คือ 5 ถึง 13 .
- จากนั้น “;” ใน “Arr =Split(Cells(k, 2), “;”)” เป็นตัวคั่นซึ่งคุณสามารถเปลี่ยนเป็นเครื่องหมายจุลภาค ไปป์ ฯลฯ ได้หากต้องการ
- สุดท้าย “cnt =3” ระบุหมายเลขคอลัมน์ที่สาม (คอลัมน์ C) .
- ตามนี้ กด วิ่ง ปุ่มหรือ F5 บนแป้นพิมพ์ของคุณ
ในที่สุด ผลลัพธ์ควรปรากฏในภาพหน้าจอด้านล่าง
ภาคปฏิบัติ
เราได้จัดเตรียมการปฏิบัติ ส่วนทางด้านขวาของแต่ละแผ่นเพื่อให้คุณสามารถฝึกฝนตัวเองได้ โปรดดำเนินการด้วยตนเอง
บทสรุป
โดยสรุป บทความนี้แสดง 6 วิธีที่มีประสิทธิภาพในการแปลงข้อความเป็นคอลัมน์ที่มีตัวคั่นหลายตัวใน Excel ดังนั้น อ่านบทความเต็มอย่างละเอียดและดาวน์โหลดสมุดงานฟรีเพื่อฝึกฝน ตอนนี้ เราหวังว่าบทความนี้จะเป็นประโยชน์สำหรับคุณ และหากคุณมีคำถามหรือคำแนะนำเพิ่มเติม โปรดแสดงความคิดเห็นได้ที่นี่ สุดท้าย ไปที่ ExcelDemy สำหรับบทความแบบนี้อีกมากมาย
บทความที่เกี่ยวข้อง
- วิธีใช้ข้อความเป็นคอลัมน์ใน Excel สำหรับวันที่ (ด้วยขั้นตอนง่ายๆ)
- [แก้ไขแล้ว!] ข้อความเป็นคอลัมน์ของ Excel กำลังลบข้อมูล
- วิธีการแปลงข้อความเป็นคอลัมน์โดยไม่ต้องเขียนทับใน Excel
