แยกตัวเลขจากสตริงใน Excel (โดยใช้สูตรหรือ VBA)

ไม่มีฟังก์ชันในตัวใน Excel เพื่อแยกตัวเลขออกจากสตริงในเซลล์ (และในทางกลับกัน - ลบส่วนที่เป็นตัวเลขและแยกส่วนข้อความออกจากสตริงที่เป็นตัวอักษรและตัวเลขคละกัน)

อย่างไรก็ตาม สามารถทำได้โดยใช้ฟังก์ชัน Excel หรือโค้ด VBA แบบง่าย

ให้ฉันบอกคุณก่อนว่าฉันกำลังพูดถึงอะไร

สมมติว่าคุณมีชุดข้อมูลด้านล่างและต้องการแยกตัวเลขออกจากสตริง (เช่นด้านล่าง):

วิธีการที่คุณเลือกยังขึ้นอยู่กับเวอร์ชันของ Excel ที่คุณใช้อีกด้วย:

• สำหรับ Excel เวอร์ชันก่อนปี 2016 คุณต้องใช้สูตรที่ยาวกว่านี้เล็กน้อย
• สำหรับ Excel 2016 คุณสามารถใช้ฟังก์ชัน TEXTJOIN ที่เพิ่งเปิดตัวใหม่ได้
• วิธี VBA ใช้ได้กับ Excel . ทุกรุ่น

แยกตัวเลขจากสตริงใน Excel (สูตร Excel 2016)

สูตรนี้ใช้ได้เฉพาะใน Excel 2016 เนื่องจากใช้ฟังก์ชัน TEXTJOIN ที่เพิ่งเปิดตัวใหม่

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

โปรดทราบว่าสูตร TEXTJOIN ที่อธิบายไว้ในส่วนนี้จะรวมอักขระตัวเลขทั้งหมดไว้ด้วยกันตัวอย่างเช่น หากข้อความคือ "10 ตั๋วคือ $200" ผลลัพธ์จะเป็น 10200

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

สูตรด้านล่างจะให้ส่วนที่เป็นตัวเลขของสตริงใน Excel

=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))

นี่คือสูตรอาร์เรย์ ดังนั้นคุณต้องใช้ " ควบคุม + Shift + Enter " แทนที่จะใช้ Enter

สูตรนี้จะคืนค่าว่าง (สตริงว่าง) หากไม่มีตัวเลขในสตริงข้อความ

สูตรนี้ทำงานอย่างไร?

ให้ฉันแบ่งสูตรนี้และพยายามอธิบายว่ามันทำงานอย่างไร:

• ROW(INDIRECT(“1:”&LEN(A2))) – ส่วนนี้ของสูตรจะให้ชุดของตัวเลขที่เริ่มต้นจากหนึ่งฟังก์ชัน LEN ในสูตรจะคืนค่าจำนวนอักขระทั้งหมดในสตริง在“成本為 100 美元”的情況下，它將返回 19。因此，公式將變為 ROW(INDIRECT(“1:19”)。然後 ROW 函數將返回一系列數字 – {1;2;3 ;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}
• (MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1) - ส่วนนี้ของสูตรจะคืนค่าอาร์เรย์ของ #VALUEs!ข้อผิดพลาดตามสตริงหรือตัวเลขอักขระข้อความทั้งหมดในสตริงจะกลายเป็น #VALUE!ข้อผิดพลาดและค่าทั้งหมดยังคงเหมือนเดิมสิ่งนี้จะเกิดขึ้นเมื่อเราคูณฟังก์ชัน MID ด้วย 1
• IFERROR((MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1),””) – เมื่อใช้ฟังก์ชัน IFERROR #VALUE ทั้งหมดจะถูกลบออก!เกิดข้อผิดพลาด จะเหลือเพียงตัวเลขเท่านั้นผลลัพธ์ของส่วนนี้จะออกมาเป็นแบบนี้ - {"";"";"";"";"";";"";"";";"";"";";"";" ";"";"";1;0;0}
• =TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1,"")) - ฟังก์ชัน TEXTJOIN จะรวมอักขระสตริงเข้าด้วยกัน (เฉพาะตัวเลข) และละเว้นสตริงว่าง

เคล็ดลับสำหรับมือโปร:ถ้าคุณต้องการตรวจสอบผลลัพธ์ของส่วนหนึ่งของสูตร ให้เลือกเซลล์ กด F2 เพื่อเข้าสู่โหมดแก้ไข เลือกส่วนของสูตรที่คุณต้องการส่งออก แล้วกด F9คุณจะเห็นผลทันทีจากนั้นอย่าลืมกด Control + Z หรือกด Escapeอย่ากดเข้าไป

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

=TEXTJOIN("",TRUE,IF(ISERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),MID(A2,ROW(INDIRECT("1:"&LEN) (A2))),1),""))

การเปลี่ยนแปลงเล็กน้อยในสูตรนี้คือใช้ฟังก์ชัน IF เพื่อตรวจสอบอาร์เรย์ที่เราได้รับจากฟังก์ชัน MID เพื่อหาข้อผิดพลาดหากเป็นข้อผิดพลาด ให้เก็บค่าไว้ มิฉะนั้น ให้แทนที่ด้วยค่าว่าง

จากนั้นใช้ TEXTJOIN เพื่อรวมอักขระข้อความทั้งหมด

