วิธีการแยกตัวเลขจากสตริงข้อความใน Excel

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

ดึงตัวเลขจากตำแหน่งใดก็ได้จากสตริงข้อความ

• ด้วยสูตร
• ด้วย Kutools for Excel
• ด้วยรหัส VBA

แยกตัวเลขทศนิยมจากสตริงข้อความเท่านั้น

แยกตัวเลขจากท้ายสตริงข้อความ

แยกตัวเลขจากจุดเริ่มต้นของสตริงข้อความ

ดึงตัวเลขจากตำแหน่งใดก็ได้จากสตริงข้อความ

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

♦ แยกตัวเลขจากตำแหน่งใดก็ได้จากสตริงข้อความด้วยสูตร

สูตรต่อไปนี้สามารถช่วยคุณแยกตัวเลขจากสตริงข้อความได้ โปรดทำดังนี้:

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

● เวอร์ชัน Excel ทั้งหมด:

=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),"")

● Excel 365 หรือ Excel 2021 และเวอร์ชันใหม่กว่า:

=TEXTJOIN("", TRUE, IFERROR(MID(A2, SEQUENCE(LEN(A2)), 1) *1, ""))

♦ แยกตัวเลขจากตำแหน่งใดก็ได้จากสตริงข้อความด้วยสูตร

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

1. คลิก Kutools > ข้อความ > แยกข้อความดูภาพหน้าจอ:
   
2. ตัว Vortex Indicator ได้ถูกนำเสนอลงในนิตยสาร แยกข้อความ ไดอะล็อกบ็อกซ์ให้ดำเนินการดังต่อไปนี้:
   (1.) เลือกรายการข้อมูลที่คุณต้องการแยกตัวเลข
   (2. ) เลือก แยกหมายเลข ตัวเลือก;
   (3. ) ตรวจสอบ แทรกเป็นสูตร ตัวเลือกตามที่คุณต้องการ
   (4.) สุดท้าย คลิก OK ปุ่ม
   
3. ในกล่องพร้อมท์ต่อไปนี้ ให้เลือกเซลล์เพื่อแสดงผลลัพธ์ที่แยกออกมา ดูภาพหน้าจอ:
   
4. คลิก OKตัวเลขทั้งหมดจากแต่ละเซลล์จะถูกแยกออกมาในคราวเดียว ดูภาพหน้าจอ:
   

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

นี่คือรหัส VBA ที่สามารถช่วยคุณได้เช่นกันโปรดทำดังนี้:

1. ค้างไว้ Alt + F11 คีย์เพื่อเปิด Microsoft Visual Basic สำหรับแอปพลิเคชัน หน้าต่าง
2. คลิก สิ่งที่ใส่เข้าไป > โมดูลและวางรหัสต่อไปนี้ในไฟล์ โมดูล หน้าต่าง.
   รหัส VBA: แยกหมายเลขออกจากสตริงข้อความเท่านั้น:

   Sub ExtrNumbersFromRange()
       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 คีย์เพื่อเรียกใช้รหัสนี้และกล่องพร้อมต์จะปรากฏขึ้นเพื่อเตือนให้เลือกช่วงข้อความที่คุณต้องการใช้ดูภาพหน้าจอ:
   
4. จากนั้นคลิก OKกล่องพร้อมต์อื่นกำลังตามมาโปรดเลือกเซลล์เพื่อแสดงผลลัพธ์ดูภาพหน้าจอ:
   
5. ในที่สุดคลิก OK และตัวเลขทั้งหมดในเซลล์ที่เลือกจะถูกแยกออกพร้อมกัน

แยกตัวเลขทศนิยมจากสตริงข้อความเท่านั้น

หากสตริงข้อความซึ่งรวมถึงตัวเลขทศนิยมในแผ่นงานของคุณคุณจะแยกเฉพาะตัวเลขทศนิยมออกจากสตริงข้อความได้อย่างไร

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

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

● เวอร์ชัน Excel ทั้งหมด:

=LOOKUP(9.9E+307,--LEFT(MID(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0}, $A2&"1023456789")),999),ROW(INDIRECT("1:999"))))

● เอ็กเซล 365:

=REGEXEXTRACT(A2,"[\d.]+")

แยกตัวเลขจากท้ายสตริงข้อความ

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

คัดลอกและวางสูตรด้านล่างนี้ลงในเซลล์ว่าง กด Ctrl + Shift + Enter เพื่อให้ได้ผลลัพธ์แรก:

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

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

แยกตัวเลขจากจุดเริ่มต้นของสตริงข้อความ

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

คัดลอกและวางสูตรด้านล่างนี้ลงในเซลล์ว่าง กด Ctrl + Shift + Enter เพื่อให้ได้ผลลัพธ์แรก:

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

จากนั้นลากจุดจับเติมลงมาเพื่อเติมสูตรนี้ลงในเซลล์อื่น โดยจะแยกเฉพาะตัวเลขเริ่มต้นเท่านั้น ดูภาพหน้าจอ:

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

บทความญาติ:

• แยกข้อความที่เป็นตัวหนาใน Excel เท่านั้น
• แยกข้อความใน Excel เท่านั้น