กวดวิชา Excel – แยกข้อความหรือตัวเลขตามตำแหน่งที่ระบุ

ในหลายกรณี คุณอาจจำเป็นต้องแยกเฉพาะเนื้อหาที่เป็นประโยชน์จากประโยคหรือสตริงข้อความในเซลล์ เช่น การแยกจังหวัดออกจากที่อยู่ การแยกที่อยู่อีเมลออกจากประโยค การดึงหมายเลขบิลจัดส่งจากการสนทนา และอื่นๆ บทช่วยสอนนี้จะจำกัดการดึงข้อมูลให้แคบลงจนถึงตำแหน่งเฉพาะในเซลล์ และรวบรวมวิธีการต่างๆ เพื่อช่วยแยกข้อความหรือตัวเลขออกจากเซลล์ตามตำแหน่งเฉพาะใน Excel

---

แยกข้อความตามตำแหน่ง

ส่วนนี้รวบรวมตำแหน่งทั่วไปที่สามารถดึงข้อความออกจากเซลล์และจัดเตรียมวิธีการที่เกี่ยวข้องเพื่อจัดการทีละขั้นตอน คุณสามารถเรียกดูรายละเอียดเพิ่มเติม

1. แยกจำนวนตัวอักษรจากซ้ายหรือขวา

หากต้องการแยกจำนวนอักขระจากด้านซ้ายหรือด้านขวาของสตริง คุณสามารถลองใช้วิธีใดวิธีหนึ่งด้านล่างนี้

1.1 แยกอักขระ N ตัวแรกหรือตัวสุดท้ายด้วยสูตร

สมมติว่าคุณมีรายการสตริงข้อความในคอลัมน์ B ดังที่แสดงในภาพหน้าจอด้านล่าง หากต้องการแยกอักขระ 2 ตัวแรกและ 2 อักขระสุดท้ายออกจากแต่ละสตริง คุณสามารถใช้สูตรต่อไปนี้ได้

แยกอักขระ N ตัวแรกออกจากสตริงข้อความ

ฟังก์ชัน LEFT สามารถช่วยดึงอักขระ N ตัวแรกออกจากสตริงข้อความใน Excel ได้อย่างง่ายดาย

สูตรทั่วไป

=LEFT(text_string,[num_chars])

ข้อโต้แย้ง

ตอนนี้คุณสามารถใช้สูตรนี้เพื่อแยกอักขระ 2 ตัวแรกออกจากเซลล์ในคอลัมน์ B

1. เลือกเซลล์ว่าง คัดลอกหรือป้อนสูตรด้านล่างแล้วกด เข้าสู่ ที่สำคัญเพื่อให้ได้ผลลัพธ์แรก เลือกเซลล์ผลลัพธ์แล้วลากที่จับป้อนอัตโนมัติลงเพื่อใช้สูตรกับเซลล์อื่น

=LEFT(B5,2)

ตอนนี้ได้แยกอักขระ 2 ตัวแรกในแต่ละเซลล์ของช่วง B5:B10 แล้ว

แยกอักขระ N ตัวสุดท้ายออกจากสตริงข้อความ

ที่นี่เราใช้ฟังก์ชัน RIGHT เพื่อแยกอักขระ N ตัวสุดท้ายออกจากสตริงข้อความใน Excel

สูตรทั่วไป

=RIGHT(text_string,[num_chars])

ข้อโต้แย้ง

เลือกเซลล์ว่าง คัดลอกหรือป้อนสูตรด้านล่างแล้วกด เข้าสู่ ที่สำคัญเพื่อให้ได้ผลลัพธ์ จากนั้นเลือกเซลล์ผลลัพธ์นี้แล้วลากที่จับป้อนอัตโนมัติลงไปเพื่อรับผลลัพธ์อื่นๆ

=RIGHT(B5,2)

1.2 แยกอักขระ N ตัวแรกหรือตัวสุดท้ายด้วยเครื่องมือที่น่าทึ่ง

แม้ว่าสูตรข้างต้นจะเรียบง่าย แต่หากต้องการแยกอักขระ n ตัวแรกหรือ n ตัวสุดท้ายออกจากรายการสตริงข้อความแบบยาว คุณยังต้องลากตัวจัดการป้อนอัตโนมัติจากบนลงล่าง ซึ่งอาจใช้เวลานานเล็กน้อย ที่นี่ขอแนะนำ Kutools for Excel's แยกข้อความ ยูทิลิตี้เพื่อช่วยแยกอักขระ N ตัวแรกหรือตัวสุดท้ายออกจากรายการสตริงข้อความจำนวนมาก

1. เลือกรายการสตริงข้อความที่คุณต้องการแยกข้อความล่วงหน้าแล้วคลิก Kutools > ข้อความ > แยกข้อความ.

2. ในการโผล่ขึ้นมา แยกข้อความ คุณต้องกำหนดค่าดังต่อไปนี้

หมายเหตุ: ในการทำให้ผลลัพธ์เป็นไดนามิกเมื่อสตริงข้อความเปลี่ยนแปลง คุณสามารถตรวจสอบ แทรกเป็นสูตร กล่อง.

3. ในป๊อปอัพถัดไป แยกข้อความ กล่องโต้ตอบ เลือกเซลล์เพื่อส่งออกอักขระที่แยกออกมาแล้วคลิก OK.

จากนั้นอักขระ N ตัวแรกหรือตัวสุดท้ายที่ระบุจะถูกแยกจากเซลล์ที่เลือกเป็นกลุ่ม

คลิกเพื่อทราบข้อมูลเพิ่มเติมเกี่ยวกับคุณลักษณะนี้

---

2. แยกข้อความก่อนหรือหลังอักขระ/คำบางคำ

หากต้องการแยกข้อความก่อนหรือหลังอักขระหรือคำบางคำ สถานการณ์ต่างๆ ในส่วนนี้จะตรงตามความต้องการของคุณ

2.1 แยกข้อความก่อนหรือหลังตัวคั่นแรก (อักขระ)

ดังที่แสดงในภาพหน้าจอด้านล่าง หากต้องการแยกข้อความก่อนหรือหลังตัวคั่นแรกจากแต่ละเซลล์ในช่วง B4:B10 คุณสามารถใช้วิธีใดวิธีหนึ่งด้านล่างนี้

