เมื่อคุณนำเข้าข้อมูลจากฐานข้อมูลหรือแหล่งที่มาอื่น คุณต้อง แยกเซลล์ (Split Cell) หนึ่งเป็นสองหรือมากกว่าในสถานการณ์เช่นนี้
ในบทช่วยสอนนี้ ผมจะอภิปรายวิธีแยกเซลล์หนึ่งเป็นสองใน Excel โดยใช้วิธีที่มีประสิทธิภาพต่อไปนี้ รวมถึงตัวอย่างจากชีวิตจริง
วิธีแยกเซลล์หนึ่งเป็นสองใน Excel
ที่นี่ เรามีชุดข้อมูลที่คอลัมน์ B ประกอบด้วยชื่อเต็มเป็นหลัก ตอนนี้ เราต้องแยกเซลล์ของคอลัมน์ B เป็นสองคอลัมน์ เช่น ชื่อและนามสกุล เราสามารถทำได้โดยใช้วิธีต่อไปนี้
1. แยกเซลล์ด้วย Text to Columns
ในชุดข้อมูล เราเห็นชื่อนักเขียนวรรณกรรมอังกฤษที่ผมชอบ ตอนนี้ เราจะแยกชื่อเป็นชื่อและนามสกุลโดยใช้ Text to Columns
Text to Columns เป็นฟีเจอร์ที่ใช้งานง่ายใน Excel ที่ใช้ตัวคั่น (Delimiter) เพื่อแยกข้อความในเซลล์/คอลัมน์หนึ่งให้เป็นหลายคอลัมน์
ตัวคั่น (Delimiter) คือประเภทของตัวอักษร เช่น จุลภาค ช่องว่าง จุดทอน ฯลฯ ที่แยกสตริงข้อความหรือสตรีมข้อมูลอื่นๆ
ในชุดข้อมูลของเรา ช่องว่าง (Space) เป็นตัวคั่น
คุณสามารถดำเนินการตามขั้นตอนต่อไปนี้
ขั้นตอน:
- เลือกข้อมูลทั้งหมด เช่น B4:B11
- เลือกตัวเลือก Text to Columns จากแท็บ Data
- เลือกตัวเลือก Delimited
- กด Next
- ตอนนี้เลือกตัวเลือก Space
- กด Next
- เลือกตัวเลือก Text จากรูปแบบข้อมูลคอลัมน์
- ปรับ Destination หากจำเป็น และกด Finish
และคุณจะได้ผลดังนี้
อ่านเพิ่มเติม: วิธีแยกเซลล์ใน Excel (5 เทคนิคง่าย)
2. แยกเซลล์ด้วย Flash Fill
Flash Fill เป็นเครื่องมือพิเศษของ Excel ที่กรอกค่าอัตโนมัติเมื่อพบรูปแบบในข้อมูล ฟีเจอร์นี้มีให้ใช้งานตั้งแต่ Microsoft Excel 2013
มันเป็นหนึ่งในเทคนิค Machine Learning สำหรับประเมินรูปแบบข้อมูล เรียนรู้รูปแบบ และกรอกเซลล์โดยใช้รูปแบบนั้น
คุณสามารถแยกชื่อในชุดข้อมูลเป็นชื่อและนามสกุลด้วยความช่วยเหลือของเครื่องมือนี้
ขั้นตอน:
- เลือกเซลล์ว่าง เช่น C5
- พิมพ์ชื่อแรก William ของเซลล์ B5 ในเซลล์ C5 ที่เลือก
- ดับเบิลคลิกที่มุมล่างขวาของเซลล์ ช่วงเซลล์ทั้งหมดจะถูกกรอกอัตโนมัติ ที่นี่มีทางเลือกมากมาย เช่น คุณสามารถลากเคอร์เซอร์ไปเซลล์สุดท้ายของช่วง หรือเลือก Flash Fill โดยตรงจาก Data > Data Tools > Flash Fill
- ตอนนี้เลือกตัวเลือก Flash Fill
เครื่องมือกรอกชื่อแรกในช่วงเซลล์ทั้งหมดอัตโนมัติ ดังภาพด้านล่าง
เช่นเดียวกัน คุณสามารถใช้ฟีเจอร์นี้สำหรับหานามสกุล ในกรณีนั้น คุณต้องพิมพ์นามสกุลของเซลล์ B5 ในเซลล์ D5 ตอนนี้ทำตามกระบวนการเดียวกับก่อนหน้า คุณจะได้ผลลัพธ์ดังนี้
อ่านเพิ่มเติม: VBA to Split String into Multiple Columns in Excel (2 Ways)
3. แยกเซลล์ด้วยการใช้สูตร (Formulas)
i. แยกเซลล์ด้วยตัวคั่น (Delimiter)
เราสามารถแยกชื่อเป็นชื่อและนามสกุลโดยใช้ฟังก์ชัน Excel พร้อมตัวคั่น ในชุดข้อมูลของเรา ช่องว่าง (Space) เป็นตัวคั่นที่อยู่กลางชื่อ
มาดูภาพรวมของฟังก์ชันที่จะใช้ที่นี่
ฟังก์ชัน LEFT ดึงข้อความจากด้านซ้ายของสตริง ไวยากรณ์ของฟังก์ชันคือ
=LEFT (text, [num_chars])
มีอาร์กิวเมนต์ในฟังก์ชันดังนี้
text – ข้อความที่จะดึงตัวอักษร
num_chars – [ตัวเลือก] จำนวนตัวอักษรที่จะดึง เริ่มจากด้านซ้ายของข้อความ
ฟังก์ชัน RIGHT ดึงจำนวนตัวอักษรที่กำหนดจากด้านขวาของข้อความที่ให้มา ไวยากรณ์คือ
=RIGHT (text, [num_chars])
มีอาร์กิวเมนต์ดังนี้
text – ข้อความที่จะดึงตัวอักษรทางขวา
num_chars – [ตัวเลือก] จำนวนตัวอักษรที่จะดึง เริ่มจากด้านขวา
ฟังก์ชัน SEARCH ของ Excel ส่งคืนตำแหน่งของสตริงข้อความหนึ่งภายในอีกสตริงหนึ่ง
ไวยากรณ์คือ
=SEARCH (find_text, within_text, [start_num])
อาร์กิวเมนต์ของไวยากรณ์มีดังนี้
find_text – ข้อความที่จะค้นหา
within_text – ข้อความที่จะค้นหาภายใน
start_num – [ตัวเลือก] ตำแหน่งเริ่มต้นในข้อความที่จะค้นหา
ฟังก์ชัน FIND ส่งคืนตำแหน่ง (เป็นตัวเลข) ของสตริงข้อความหนึ่งภายในอีกสตริงหนึ่ง
ไวยากรณ์คือ
=FIND (find_text, within_text, [start_num])
อาร์กิวเมนต์ของไวยากรณ์มีดังนี้
find_text – ข้อความที่จะค้นหา
within_text – ข้อความที่จะค้นหาภายใน
start_num – [ตัวเลือก] ตำแหน่งเริ่มต้นในข้อความที่จะค้นหา
ใช้ฟังก์ชัน LEN เราสามารถได้ความยาวของข้อความ ไวยากรณ์คือ
=LEN (text)
มีอาร์กิวเมนต์ดังนี้
text – ข้อความที่จะคำนวณความยาว
ฟังก์ชัน SUBSTITUTE แทนที่ข้อความในสตริงที่กำหนด ไวยากรณ์คือ
=SUBSTITUTE (text, old_text, new_text, [instance])
มีอาร์กิวเมนต์ดังนี้
text – ข้อความที่จะเปลี่ยน
old_text – ข้อความที่จะแทนที่
new_text – ข้อความที่จะแทนที่ด้วย
instance – [ตัวเลือก] Instance ที่จะแทนที่
ทันทีนี้ เราจะหาชื่อแรกโดยใช้การรวมของ LEFT และ SEARCH
สำหรับนี้ ให้เลือกเซลล์ว่าง مثل C3 และพิมพ์สูตร =LEFT(B5,SEARCH(" ",B5)) โดยที่ B5 คือเซลล์ที่เราต้องการแยกชื่อ และกด Enter และใช้ Fill Handle tool เพื่อได้ผลลัพธ์ทั้งหมดของคอลัมน์
ในทางเดียวกัน เราสามารถหานามสกุลได้โดยใช้สูตรต่อไปนี้ คือ
=RIGHT(B5,LEN(B5)-FIND("*",SUBSTITUTE(B5," ","*",LEN(B5)-LEN(SUBSTITUTE(B5," ",""))))) โดยที่ B5 คือข้อความที่เราต้องการแยก ช่องว่าง(" ") เป็นตัวคั่นของเรา
➥ อ่านเพิ่มเติม: Excel Split Cell by Delimiter Formula
ii. แยกเซลล์ด้วยการขึ้นบรรทัดใหม่ (Line Break)
วิธีนี้คล้ายกับวิธีก่อนหน้า แต่เราต้องใช้ฟังก์ชัน CHAR
ฟังก์ชัน CHAR ส่งคืนตัวอักษรที่ระบุโดยหมายเลขรหัสจากชุดตัวอักษรสำหรับชุดข้อมูลของคุณ ที่นี่รหัสหมายถึงรหัส ASCII
ไวยากรณ์ของฟังก์ชันคือ
=CHAR (number)
มีอาร์กิวเมนต์ดังนี้
number – ตัวเลขระหว่าง 1 ถึง 255
ตอนนี้ เราสามารถได้ชื่อแรกโดยใส่สูตรในเซลล์ว่างและใช้ Fill Handle Tool สำหรับเซลล์อื่น สูตรคือ =LEFT(B5,SEARCH(CHAR(10),B5,1)-1) โดยที่ B5 คือข้อความ และ 10 คือรหัส ASCII สำหรับ Line feed
หมายเหตุ เราลบ 1 เพราะเราไม่ต้องการดึงตัวคั่น "ช่องว่าง" เอง
เช่นเดียวกัน พิมพ์สูตรในเซลล์ว่าง คือ =RIGHT(B5,LEN(B5)-SEARCH(CHAR(10),B5,SEARCH(CHAR(10),B5))) โดยที่ B5 คือเซลล์ที่เราต้องการแยก และ 10 คือรหัส ASCII สำหรับ Line feed ตอนนี้กด Enter และใช้ Fill Handle Tool สำหรับเซลล์อื่นในคอลัมน์เดียวกัน
อ่านเพิ่มเติม: Excel Formula to Split: 8 Examples
4. แยกเซลล์รูปแบบ Text+Number (ข้อความ+ตัวเลข)
บางครั้ง เราอาจต้องแยกเซลล์ที่มีรูปแบบ Text+Number ในกรณีนั้น เราสามารถทำตามขั้นตอนได้ ที่นี่เราต้องใช้ฟังก์ชัน SUM ร่วมกับฟังก์ชันอื่นๆ
ฟังก์ชัน SUM ส่งคืนผลรวมของค่าที่ให้มา ค่าเหล่านี้อาจเป็นช่วง อาเรย์ ตัวเลข ฯลฯ ไวยากรณ์คือ
=SUM (number1, [number2], [number3], …)
มีอาร์กิวเมนต์ดังนี้
number1 – ค่าแรกที่จะบวก
number2 – [ตัวเลือก] ค่าที่สองที่จะบวก
number3 – [ตัวเลือก] ค่าที่สามที่จะบวก
วิธีที่ง่ายที่สุดในการแยกเซลล์แบบรูปแบบนี้คือแยกตัวเลขก่อน แล้วค่อยหาข้อความ ตอนนี้ ให้เลือกเซลล์ว่างและพิมพ์สูตร คือ =RIGHT(B5,SUM(LEN(B5) - LEN(SUBSTITUTE(B5, {"0","1","2","3","4","5","6","7","8","9"},"")))) โดยที่ B5 คือเซลล์ที่คุณต้องการแยก
กด Enter และใช้ Fill Handle Tool สำหรับเซลล์อื่นๆ
สำหรับการหาข้อความ ให้พิมพ์สูตรในเซลล์ว่าง สูตรคือ
=LEFT(B5,LEN(B5)-LEN(D5)) โดย B5 คือเซลล์ที่เราต้องการแยก และ D5 คือตัวเลขที่เราหาได้จากสูตรก่อนหน้า
5. แยกเซลล์ด้วย Power Query
เราสามารถแยกชื่อเป็นชื่อและนามสกุลได้อีกด้วย Power Query เป็น Add-in ฟรีของ Microsoft สำหรับนำเข้าข้อมูลจากแหล่งต่างๆ และทำความสะอาด แปลง และปรับรูปข้อมูลหากจำเป็น
คุณสามารถดำเนินการตามขั้นตอนต่อไปนี้
ขั้นตอน:
- เลือกช่วงเซลล์ทั้งหมด B4:B11
- คลิกที่ From Table ของแท็บ Data
- กด OK
ตอนนี้คุณอยู่ใน Power Query Editor และเลือก Home > Split Column > By Delimiter
จากนั้นคุณจะเห็นรูปดังนี้ เลือก Space เป็นตัวคั่นของคุณ และกด OK
การวิเคราะห์เสร็จสิ้นแล้ว แต่คุณต้องโหลดไฟล์ในชีตทำงานที่มีอยู่ สำหรับนี้ ให้เลือก Home > Close & Load > Close & Load To
จากนั้นเลือก Destination ของคุณ และกด Load
สุดท้าย คุณจะได้ผลลัพธ์ดังนี้-
อ่านเพิ่มเติม: วิธีแยกเซลล์หนึ่งเป็นสองแถวใน Excel (3 วิธี)
สิ่งที่ควรจำไว้
ระมัดระวังเกี่ยวกับสูตรเมื่อคุณใส่ในแถบสูตร
นอกจากนี้ ให้ระมัดระวังเกี่ยวกับชื่อไฟล์ ตำแหน่งไฟล์ และนามสกุลของไฟล์ Excel ด้วย
สรุป
ผมได้อภิปรายวิธีที่มีประสิทธิภาพและสะดวกที่สุด ตอนนี้ เลือกวิธีหนึ่งตามข้อมูลและความต้องการของคุณ และหากคุณมีข้อเสนอแนะและความสับสน โปรดบอกผมได้
ขอบคุณที่อยู่กับผม
การอ่านเพิ่มเติม
- วิธีแยกเซลล์เดี่ยวครึ่งหนึ่งใน Excel (ทแยงมุมและแนวนอน)
- Excel Formula to Split String by Comma (5 Examples)
- Excel VBA: Split String by Character (6 Useful Examples)
- วิธีทำให้เป็นสองบรรทัดในเซลล์เดียวใน Excel (4 วิธี)
- Excel VBA: Split String into Cells (4 Useful Applications)
- Excel VBA: Split String by Number of Characters (2 Easy Methods)
