บทช่วยสอนนี้จะแสดงวิธีการแยกเซลล์ด้วยตัวคั่นโดยใช้สูตรใน Excel ตัวคั่นคืออักขระที่ใช้แยกข้อมูลภายในสตริงข้อความ ในบทความนี้เราจะสาธิตวิธีการต่างๆ ในการแยกเซลล์ด้วยตัวคั่นโดยใช้สูตรใน Excel
สมุดงานฝึกฝน
คุณสามารถดาวน์โหลดสมุดงานจากลิงก์ด้านล่างได้
8 วิธีที่แตกต่างกันในการแยกเซลล์ด้วยตัวคั่นโดยใช้สูตรใน Excel
คุณอาจต้องแบ่งเซลล์ใน Excel ในบางสถานการณ์ ซึ่งอาจเกิดขึ้นเมื่อคัดลอกข้อมูลจากอินเทอร์เน็ต ฐานข้อมูล หรือเพื่อนร่วมงาน ตัวอย่างง่ายๆ คือเมื่อคุณมีชื่อเต็มและต้องการแยกเป็นชื่อและนามสกุล
1. รวมฟังก์ชันข้อความของ Excel กับฟังก์ชัน SEARCH เพื่อแยกข้อความที่คั่นด้วยขีดกลาง/ขีดลบ
ขั้นตอนแรกในการแยกด้วยตัวคั่นคือการค้นหาตำแหน่งของตัวคั่นเอง เมื่อคุณพบตำแหน่งแล้ว คุณสามารถแยกข้อมูลจากทั้งสองข้างของตัวคั่นได้ง่าย เราจะใช้ ฟังก์ชัน SEARCH เพื่อค้นหาตัวคั่น จากนั้นใช้ LEFT, MID, หรือ RIGHT เพื่อดึงค่าที่ต้องการ
1.1. รวมฟังก์ชัน LEFT และ SEARCH
เริ่มต้นกันเลย ฟังก์ชัน LEFT มี 2 พารามิเตอร์คือ ข้อความและจำนวนตัวอักษร เราจะระบุข้อความที่ทราบค่า ส่วนจำนวนตัวอักษรจะใช้ ฟังก์ชัน SEARCH
ขั้นตอน:
- เลือกเซลล์และใส่สูตรดังนี้:
=LEFT(B5, SEARCH("-",B5,1)-1)- กด Enter
- ลาก Fill Handle ลงไปเพื่อคัดลอกสูตร หรือดับเบิลคลิกที่เครื่องหมาย + เพื่อ AutoFill
- ผลลัพธ์จะแสดงดังภาพ
🔎 สูตรทำงานอย่างไร?
ในตัวอย่าง ตัวคั่นคือขีดกลาง '–' ฟังก์ชัน SEARCH จะให้ตำแหน่งของขีดกลาง เราไม่ต้องการขีดกลางเอง แต่ต้องการข้อความก่อนขีดกลาง
1.2. รวมฟังก์ชัน MID และ SEARCH
สำหรับค่ากลาง เราจะใช้ MID และ SEARCH ร่วมกัน
ขั้นตอน:
- เลือกเซลล์และใส่สูตร:
=MID(B5, SEARCH("-",B5) + 1, SEARCH("-",B5,SEARCH("-",B5)+1) - SEARCH("-",B5) - 1)- กด Enter
- ลาก Fill Handle หรือดับเบิลคลิกเครื่องหมาย + เพื่อคัดลอก
- ค่ากลางทั้งหมดถูกแยกออกมาแล้ว
🔎 สูตรทำงานอย่างไร?
ฟังก์ชัน SEARCH คืนค่าตำแหน่งของข้อความหนึ่งภายในอีกข้อความหนึ่ง เริ่มจากตัวอักษรถัดจากขีดกลาง ฟังก์ชัน MID ดึงจำนวนตัวอักษรที่ระบุจากตำแหน่งที่กำหนด
1.3. รวมฟังก์ชัน RIGHT, LEN และ SEARCH
สำหรับค่าสุดท้าย เราจะใช้ RIGHT, LEN, และ SEARCH
ขั้นตอน:
- เลือกเซลล์และใส่สูตร:
=RIGHT(B5,LEN(B5) - SEARCH("-", B5, SEARCH("-", B5) + 1))- กด Enter
- ลาก Fill Handle หรือดับเบิลคลิกเครื่องหมาย +
- ค่าสุดท้ายถูกแยกด้วยตัวคั่นแล้ว
🔎 สูตรทำงานอย่างไร?
LEN คืนความยาวรวมของสตริง ลบด้วยตำแหน่งขีดกลางตัวสุดท้าย ผลต่างคือจำนวนตัวอักษรหลังขีดกลางสุดท้าย ซึ่ง RIGHT จะดึงออกมา
หมายเหตุ: คุณสามารถแยกคอลัมน์ด้วยอักขระอื่นได้เช่นกัน เพียงแทนที่ '–' ด้วยตัวคั่นที่ต้องการ
อ่านเพิ่มเติม: สูตร Excel แยกสตริงด้วยจุลภาค (5 ตัวอย่าง)
2. รวมสูตรสำหรับแยกข้อความด้วยตัวขึ้นบรรทัดใหม่
เพื่อแยกสตริงด้วยตัวขึ้นบรรทัด เราใช้สูตรคล้ายส่วนก่อนหน้า แต่เพิ่มฟังก์ชัน CHAR
2.1. รวม LEFT, SEARCH และ CHAR
CHAR จะให้อักขระขึ้นบรรทัดใหม่ เพื่อดึงค่าแรกและแยกออกจากเซลล์ เราใช้ LEFT, SEARCH, และ CHAR
ขั้นตอน:
- เลือกเซลล์และใส่สูตรเพื่อดึงค่าบนสุด:
=LEFT(B5, SEARCH(CHAR(10),B5,1)-1)- กด Enter
- ลากเครื่องหมาย + เพื่อคัดลอกสูตร
🔎 สูตรทำงานอย่างไร?
10 เป็นรหัส ASCII ของขึ้นบรรทัดใหม่ เราใส่ 10 ใน CHAR เพื่อค้นหาตัวขึ้นบรรทัด จากนั้นคืนค่าข้อความบนสุด
2.2. รวม MID, SEARCH และ CHAR
สำหรับค่ากลาง ให้ทำตามขั้นตอนด้านล่าง
ขั้นตอน:
- เลือกเซลล์และใส่สูตร:
=MID(B5, SEARCH(CHAR(10),B5) + 1, SEARCH(CHAR(10),B5, SEARCH(CHAR(10),B5)+1) - SEARCH(CHAR(10),B5) - 1)- กด Enter
- ลากเครื่องหมาย + เพื่อคัดลอก
2.3. รวม RIGHT, LEN, CHAR และ SEARCH
สำหรับข้อความด้านขวา ใช้ RIGHT, LEN, CHAR, และ SEARCH
ขั้นตอน:
- เลือกเซลล์และใส่สูตรเพื่อดึงค่าล่างสุด:
=RIGHT(B5,LEN(B5) - SEARCH(CHAR(10), B5, SEARCH(CHAR(10), B5) + 1))- กด Enter
- ลากเครื่องหมาย + เพื่อคัดลอก
อ่านเพิ่มเติม: วิธีแยกเซลล์ใน Excel (5 เทคนิคง่าย)
3. แยกเซลล์ตามรูปแบบข้อความต่อตัวเลขใน Excel
ในส่วนนี้ เราจะดูวิธีแยกข้อความที่มีสตริงตัวอักษรตามด้วยตัวเลข เพื่อความง่าย เราได้ปรับเปลี่ยนชีตเล็กน้อย (ไม่ต้องกังวล ทุกชีตอยู่ในสมุดงาน) ในตัวอย่างของเรา มี ชื่อนักเรียน และ รหัส รวมอยู่ในคอลัมน์เดียว และจะแยกเป็น 2 คอลัมน์
3.1. รวม RIGHT, SUM, LEN และ SUBSTITUTE
ใน SUBSTITUTE เราแทนที่ตัวเลขด้วยช่องว่างและนับด้วย LEN เพื่อแยกข้อความตามด้วยรูปแบบตัวเลข เราต้องหาตัวเลขก่อน แล้วใช้ตัวเลขที่ได้มาดึงข้อความ
ขั้นตอน:
- เลือกเซลล์ที่ต้องการผลลัพธ์ เช่น C5
- ใส่สูตร:
=RIGHT(B5,SUM(LEN(B5) -LEN(SUBSTITUTE(B5, {"0","1","2","3","4","5","6","7","8","9"},""))))- กด Enter
- ลากเครื่องหมาย + เพื่อคัดลอก
🔎 สูตรทำงานอย่างไร?
เพื่อดึงตัวเลข เราต้องค้นหาตัวเลข 0-9 ทุกตัวในสตริง แล้วรวมจำนวนตัวเลขทั้งหมด และดึงจำนวนตัวอักษรจากท้ายสตริง
3.2. รวม LEFT และ LEN
เพื่อดึงค่าข้อความ ใช้ LEFT และในพารามิเตอร์จำนวนตัวอักษร ให้ความยาวเซลล์ลบด้วยความยาวตัวเลขในเซลล์ D5 (ที่แยก ID ไปก่อนหน้า)
ขั้นตอน:
- เลือกเซลล์และใส่สูตร:
=LEFT(B5,LEN(B5)-LEN(D5))- กด Enter
- ลากเครื่องหมาย + เพื่อคัดลอก
อ่านเพิ่มเติม: Excel VBA: แยกสตริงตามจำนวนตัวอักษร (2 วิธีง่าย)
4. แยกเซลล์ตามรูปแบบตัวเลขต่อข้อความโดยใช้สูตร
หากคุณเข้าใจวิธีแยก 'ข้อความ + ตัวเลข' แล้ว คงนึกภาพวิธีแยกสตริงตัวเลขต่อข้อความได้ วิธีการคล้ายเดิม แค่เปลี่ยน ตัวเลขอยู่ซ้ายข้อความ จึงใช้ LEFT ดึงตัวเลข และ RIGHT ดึงข้อความ
4.1. รวม LEFT, SUM, LEN และ SUBSTITUTE
เพื่อแยกเซลล์ตามรูปแบบตัวเลขและข้อความสำหรับค่าแรก เรารวม LEFT, SUM, LEN, และ SUBSTITUTE
ขั้นตอน:
- เลือกเซลล์และใส่สูตร:
=LEFT(B5, SUM(LEN(B5) -LEN(SUBSTITUTE(B5, {"0","1","2","3","4","5","6","7","8","9"}, ""))))- กด Enter
- ลากเครื่องหมาย + เพื่อคัดลอก
4.2. รวม RIGHT และ LEN
เรารวม RIGHT และ LEN เพื่อแยกค่าสุดท้าย
ขั้นตอน:
- เลือกเซลล์และใส่สูตร:
=RIGHT(B5,LEN(B5)-LEN(C5))- กด Enter
- ลากเครื่องหมาย + เพื่อคัดลอก
5. แยกวันที่จากเซลล์โดยรวม RIGHT, LEN, FIND และ SUBSTITUTE
เพื่อแยกวันที่จากข้อความ ใช้ RIGHT, LEN, FIND และ SUBSTITUTE
ขั้นตอน:
- เลือกเซลล์และใส่สูตร:
=RIGHT(B5,LEN(B5)-FIND(" ",SUBSTITUTE(B5," "," ",LEN(B5)-LEN(SUBSTITUTE(B5," ",""))-2)))- กด Enter
- ลากเครื่องหมาย + เพื่อคัดลอก
🔎 สูตรทำงานอย่างไร?
เนื่องจากค่าวันที่อยู่ท้ายสตริง เราจึงนับจำนวนช่องว่างเพื่อแยกเดือน วัน ปี หากต้องการข้อความมากกว่านี้ สามารถปรับจำนวนครั้งที่ค้นหาได้
หมายเหตุ: สูตรนี้ใช้ได้ก็ต่อเมื่อวันที่อยู่ท้ายสตริงข้อความ
อ่านเพิ่มเติม: Excel VBA: แยกสตริงเป็นเซลล์ (4 การประยุกต์ที่มีประโยชน์)
6. รวม FILTERXML และ SUBSTITUTE เพื่อแยกเซลล์
โดยใช้ xpath ที่กำหนด ฟังก์ชัน FILTERXML ดึงข้อมูลเฉพาะจากเอกสาร XML เราสามารถรวม FILTERXML และ SUBSTITUTE เพื่อแยกเซลล์ได้
ขั้นตอน:
- เลือกเซลล์และใส่สูตร:
=FILTERXML(""&SUBSTITUTE(B5,",","")&"- กด Enter
- ลากเครื่องหมาย + เพื่อคัดลอก
🔎 สูตรทำงานอย่างไร?
SUBSTITUTE แทนที่ข้อความเฉพาะในสตริง จากนั้น FILTERXML ของ Excel ดึงข้อมูลจากไฟล์ XML
7. ใช้ฟังก์ชัน TEXTSPLIT แยกเซลล์ด้วยตัวคั่น
เราใช้ ฟังก์ชัน TEXTSPLIT โดยใช้คอลัมน์และแถวเป็นตัวคั่นเพื่อแบ่งลำดับข้อความ คุณสามารถแบ่งตามแถวหรือคอลัมน์ได้ นี่คือวิธีที่สั้นที่สุดและง่ายที่สุดในการแยกเซลล์ด้วยตัวคั่น
ขั้นตอน:
- เลือกเซลล์ที่ต้องการผลลัพธ์และใส่สูตร:
=TEXTSPLIT(B5,",")- กด Enter
- ลากเครื่องหมาย + เพื่อคัดลอก
หมายเหตุ: ตรวจสอบให้แน่ใจว่ามีคอลัมน์ว่างเพียงพอด้านข้าง มิฉะนั้นอาจเกิดข้อผิดพลาด #SPILL!
8. แยกเซลล์โดยรวม TRIM, MID, SUBSTITUTE, REPT และ LEN
สูตรอื่นๆ คือการรวม TRIM, MID, SUBSTITUTE, REPT, และ LEN ซึ่งสามารถแยกเซลล์ด้วยตัวคั่นได้
ขั้นตอน:
- เลือกเซลล์และใส่สูตร:
=TRIM(MID(SUBSTITUTE($B5,"|",REPT(" ",LEN($B5))),(C$4-1)*LEN($B5)+1,LEN($B5)))- กด Enter
- ลากเครื่องหมาย + เพื่อคัดลอก
🔎 สูตรทำงานอย่างไร?
LEN คืนความยาวข้อความ SUBSTITUTE แทนที่ข้อความที่ตำแหน่งเฉพาะ MID ดึงจำนวนตัวอักษรจากตำแหน่งที่กำหนด สุดท้าย TRIM ลบช่องว่างเกินออก
วิธีแยกเซลล์ด้วยตัวคั่นโดยใช้คำสั่งใน Excel
Excel มีฟีเจอร์แยกเซลล์ในตัว อยู่ในแท็บ Data ให้ทำตามขั้นตอนด้านล่าง
ขั้นตอน:
- เลือกเซลล์หรือคอลัมน์ (มักเลือกคอลัมน์ทั้งหมด)
- ไปที่แท็บ Data ในส่วน Data Tools คลิก Text to Columns
- กล่องโต้ตอบจะปรากฏ เลือก Delimited และคลิก Next
- เลือกตัวคั่นที่ต้องการ หรือป้อนตัวคั่นเองได้
- ดูตัวอย่างผลลัพธ์ด้านล่างกล่อง แล้วคลิก Next
- ในตัวอย่างเราเลือกจุลภาค เพราะข้อมูลคั่นด้วยจุลภาค
- คลิก Next เลือกรูปแบบข้อมูล แล้วคลิก Finish
- เก็บเป็น General (ค่าเริ่มต้น) ผลลัพธ์จะแสดงดังภาพ
สรุป
วิธีการข้างต้นจะช่วยให้คุณแยกเซลล์ด้วยตัวคั่นโดยใช้สูตรใน Excel ได้ นี่คือสิ้นสุดสำหรับวันนี้ ด้วยวิธีการเหล่านี้ คุณสามารถแยกเซลล์ด้วยตัวคั่นได้ เราพยายามรวบรวมหลายวิธีไว้ หวังว่าจะเป็นประโยชน์ ถ้ามีจุดไหนยากเข้าใจ สามารถคอมเมนต์ได้ หรือแจกวิธีอื่นๆ ที่เราพลาดมา
อ่านเพิ่มเติม
- วิธีแยกเซลล์หนึ่งเป็นสองใน Excel (5 วิธีที่มีประโยชน์)
- วิธีแยกเซลล์เดี่ยวครึ่งหนึ่งใน Excel (แนวทแยงและแนวนอน)
- VBA แยกสตริงเป็นหลายคอลัมน์ใน Excel (2 วิธี)
- Excel VBA: แยกสตริงตามตัวอักษร (6 ตัวอย่างที่มีประโยชน์)
- วิทีทำให้มีสองบรรทัดในเซลล์เดียวใน Excel (4 วิธี)