注意: แม้ว่าสูตรนี้จะใช้ได้ แต่จะใช้ฟังก์ชันระเหย (ฟังก์ชัน INDIRECT)ซึ่งหมายความว่าหากคุณใช้กับชุดข้อมูลขนาดใหญ่ อาจต้องใช้เวลาสักระยะในการให้ผลลัพธ์ทางที่ดีควรสำรองข้อมูลไว้ก่อนใช้สูตรนี้ใน Excel

แยกตัวเลขจากสตริงใน Excel (สำหรับ Excel 2013/2010/2007)

หากคุณมี Excel 2013. 2010 หรือ 2007 คุณจะไม่สามารถใช้สูตร TEXTJOIN ได้ ดังนั้นคุณต้องใช้สูตรที่ซับซ้อนในการทำเช่นนี้

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

สูตรด้านล่างจะทำสิ่งนี้:

=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7"," 8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2)))), 1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT ("$1:$"&LEN(A2)))/10),"")

สูตรนี้จะคืนค่าว่าง (สตริงว่าง) หากไม่มีตัวเลขในสตริงข้อความ

แม้ว่านี่จะเป็นสูตรอาร์เรย์ แต่คุณไม่ต้องการใช้ "Control-Shift-Enter" เพื่อใช้งานอินพุตแบบธรรมดาใช้ได้กับสูตรนี้

เครดิตสำหรับสูตรนี้ไปสู่ความอัศจรรย์นายเอกเซล ฟอรั่ม.

อีกครั้ง สูตรนี้จะแยกตัวเลขทั้งหมดในสตริงโดยไม่คำนึงถึงตำแหน่งตัวอย่างเช่น หากข้อความคือ "10 ตั๋วคือ $200" ผลลัพธ์จะเป็น 10200

注意: แม้ว่าสูตรนี้จะใช้ได้ แต่จะใช้ฟังก์ชันระเหย (ฟังก์ชัน INDIRECT)ซึ่งหมายความว่าหากคุณใช้กับชุดข้อมูลขนาดใหญ่ อาจต้องใช้เวลาสักระยะในการให้ผลลัพธ์ทางที่ดีควรสำรองข้อมูลไว้ก่อนใช้สูตรนี้ใน Excel

แยกข้อความและตัวเลขใน Excel ด้วย VBA

ถ้าคุณต้องการแยกข้อความและตัวเลขบ่อยๆ (หรือแยกตัวเลขออกจากข้อความ) คุณสามารถใช้วิธี VBA ได้เช่นกัน

สิ่งที่คุณต้องทำคือสร้างฟังก์ชันที่กำหนดเองโดยผู้ใช้ (UDF) ใน Excel โดยใช้โค้ด VBA แบบง่าย จากนั้นใช้สูตร VBA นั้นแทนสูตรที่ยาวและซับซ้อน

ให้ฉันแสดงวิธีสร้างสองสูตรใน VBA หนึ่งสูตรเพื่อแยกตัวเลขและอีกสูตรหนึ่งเพื่อแยกข้อความออกจากสตริง

แยกตัวเลขจากสตริงใน Excel (โดยใช้ VBA)

ในส่วนนี้ ฉันจะแสดงวิธีสร้างฟังก์ชันแบบกำหนดเองเพื่อรับเฉพาะส่วนที่เป็นตัวเลขจากสตริง

นี่คือรหัส VBA ที่เราจะใช้เพื่อสร้างฟังก์ชันที่กำหนดเองนี้:

ฟังก์ชัน GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1)) จากนั้น Result = Result & Mid (CellRef, i, 1) Next i GetNumeric = ฟังก์ชันสิ้นสุดผลลัพธ์

ต่อไปนี้เป็นขั้นตอนในการสร้างฟังก์ชันนี้และใช้ในเวิร์กชีต:

ตอนนี้คุณสามารถใช้ฟังก์ชัน GetText บนเวิร์กชีตได้เนื่องจากเราได้ยกระดับโค้ดทั้งหมดแล้ว สิ่งที่คุณต้องทำคือใช้สูตร =GetNumeric(A2) 

นี่จะให้ส่วนที่เป็นตัวเลขของสตริงทันที

โปรดทราบว่าเนื่องจากสมุดงานตอนนี้มีโค้ด VBA คุณจะต้องบันทึกด้วยนามสกุล .xls หรือ .xlsm

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

แยกข้อความจากสตริงใน Excel (โดยใช้ VBA)

ในส่วนนี้ ฉันจะแสดงวิธีสร้างฟังก์ชันแบบกำหนดเองเพื่อรับเฉพาะส่วนข้อความจากสตริง

นี่คือรหัส VBA ที่เราจะใช้เพื่อสร้างฟังก์ชันที่กำหนดเองนี้:

ฟังก์ชัน GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength ถ้าไม่ (IsNumeric(Mid(CellRef, i, 1))) แล้ว Result = Result & Mid(CellRef, i, 1 ) ถัดไป i GetText = ฟังก์ชันสิ้นสุดผลลัพธ์

ต่อไปนี้เป็นขั้นตอนในการสร้างฟังก์ชันนี้และใช้ในเวิร์กชีต:

ตอนนี้ คุณจะสามารถใช้ฟังก์ชัน GetNumeric ในเวิร์กชีตได้เนื่องจากเราได้ทำทุกอย่างในโค้ดแล้ว สิ่งที่คุณต้องทำคือใช้สูตร =GetText(A2)

นี่จะให้ส่วนที่เป็นตัวเลขของสตริงทันที

โปรดทราบว่าเนื่องจากสมุดงานตอนนี้มีโค้ด VBA คุณจะต้องบันทึกด้วยนามสกุล .xls หรือ .xlsm

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