2.1.1 แยกข้อความก่อนตัวคั่นแรกด้วยสูตร

การใช้สูตรโดยยึดตามฟังก์ชัน LEFT และ FIND สามารถช่วยในการแยกข้อความก่อนตัวคั่นแรกจากเซลล์ คุณสามารถทำตามขั้นตอนด้านล่างเพื่อดำเนินการให้เสร็จสิ้น

สูตรทั่วไป

=LEFT(text_string,FIND("delimiter",text_string,1)-1)

ข้อโต้แย้ง

เลือกเซลล์ว่าง คัดลอกหรือป้อนสูตรด้านล่างแล้วกด เข้าสู่ ที่สำคัญเพื่อให้ได้ผลลัพธ์แรก เลือกเซลล์ผลลัพธ์แรกแล้วลากจุดจัดการการป้อนอัตโนมัติลงเพื่อรับข้อความก่อนตัวคั่นแรกของเซลล์อื่น

=LEFT(B5,FIND("-",B5,1)-1)

2.1.2 แยกข้อความหลังตัวคั่นแรกด้วยสูตร

สูตรด้านล่างช่วยในการแยกข้อความหลังจากตัวคั่นแรกจากเซลล์ใน Excel

สูตรทั่วไป

=MID(text_string,FIND("delimiter",text_string)+1,LEN(text_string))

ข้อโต้แย้ง

=MID(B5,FIND("-",B5)+1,LEN(B5))

2.1.3 แยกข้อความก่อนหรือหลังตัวคั่นแรกด้วยเครื่องมือที่น่าทึ่ง

ที่นี่ขอแนะนำ แยกข้อความ ประโยชน์ของ Kutools for Excel. ด้วยคุณสมบัตินี้ คุณสามารถแยกข้อความก่อนหรือหลังตัวคั่นแรกจากช่วงของเซลล์จำนวนมากได้อย่างง่ายดาย

1. เลือกช่วงของเซลล์ที่คุณต้องการแยกข้อความ จากนั้นคลิก Kutools > ข้อความ > แยกข้อความ.

2 ใน แยกข้อความ คุณต้องกำหนดค่าดังต่อไปนี้

หมายเหตุ ในการทำให้ผลลัพธ์เป็นไดนามิกเมื่อสตริงข้อความเปลี่ยนแปลง คุณสามารถเลือกกล่องแทรกเป็นสูตรได้

3. แล้วอีกอย่าง แยกข้อความ กล่องโต้ตอบปรากฏขึ้น เลือกเซลล์เพื่อแสดงผลลัพธ์และคลิก ตกลง

จากนั้นข้อความก่อนหรือหลังตัวคั่นแรกจะถูกดึงออกจากเซลล์ที่เลือกพร้อมกัน

หากต้องการทราบข้อมูลเพิ่มเติมเกี่ยวกับคุณลักษณะนี้ โปรดไปที่: แยกข้อความบางส่วนออกจากเซลล์ใน Excel . อย่างรวดเร็ว.

2.2 แยกข้อความก่อนหรือหลังตัวคั่นสุดท้าย (อักขระ)

ในขั้นตอนข้างต้น เราได้เรียนรู้วิธีการแยกข้อความก่อนหรือหลังตัวคั่นแรกจากเซลล์ ดังที่แสดงในภาพหน้าจอด้านล่าง ส่วนนี้จะแสดงสูตรสองสูตรเพื่อแยกข้อความก่อนหรือหลังตัวคั่นสุดท้ายจากเซลล์ คุณสามารถทำตามขั้นตอนด้านล่างเพื่อดำเนินการให้เสร็จสิ้น

2.2.1 แยกข้อความก่อนตัวคั่นสุดท้ายด้วยสูตร

หากต้องการแยกข้อความก่อนตัวคั่นสุดท้ายจากเซลล์ คุณสามารถใช้ฟังก์ชัน SEARCH, LEN และ SUBSTITUTE ภายในฟังก์ชัน LEFT

สูตรทั่วไป

=LEFT(text_string,SEARCH("#",SUBSTITUTE(text_string,"delimiter","#",LEN(text_string)-LEN(SUBSTITUTE(text_string,"delimiter",""))))-1)

ข้อโต้แย้ง

เลือกเซลล์ ป้อนสูตรด้านล่างแล้วกด เข้าสู่ ที่สำคัญเพื่อให้ได้ผลลัพธ์ เลือกเซลล์ผลลัพธ์นี้แล้วลากที่จับป้อนอัตโนมัติลงเพื่อแยกข้อความจากสตริงข้อความอื่นๆ ในคอลัมน์เดียวกัน

