Excel

แยกข้อความด้วยตัวคั่น

Split Text With Delimiter

สูตร Excel: แยกข้อความด้วยตัวคั่นสูตรทั่วไป |_+_| สรุป

หากต้องการแยกข้อความที่ตัวคั่นที่ต้องการ (จุลภาค เว้นวรรค ไปป์ ฯลฯ) คุณสามารถใช้สูตรตามฟังก์ชัน TRIM, MID, SUBSTITUTE, REPT และ LEN ในตัวอย่างที่แสดง สูตรใน C5 คือ:





= TRIM ( MID ( SUBSTITUTE (A1,delim, REPT (' ', LEN (A1))),(N-1)* LEN (A1)+1, LEN (A1)))

หมายเหตุ: การอ้างอิงถึง B5 และ C4 คือ การอ้างอิงแบบผสม เพื่อให้สามารถคัดลอกสูตรข้ามและลงได้

เซลล์สีสูตร excel ถ้าค่า
คำอธิบาย

สาระสำคัญของสูตรนี้คือการแทนที่ตัวคั่นที่กำหนดด้วยช่องว่างจำนวนมากโดยใช้ SUBSTITUTE และ REPT จากนั้นใช้ฟังก์ชัน MID เพื่อแยกข้อความที่เกี่ยวข้องกับ 'การเกิดขึ้นที่ n' และฟังก์ชัน TRIM เพื่อกำจัดพื้นที่ส่วนเกิน





ในตัวอย่างนี้ ตัวคั่น (ตัวคั่น) จะถูกแทนที่ด้วยช่องว่างจำนวนหนึ่งเท่ากับความยาวทั้งหมดของสตริง:

 
= TRIM ( MID ( SUBSTITUTE ($B5,'|', REPT (' ', LEN ($B5))),(C-1)* LEN ($B5)+1, LEN ($B5)))

จากนั้นสูตรจะใช้ฟังก์ชัน MID เพื่อแยกสตริงย่อยที่ n จุดเริ่มต้นคำนวณด้วยรหัสด้านล่าง โดยที่ N หมายถึง 'nth':



 
 SUBSTITUTE (A1,delim, REPT (' ', LEN (A1)))

อักขระทั้งหมดที่แยกออกมาจะเท่ากับความยาวของสตริงข้อความแบบเต็ม ฟังก์ชัน TRIM จะลบช่องว่างพิเศษทั้งหมดและส่งคืนเฉพาะสตริงที่ n

แยกเพียงตัวอย่างเดียว

แม้ว่าตัวอย่างจะถูกตั้งค่าให้แยก 5 สตริงย่อยออกจากข้อความในคอลัมน์ B แต่คุณสามารถแยกเพียง 1 อินสแตนซ์ได้อย่างง่ายดาย ตัวอย่างเช่น หากต้องการแยกเฉพาะรายการที่ 4 (เมือง) คุณสามารถใช้:

 
(N-1)* LEN (A1)+1

คุณสมบัติข้อความเป็นคอลัมน์

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

ผู้เขียน Dave Bruns


^