หากต้องการแยกข้อความที่ตัวคั่นที่ต้องการ (จุลภาค เว้นวรรค ไปป์ ฯลฯ) คุณสามารถใช้สูตรตามฟังก์ชัน 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