=LEFT(B5,SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-",""))))-1)

2.2.2 แยกข้อความหลังตัวคั่นสุดท้ายด้วยสูตร

หลังจากแยกข้อความก่อนตัวคั่นสุดท้ายจากเซลล์ คุณสามารถใช้สูตรด้านล่างเพื่อแยกข้อความหลังตัวคั่นสุดท้ายตามที่คุณต้องการ

สูตรทั่วไป

=RIGHT(text_string,LEN(text_string)-SEARCH("#",SUBSTITUTE(text_string,"delimiter","#",LEN(text_string)-LEN(SUBSTITUTE(text_string,"delimiter","")))))

ข้อโต้แย้ง

เลือกเซลล์ ป้อนสูตรด้านล่างแล้วกด เข้าสู่ ที่สำคัญเพื่อให้ได้ผลลัพธ์ เลือกเซลล์ผลลัพธ์นี้แล้วลากที่จับป้อนอัตโนมัติลงเพื่อแยกข้อความจากสตริงข้อความอื่นๆ ในคอลัมน์เดียวกัน

=RIGHT(B5,LEN(B5)-SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-","")))))

2.3 แยกข้อความหลังอักขระที่ n

ดูตัวอย่างด้านล่าง มีรายการสตริงข้อความอยู่ในช่วง B4:B10 หากต้องการแยกข้อความตามหลังอักขระที่ XNUMX จากแต่ละเซลล์ คุณสามารถใช้สูตรตามฟังก์ชัน MID และฟังก์ชัน LEN

สูตรทั่วไป

=MID(text_string,nth_char+1,LEN(text_string))

ข้อโต้แย้ง

เลือกเซลล์ว่าง คัดลอกหรือป้อนสูตรด้านล่างแล้วกด เข้าสู่ ที่สำคัญเพื่อให้ได้ผลลัพธ์ เลือกเซลล์ผลลัพธ์นี้แล้วลากที่จับป้อนอัตโนมัติลงไปเพื่อรับผลลัพธ์อื่นๆ

=MID(B5,3+1,LEN(B5))

2.4 แยกคำที่ n จากสตริงข้อความ

สมมติว่าคุณมีรายการสตริงข้อความตามที่แสดงในภาพหน้าจอด้านล่าง และต้องการแยกเฉพาะคำที่ n ออกจากสตริงข้อความ ส่วนนี้มีสามวิธีเพื่อให้คุณดำเนินการให้เสร็จสิ้น

2.4.1 แยกคำที่ n ด้วยสูตร

คุณสามารถรวมฟังก์ชัน TRIM, MID, SUBSTITUTE, REPT และ LEN เพื่อแยกคำที่ n ออกจากสตริงข้อความในเซลล์ได้

สูตรทั่วไป

=TRIM(MID(SUBSTITUTE(text_string," ",REPT(" ",LEN((text_string))), (N-1)*LEN((text_string)+1, LEN((text_string)))

ข้อโต้แย้ง

ในกรณีนี้ ช่วง B5:B10 มีสตริงข้อความ D5:D10 มีตัวเลขที่แสดงคำที่ n ลองใช้สูตรนี้เพื่อแยกคำที่ n ออกจากสตริงข้อความ

เลือกเซลล์ว่าง คัดลอกหรือป้อนสูตรด้านล่างแล้วกด เข้าสู่ ที่สำคัญเพื่อให้ได้ผลลัพธ์แรก เลือกเซลล์ผลลัพธ์นี้แล้วลากที่จับป้อนอัตโนมัติลงไปเพื่อรับคำที่ n ของเซลล์อื่น

=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (D5-1)*LEN(B5)+1, LEN(B5)))

หมายเหตุ คุณสามารถพิมพ์ตัวเลขที่ n ในสูตรได้โดยตรงดังนี้

=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (2-1)*LEN(B5)+1, LEN(B5)))

2.4.2 แยกคำที่ n ด้วยฟังก์ชันที่ผู้ใช้กำหนด

นอกจากสูตรข้างต้นแล้ว คุณยังสามารถใช้ฟังก์ชันที่ผู้ใช้กำหนดเองเพื่อแยกคำที่ n ออกจากเซลล์ใน Excel ได้อีกด้วย

1 กด อื่น ๆ + F11 คีย์เพื่อเปิด Microsoft Visual Basic สำหรับแอปพลิเคชัน หน้าต่าง

2 ใน Microsoft Visual Basic สำหรับแอปพลิเคชัน หน้าต่างคลิก สิ่งที่ใส่เข้าไป > โมดูล, แล้วคัดลอก VBA ด้านล่างลงในหน้าต่างรหัส

รหัส VBA: แยกคำที่ n ออกจากสตริงข้อความในเซลล์

Function ExtractTheNthWord(Source As String, Position As Integer)
'Update by Extendoffice 20211202
Dim arr() As String
arr = VBA.Split(Source, " ")
xCount = UBound(arr)
If xCount < 1 Or (Position - 1) > xCount Or Position < 0 Then
    FindWord = ""
Else
    FindWord = arr(Position - 1)
End If
End Function

3 กด อื่น ๆ + Q ปุ่มเพื่อปิดไฟล์ Microsoft Visual Basic สำหรับแอปพลิเคชัน หน้าต่าง

4. กลับไปที่แผ่นงานที่มีสตริงข้อความที่คุณต้องการแยกคำที่ n จาก เลือกเซลล์ว่าง คัดลอกหรือป้อนสูตรด้านล่างแล้วกด เข้าสู่ กุญแจสำคัญในการรับคำที่ n

=FindWord(B5,D5)

Or

=FindWord(B5,2)

หมายเหตุ ในสูตร D5 คือเซลล์ที่มีตัวเลขแทนคำที่ n หรือคุณสามารถแทนที่การอ้างอิงเซลล์ด้วยตัวเลขได้โดยตรง

5. เลือกเซลล์ผลลัพธ์แล้วลาก AutoFill Handle ลงไปเพื่อแยกคำที่ n ออกจากสตริงข้อความของเซลล์อื่น

2.4.3 แยกคำที่ n ด้วยเครื่องมือที่น่าทึ่ง

หากคุณไม่ต้องการใช้สูตรหรือฟังก์ชันที่ผู้ใช้กำหนดด้วยตนเองที่ให้ไว้ด้านบน ขอแนะนำที่นี่ Kutools for Excel's แยกคำที่ n ในเซลล์ คุณประโยชน์. ด้วยคุณสมบัตินี้ คุณสามารถแยกคำที่ n ออกจากสตริงข้อความในเซลล์ได้ด้วยการคลิกเพียงไม่กี่ครั้ง

1. เลือกเซลล์ที่จะวางผลลัพธ์ แล้วคลิก Kutools > ตัวช่วยสูตร > ข้อความ > สารสกัด คำที่ n ในเซลล์. ดูภาพหน้าจอ:

2 ใน ตัวช่วยสูตร คุณต้องกำหนดค่าดังต่อไปนี้

3. จากนั้นคำที่ n (วินาที) จะถูกดึงออกจากสตริงข้อความในเซลล์ B5 และคุณจะเห็นสูตรถูกสร้างขึ้นในเวลาเดียวกัน เลือกเซลล์ผลลัพธ์นี้แล้วลากที่จับป้อนอัตโนมัติลงไปเพื่อรับคำที่ n จากสตริงข้อความอื่นๆ

คลิกเพื่อทราบข้อมูลเพิ่มเติมเกี่ยวกับคุณลักษณะนี้

2.5 แยกข้อความก่อนหรือหลังการเกิดตัวคั่นครั้งที่ n

สมมติว่าคุณมีรายการสตริงข้อความตามที่แสดงในภาพหน้าจอด้านล่าง ในการแยกข้อความก่อนหรือหลังการเว้นวรรคครั้งที่สอง ส่วนนี้จะมีสูตรสองสูตรที่จะช่วยให้คุณทำเสร็จ

2.5.1 แยกข้อความก่อนการเกิดตัวคั่นครั้งที่ n

คุณสามารถใช้ฟังก์ชัน LEFT ร่วมกับฟังก์ชัน SUBSTITUTE และ FIND เพื่อแยกข้อความก่อนตัวคั่นที่ n เกิดขึ้นจากเซลล์ใน Excel

สูตรทั่วไป

=LEFT(SUBSTITUTE(text_string,"delimiter",CHAR(9),n),FIND(CHAR(9),SUBSTITUTE(text_string,"delimiter",CHAR(9),n),1)-1)

ข้อโต้แย้ง

เลือกเซลล์ คัดลอกหรือป้อนสูตรด้านล่างแล้วกด เข้าสู่ ที่สำคัญเพื่อให้ได้ผลลัพธ์ เลือกเซลล์ผลลัพธ์นี้แล้วลากจัดการป้อนอัตโนมัติลงเพื่อรับผลลัพธ์อื่นๆ ในรายการ

=LEFT(SUBSTITUTE(B5," ",CHAR(9),2),FIND(CHAR(9),SUBSTITUTE(B5," ",CHAR(9),2),1)-1)

หมายเหตุ ในสูตร B5 คือเซลล์ที่มีสตริงข้อความที่คุณต้องการแยกข้อความ “ ” ในที่นี้แทนช่องว่างและตัวเลข 2 หมายถึงการเกิดขึ้นครั้งที่สองของช่องว่าง คุณสามารถเปลี่ยนได้ตามความต้องการของคุณ

2.5.2 แยกข้อความหลังจากเกิดตัวคั่นครั้งที่ n

หากต้องการแยกข้อความหลังจากตัวคั่นเกิดขึ้นครั้งที่ n คุณสามารถใช้ฟังก์ชัน RIGHT กับฟังก์ชัน SUBSTITUTE, LEN และ FIND

สูตรทั่วไป

=RIGHT(SUBSTITUTE(text_string, "delimiter", CHAR(9), n), LEN(text_string)- FIND(CHAR(9), SUBSTITUTE(text_string, "delimiter", CHAR(9), n), 1) + 1)

ข้อโต้แย้ง

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

เลือกเซลล์ ป้อนสูตรด้านล่างแล้วกด เข้าสู่ ที่สำคัญเพื่อให้ได้ผลลัพธ์ เลือกเซลล์ผลลัพธ์นี้แล้วลากจัดการป้อนอัตโนมัติลงเพื่อรับผลลัพธ์อื่นๆ

=RIGHT(SUBSTITUTE(B5, " ", CHAR(9), 2), LEN(B5)- FIND(CHAR(9), SUBSTITUTE(B5, " ", CHAR(9), 2), 1) + 1)

2.6 แยกข้อความก่อนหรือหลังตัวแบ่งบรรทัด

สมมติว่าคุณมีรายการสั่งซื้อในคอลัมน์ B และคุณต้องการแยกเฉพาะส่วนวันที่และส่วนหมายเลขผลิตภัณฑ์ออกจากแต่ละเซลล์ คุณสามารถใช้สูตร Excel ด้านล่างเพื่อดำเนินการให้เสร็จสิ้น

2.6.1 แยกข้อความก่อนขึ้นบรรทัดแรกด้วยสูตร

ดังที่คุณเห็นในภาพหน้าจอด้านบน ส่วนวันที่จะอยู่ก่อนตัวแบ่งบรรทัดแรกภายในเซลล์ ส่วนนี้จะแสดงฟังก์ชัน LEFT พร้อมกับฟังก์ชัน SEARCH เพื่อช่วยคุณแยกข้อความก่อนที่จะขึ้นบรรทัดแรกภายในเซลล์

สูตรทั่วไป

=LEFT(cell, SEARCH(CHAR(10), cell)-1)

ข้อโต้แย้ง

เลือกเซลล์ว่าง คัดลอกหรือป้อนสูตรด้านล่างแล้วกด เข้าสู่ ที่สำคัญเพื่อให้ได้ผลลัพธ์ เลือกเซลล์ผลลัพธ์นี้แล้วลากจุดจัดการการป้อนอัตโนมัติลงเพื่อใช้สูตรนี้กับเซลล์อื่น

=LEFT(B5, SEARCH(CHAR(10), B5)-1)

จากนั้น คุณจะเห็นข้อความก่อนที่ตัวแบ่งบรรทัดแรกในแต่ละเซลล์ในช่วง B5:B8 จะถูกแยกออกมาดังแสดงในภาพหน้าจอด้านล่าง

หมายเหตุ ในสูตร CHAR(10) แสดงถึงตัวแบ่งบรรทัดบน Windows

2.6.2 แยกข้อความหลังตัวแบ่งบรรทัดสุดท้ายด้วยสูตร

ในขั้นตอนที่แล้ว เราได้พูดถึงวิธีการแยกข้อความก่อนขึ้นบรรทัดใหม่ภายในเซลล์ และส่วนนี้จะแนะนำวิธีการแยกข้อความหลังจากตัวแบ่งบรรทัดสุดท้ายภายในเซลล์ด้วยสูตรอื่น

สูตรทั่วไป

=TRIM(RIGHT(SUBSTITUTE(cell,CHAR(10),REPT(" ",200)),200))

ข้อโต้แย้ง

เลือกเซลล์ว่าง ใส่สูตรด้านล่างแล้วกด เข้าสู่ ที่สำคัญเพื่อให้ได้ผลลัพธ์ เลือกเซลล์ผลลัพธ์นี้แล้วลากจุดจัดการการป้อนอัตโนมัติลงเพื่อใช้สูตรกับเซลล์อื่น

=TRIM(RIGHT(SUBSTITUTE(B5,CHAR(10),REPT(" ",200)),200))

จากนั้นหมายเลขผลิตภัณฑ์ ส่วนหนึ่งของแต่ละเซลล์ในรายการจะถูกแยกออกมาตามที่แสดงในภาพหน้าจอด้านบน

หมายเหตุ ในสูตร CHAR(10) แสดงถึงตัวแบ่งบรรทัดบน Windows

2.7 แยกข้อความก่อนหรือหลังคำ

ในส่วนก่อนหน้านี้ เราได้เรียนรู้วิธีการแยกข้อความก่อนหรือหลังอักขระหรือตัวคั่น คุณควรทำอย่างไรเพื่อแยกข้อความก่อนหรือหลังทั้งคำ ส่วนนี้จะแนะนำสามวิธีที่จะช่วยให้คุณทำงานนี้สำเร็จ

2.7.1 แยกข้อความก่อนคำบางคำด้วยสูตร

สูตรต่อไปนี้ช่วยให้คุณแยกข้อความก่อนคำบางคำภายในเซลล์ใน Excel

สูตรทั่วไป

=IFERROR(LEFT(cell,FIND(word,cell)-1),cell)

ข้อโต้แย้ง

เลือกเซลล์ว่าง ใส่สูตรด้านล่างแล้วกด เข้าสู่ ที่สำคัญเพื่อให้ได้ผลลัพธ์ เลือกเซลล์ผลลัพธ์นี้แล้วลากจุดจัดการการป้อนอัตโนมัติลงเพื่อใช้สูตรนี้กับเซลล์อื่น

ในตัวอย่างนี้ เราจะแยกข้อความทั้งหมดก่อนคำว่า "Excel" ดังนั้นเราจึงพิมพ์คำนั้นในสูตรโดยตรงและใส่ไว้ในเครื่องหมายคำพูดคู่ หรือคุณสามารถอ้างอิงไปยังเซลล์ที่มีคำว่า “Excel”

=IFERROR(LEFT(B5,FIND("Excel",B5)-1),B5)

หมายเหตุ:

2.7.2 แยกข้อความหลังคำบางคำด้วยสูตร

หากต้องการแยกข้อความหลังจากคำบางคำ คุณสามารถใช้สูตรต่อไปนี้เพื่อดำเนินการให้เสร็จสิ้น

สูตรทั่วไป

=TRIM(MID(cell,SEARCH(word,cell)+LEN(word),255))

ข้อโต้แย้ง

เลือกเซลล์ ป้อนสูตรด้านล่างแล้วกด เข้าสู่ ที่สำคัญเพื่อให้ได้ผลลัพธ์ เลือกเซลล์ผลลัพธ์แล้วลากจุดจัดการการป้อนอัตโนมัติลงเพื่อใช้สูตรนี้กับเซลล์อื่น

=TRIM(MID(B5,SEARCH("Excel",B5)+LEN("Excel"),255))

จากนั้น คุณจะเห็นข้อความทั้งหมดหลังจากแยกคำว่า “Excel” ในแต่ละเซลล์ออกมาดังที่แสดงในภาพหน้าจอด้านล่าง

หมายเหตุ:

2.7.3 แยกข้อความก่อนหรือหลังคำบางคำด้วยเครื่องมือที่น่าทึ่ง

หากคุณรู้สึกว่าการใช้สูตรอาจทำให้เกิดความไม่สะดวกมากมาย ที่นี่ขอแนะนำ แยกข้อความ ประโยชน์ของ Kutools for Excel. ฟีเจอร์นี้ช่วยให้งานการดึงข้อมูลใน Excel ทำงานโดยอัตโนมัติด้วยการคลิกเพียงไม่กี่ครั้ง

1 คลิก Kutools > ข้อความ > แยกข้อความ เพื่อเปิดใช้งานคุณสมบัตินี้

2 ใน แยกข้อความ กล่องโต้ตอบ ทำการตั้งค่าต่อไปนี้

หมายเหตุ: หากคุณต้องการสร้างผลลัพธ์แบบไดนามิก ให้ทำเครื่องหมายที่ แทรกเป็นสูตร กล่อง. จากนั้นผลลัพธ์จะอัปเดตโดยอัตโนมัติเมื่อข้อมูลในช่วงเปลี่ยนแปลง

3. จากนั้น แยกข้อความ กล่องโต้ตอบปรากฏขึ้น คุณต้องเลือกเซลล์เพื่อแสดงผลลัพธ์ จากนั้นคลิก OK ปุ่ม

จากนั้นข้อความก่อนหรือหลังคำบางคำภายในแต่ละเซลล์ในช่วงที่เลือกจะถูกแยกออกทันที

หมายเหตุ คุณลักษณะนี้คำนึงถึงขนาดตัวพิมพ์

คลิกเพื่อทราบข้อมูลเพิ่มเติมเกี่ยวกับคุณลักษณะนี้

---

3. แยกระหว่างตัวอักษร/คำ

หากคุณต้องการแยกข้อความระหว่างอักขระหรือคำบางคำ ให้ลองใช้วิธีต่อไปนี้

3.1 แยกข้อความระหว่างอักขระสองตัว

เพื่อแยกข้อความระหว่างอักขระสองตัวซึ่งอาจจะเป็นอักขระตัวเดียวกันหรือต่างกันก็ได้ ส่วนนี้มีวิธีการต่างๆ มากมาย และคุณสามารถเลือกวิธีใดวิธีหนึ่งได้ตามความต้องการของคุณ

3.1.1 แยกข้อความระหว่างอักขระสองตัวที่เหมือนกันด้วยสูตร

ดังที่แสดงในภาพหน้าจอด้านล่าง มีรายการสตริงข้อความในคอลัมน์ B และคุณต้องการแยกส่วนตัวเลขระหว่างอักขระ “/” จากแต่ละเซลล์ในช่วงนั้น สูตรต่อไปนี้สามารถช่วยคุณได้

เลือกเซลล์ว่าง คัดลอกหรือป้อนสูตรด้านล่างแล้วกด เข้าสู่ ที่สำคัญเพื่อให้ได้ผลลัพธ์ เลือกเซลล์ผลลัพธ์แล้วลากจัดการป้อนอัตโนมัติลงเพื่อให้ได้ผลลัพธ์ของเซลล์อื่นในรายการ

=SUBSTITUTE(MID(SUBSTITUTE("/" & B5&REPT(" ",6),"/",REPT(",",255)),2*255,255),",","")

จากนั้นข้อความระหว่างอักขระสองตัวที่เหมือนกัน "/" จะถูกแยกจากแต่ละเซลล์ในช่วง ดูภาพหน้าจอ:

หมายเหตุ

3.1.2 แยกข้อความระหว่างอักขระสองตัวที่แตกต่างกันด้วยสูตร

หลังจากเรียนรู้วิธีแยกข้อความระหว่างอักขระสองตัวที่เหมือนกันภายในเซลล์ เราจะสาธิตสูตรเพื่อแยกข้อความระหว่างอักขระสองตัวที่ต่างกัน ดังที่แสดงในภาพหน้าจอด้านล่าง หากต้องการแยกเฉพาะที่อยู่อีเมลระหว่าง “<” และ “>” จากแต่ละเซลล์ในคอลัมน์ B คุณสามารถทำได้ดังนี้

สูตรทั่วไป

=MID(LEFT(cel,FIND("end_char",cell)-1),FIND("start_char",cell)+1,LEN(cell))

ข้อโต้แย้ง

เลือกเซลล์ว่าง คัดลอกหรือป้อนสูตรด้านล่างแล้วกด เข้าสู่ ที่สำคัญเพื่อให้ได้ผลลัพธ์ เลือกเซลล์ผลลัพธ์นี้แล้วลากจุดจัดการการป้อนอัตโนมัติลงเพื่อใช้สูตรนี้กับเซลล์อื่น

=MID(LEFT(B5,FIND(">",B5)-1),FIND("<",B5)+1,LEN(B5))

คุณจะเห็นว่าเฉพาะข้อความระหว่างอักขระที่ระบุเท่านั้นที่จะถูกดึงออกมาดังที่แสดงในภาพหน้าจอด้านบน

3.1.3 แยกข้อความระหว่างอักขระสองตัวด้วยเครื่องมือที่น่าทึ่ง

ที่นี่ขอแนะนำ แยกสตริงระหว่างข้อความที่ระบุ คุณลักษณะของ Kutools for Excel เพื่อช่วยให้คุณแยกข้อความระหว่างอักขระสองตัวที่เหมือนกันหรือต่างกันภายในเซลล์ใน Excel ได้อย่างง่ายดาย

1. เลือกเซลล์ว่างเพื่อแสดงผลลัพธ์จากนั้นคลิก Kutools > ตัวช่วยสูตร > ตัวช่วยสูตร.

2 ใน ตัวช่วยสูตร กล่องโต้ตอบ ทำการตั้งค่าต่อไปนี้

3. จากนั้นจะแยกเฉพาะข้อความระหว่าง “<” และ “>” ภายในเซลล์ B5 เท่านั้น ในขณะเดียวกัน มีการสร้างสูตรแล้ว คุณสามารถเลือกเซลล์ผลลัพธ์นี้ แล้วลากการจัดการป้อนอัตโนมัติลงเพื่อแยกข้อความจากเซลล์อื่นในรายการเดียวกัน

คลิกเพื่อทราบข้อมูลเพิ่มเติมเกี่ยวกับคุณลักษณะนี้

3.1.4 แยกข้อความระหว่างอักขระสองตัว (รวมทั้งอักขระ) ตามกฎ

ถ้าคุณต้องการเก็บอักขระสองตัวไว้หลังจากแยกแล้ว ให้ลองใช้กฎในคุณสมบัติแยกข้อความของ Kutools for Excel.

1 คลิก Kutools > ข้อความ > แยกข้อความ.

2 ใน แยกข้อความ กล่องโต้ตอบ ทำการตั้งค่าต่อไปนี้

3. อื่น ๆ แยกข้อความ กล่องโต้ตอบปรากฏขึ้น กรุณาเลือกเซลล์ที่ต้องการแสดงผลลัพธ์ จากนั้นคลิก OK ปุ่ม

จากนั้นข้อความระหว่างอักขระที่ระบุ (รวมถึงอักขระ) จะถูกแยกจากแต่ละเซลล์ในช่วงที่เลือกเป็นกลุ่ม

3.2 แยกข้อความระหว่างสองคำ

นอกจากการแยกข้อความระหว่างอักขระสองตัว คุณยังอาจต้องแยกข้อความระหว่างสองคำด้วย ตัวอย่างเช่น แยกสตริงข้อความทั้งหมดระหว่างคำสองคำ "KTE" และ "คุณลักษณะ" จากแต่ละเซลล์ในคอลัมน์ B ตามที่แสดงในภาพหน้าจอด้านล่าง คุณสามารถลองใช้วิธีใดวิธีหนึ่งต่อไปนี้เพื่อดำเนินการให้เสร็จสิ้น

3.2.1 แยกข้อความระหว่างคำสองคำด้วยสูตร

คุณสามารถใช้สูตรโดยยึดตามฟังก์ชัน MID และฟังก์ชัน SEARCH เพื่อแยกสตริงข้อความทั้งหมดระหว่างคำสองคำภายในเซลล์

สูตรทั่วไป

=MID(cell,SEARCH("start_word",cell)+3,SEARCH("end_word",cell)-SEARCH("start_word",cell)-4)

ข้อโต้แย้ง

เลือกเซลล์ว่าง คัดลอกหรือป้อนสูตรด้านล่างแล้วกด เข้าสู่ ที่สำคัญเพื่อให้ได้ผลลัพธ์ เลือกเซลล์ผลลัพธ์นี้แล้วลากจุดจัดการการป้อนอัตโนมัติลงเพื่อใช้สูตรนี้กับเซลล์อื่น

=MID(B5,SEARCH("KTE",B5)+3,SEARCH("feature",B5)-SEARCH("KTE",B5)-4)

หมายเหตุ: ในสูตร หมายเลข 3 หมายถึงความยาวของอักขระของคำว่า "KTE" ตัวเลข 4 หมายถึงความยาวอักขระของคำว่า "KTE" บวก 1

คุณสามารถดูสตริงข้อความทั้งหมดระหว่างคำสองคำที่ระบุซึ่งแยกออกมาจากแต่ละเซลล์ในคอลัมน์ B

3.2.2 แยกข้อความระหว่างคำสองคำด้วยเครื่องมือที่น่าทึ่ง

สำหรับผู้ใช้ Excel หลายคน สูตรอาจจำและจัดการได้ยาก ที่นี่ด้วย แยกสตริงระหว่างข้อความที่ระบุ คุณลักษณะของ Kutools for Excelคุณสามารถแยกข้อความระหว่างคำสองคำได้ง่ายๆ ด้วยการคลิกเพียงไม่กี่ครั้ง

1. เลือกเซลล์ที่จะส่งออกผลลัพธ์ จากนั้นคลิก Kutools > ตัวช่วยสูตร > ตัวช่วยสูตร.

2 ใน ตัวช่วยสูตร คุณต้องกำหนดค่าดังต่อไปนี้

3. จากนั้นสตริงข้อความทั้งหมดระหว่างคำสองคำ "KTE" และ "คุณลักษณะ" ภายในเซลล์ B5 จะถูกแยกออก ในขณะเดียวกัน มีการสร้างสูตรแล้ว คุณสามารถเลือกเซลล์ผลลัพธ์นี้ แล้วลากการจัดการป้อนอัตโนมัติลงเพื่อแยกข้อความจากเซลล์อื่นในรายการเดียวกัน

---

แยกตัวเลขตามตำแหน่ง

สำหรับรายการสตริงที่เป็นตัวอักษรและตัวเลขคละกัน อาจมีสามกรณี:

1. ตัวเลขอยู่ต้นข้อความ;
2. ตัวเลขอยู่ท้ายข้อความ;
3. ตัวเลขสามารถอยู่ที่ไหนก็ได้ในข้อความ.

ในส่วนนี้เราจะนำเสนอวิธีการต่าง ๆ ที่สามารถใช้ในการแยกตัวเลขในแต่ละกรณีที่กล่าวถึงข้างต้น

1 แยกตัวเลขจากด้านซ้ายของสตริง

ส่วนนี้จะแนะนำสูตรเพื่อช่วยให้คุณแยกเฉพาะตัวเลขที่ปรากฏก่อนข้อความในเซลล์

สูตรทั่วไป

=LEFT(cell, MATCH(FALSE, ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell)+1)), 1) *1), 0) -1)

ข้อโต้แย้ง

หมายเหตุ

เลือกเซลล์ว่าง ใส่สูตรด้านล่างแล้วกด Ctrl + เปลี่ยน + เข้าสู่ or เข้าสู่ ที่สำคัญเพื่อให้ได้ผลลัพธ์ เลือกเซลล์ผลลัพธ์นี้แล้วลากจุดจัดการการป้อนอัตโนมัติลงเพื่อรับตัวเลขของเซลล์อื่นๆ

=LEFT(B5, MATCH(FALSE, ISNUMBER(MID(B5, ROW(INDIRECT("1:"&LEN(B5)+1)), 1) *1), 0) -1)

หมายเหตุ:

---

2 แยกตัวเลขจากด้านขวาของสตริง

ดังที่แสดงในภาพหน้าจอด้านล่าง หากต้องการแยกเฉพาะตัวเลขที่ปรากฏหลังข้อความในเซลล์ ให้ลองใช้สูตรต่อไปนี้

สูตรทั่วไป

=RIGHT(cell, LEN(cell) - MAX(IF(ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(cell))), 0)))

ข้อโต้แย้ง

หมายเหตุ

เลือกเซลล์ว่าง ใส่สูตรด้านล่างแล้วกด Ctrl + เปลี่ยน + เข้าสู่ or เข้าสู่ ที่สำคัญเพื่อให้ได้ผลลัพธ์ เลือกเซลล์ผลลัพธ์นี้แล้วลากจุดจัดการการป้อนอัตโนมัติลงเพื่อรับตัวเลขของเซลล์อื่นๆ

=RIGHT(B5, LEN(B5) - MAX(IF(ISNUMBER(MID(B5, ROW(INDIRECT("1:"&LEN(B5))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(B5))), 0)))

หมายเหตุ:

---

3. แยกตัวเลขทั้งหมดจากตำแหน่งใดๆ ในสตริงข้อความ

วิธีการข้างต้นช่วยในการแยกตัวเลขจากด้านซ้ายหรือด้านขวาของสตริงข้อความเท่านั้น หากคุณต้องการแยกตัวเลขทั้งหมดจากที่ใดก็ได้ในสตริงข้อความ เรามีสามวิธีในการดำเนินการให้คุณ

3.1 แยกตัวเลขทั้งหมดจากที่ใดก็ได้ในสตริงด้วยสูตร

คุณสามารถใช้สูตรต่อไปนี้เพื่อแยกตัวเลขทั้งหมดจากที่ใดก็ได้ในสตริงข้อความใน Excel

1. เลือกเซลล์ว่าง คัดลอกหรือป้อนสูตรด้านล่างแล้วกด เข้าสู่ คีย์เพื่อรับตัวเลขทั้งหมดจากเซลล์ B5

=SUMPRODUCT(MID(0&B5, LARGE(INDEX(ISNUMBER(--MID(B5, ROW(INDIRECT("1:"&LEN(B5))), 1)) * ROW(INDIRECT("1:"&LEN(B5))), 0), ROW(INDIRECT("1:"&LEN(B5))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(B5)))/10)

2. เลือกเซลล์ผลลัพธ์แล้วลาก AutoFill Handle ลงเพื่อให้ได้ตัวเลขทั้งหมดของเซลล์อื่น

3.2 แยกตัวเลขทั้งหมดจากที่ใดก็ได้ในสตริงด้วยVBA

สูตรข้างต้นยาวเกินไปและซับซ้อนสำหรับผู้ใช้ Excel หลายคน ที่จริงแล้ว คุณสามารถเรียกใช้สคริปต์ VBA เพื่อทำงานอัตโนมัติใน Excel ได้ คุณสามารถทำได้ดังนี้

1 กด อื่น ๆ + F11 คีย์เพื่อเปิด Microsoft Visual Basic สำหรับแอปพลิเคชัน หน้าต่าง

2. ในการเปิด Microsoft Visual Basic สำหรับแอปพลิเคชัน หน้าต่างคลิก สิ่งที่ใส่เข้าไป > โมดูล. จากนั้นคัดลอก VBA ด้านล่างลงในหน้าต่างรหัสโมดูล

รหัส VBA: แยกตัวเลขทั้งหมดจากที่ใดก็ได้ในสตริงข้อความ

Sub ExtrNumbersFromRange()
'Updated by Extendoffice 20220106
    Dim xRg As Range
    Dim xDRg As Range
    Dim xRRg As Range
    Dim nCellLength As Integer
    Dim xNumber As Integer
    Dim strNumber As String
    Dim xTitleId As String
    Dim xI As Integer
    xTitleId = "KutoolsforExcel"
    Set xDRg = Application.InputBox("Please select text strings:", xTitleId, "", Type:=8)
    If TypeName(xDRg) = "Nothing" Then Exit Sub
    Set xRRg = Application.InputBox("Please select output cell:", xTitleId, "", Type:=8)
    If TypeName(xRRg) = "Nothing" Then Exit Sub
    xI = 0
    strNumber = ""
  For Each xRg In xDRg
    xI = xI + 1
    nCellLength = Len(xRg)
    For xNumber = 1 To nCellLength
      If IsNumeric(Mid(xRg, xNumber, 1)) Then
        strNumber = strNumber & Mid(xRg, xNumber, 1)
      End If
    Next xNumber
    xRRg.Item(xI) = strNumber
    strNumber = ""
  Next xRg
End Sub

3 กด F5 กุญแจสำคัญในการเรียกใช้รหัส ในการเปิด KutoolsforExcel กล่องโต้ตอบ เลือกช่วงของเซลล์ที่คุณต้องการแยกตัวเลขทั้งหมดออกจากแต่ละเซลล์ จากนั้นคลิกปุ่ม OK ปุ่ม

4. แล้วอีกอย่าง KutoolsforExcel กล่องโต้ตอบปรากฏขึ้น ในกล่องโต้ตอบนี้ ให้เลือกเซลล์ปลายทางแล้วคลิก ตกลง

จากนั้นจะดึงตัวเลขทั้งหมดออกจากแต่ละเซลล์ในช่วงที่เลือกเป็นกลุ่ม

---

4. แยกตัวเลขหลังข้อความเฉพาะ

ดังที่แสดงในภาพหน้าจอด้านล่าง หากต้องการแยกตัวเลขใดๆ หลังข้อความ "ไม่" ที่ระบุ ส่วนนี้จะมี XNUMX วิธีในการช่วยคุณดำเนินการให้เสร็จสิ้น

4.1 แยกตัวเลขหลังข้อความเฉพาะด้วยสูตร

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

สูตรทั่วไป:

=LOOKUP(10^6,1*MID(cell,MIN(FIND({0,1,2,3,4,5,6,7,8,9},cell&"0123456789",FIND("text"," "&cell&" "))),{2,3,4,5,6}))

ข้อโต้แย้ง

เลือกเซลล์ว่าง คัดลอกหรือป้อนสูตรด้านล่างแล้วกด เข้าสู่ ที่สำคัญเพื่อให้ได้ผลลัพธ์ เลือกเซลล์ผลลัพธ์นี้แล้วลากจุดจัดการการป้อนอัตโนมัติลงเพื่อใช้สูตรนี้กับเซลล์อื่น

=LOOKUP(10^6,1*MID(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789",FIND("No."," "&B5&" "))),{2,3,4,5,6}))

หมายเหตุ:

4.2 แยกตัวเลขหลังข้อความเฉพาะด้วยฟังก์ชันที่ผู้ใช้กำหนด

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

1 กด อื่น ๆ + F11 คีย์เพื่อเปิด Microsoft Visual Basic สำหรับแอปพลิเคชัน หน้าต่าง

2 ใน Microsoft Visual Basic สำหรับแอปพลิเคชัน หน้าต่างคลิก สิ่งที่ใส่เข้าไป > โมดูล, จากนั้นคัดลอกโค้ด VBA ด้านล่างลงในหน้าต่างโค้ดโมดูล

รหัส VBA: แยกตัวเลขหลังข้อความที่ระบุในเซลล์

Function GetNumberAfterTheChar(Rng As Range, Char As String)
'Updated by Extendoffice 20220106
Dim xValue As String
Dim xRntString As String
Dim xStart As Integer
Dim xC
    xValue = Rng.Text
    xStart = InStr(1, xValue, Char, vbTextCompare)
    If IsEmpty(xStart) Then
            GetNumberAfterTheChar = ""
            Exit Function
    End If
    If xStart < 1 Then
        GetNumberAfterTheChar = ""
        Exit Function
    End If
    xStart = xStart - 1 + Len(Char)
    If xStart < 1 Then
        GetNumberAfterTheChar = ""
        Exit Function
    End If
    xValue = Mid(xValue, xStart + 1)
    xRntString = ""
    For xI = 1 To Len(xValue)
        xC = Mid(xValue, xI, 1)
        Select Case Asc(xC)
        Case 48 To 57
            xRntString = xRntString & xC
       Case Else
            Exit For
        End Select
    Next
   GetNumberAfterTheChar = xRntString
End Function

3 กด อื่น ๆ + Q ปุ่มเพื่อปิดไฟล์ Microsoft Visual Basic สำหรับแอปพลิเคชัน หน้าต่าง

4. เลือกเซลล์ ป้อนสูตรด้านล่าง แล้วกด เข้าสู่ กุญแจ. เลือกเซลล์ผลลัพธ์นี้แล้วลากจุดจัดการการป้อนอัตโนมัติลงเพื่อใช้สูตรนี้กับเซลล์อื่น

=GetNumberAfterTheChar(B5,"No. ")

หมายเหตุ:

---

บทความที่เกี่ยวข้อง: