การดึงตัวเลขออกจากตัวอักษร

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

สมมุติ A1:A3 มีค่าตามลำดับดังนี้

• A1: ABC123XYZ
• A2: 456ABC
• A3: XYZ789

หากจะดึง 123, 456, 789 ออกมาจากอักขระด้านบนตามลำดับ สามารถใช้สูตรที่ B1 ได้ดังนี้

=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},
A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))

Enter แล้ว Copy ลงด้านล่าง

ภาพการดึงตัวเลขออกจากตัวอักษรกรณีตัวเลขติดกันเป็นกลุ่ม

และหากว่าตัวเลขไม่อยู่ติดกันเป็นกลุ่ม เช่น A1:A3 มีข้อความตามด้านล่าง

• A1: 1หนึ่ง23สองสาม4สี่5ห้า
• A2: 123หนึ่งสองสาม456สี่ห้าหก78
• A3: 102หนึ่งศูนย์สอง0304ศูนย์สามศูนย์สี่7890เจ็ดแปดเก้าศูนย์

หากจะดึงเอาเฉพาะตัวเลขออกมาใช้งานสูตรจะมีความซับซ้อนขึ้นมาก โดยสามารถเขียนสูตรที่เซลล์ B1 ได้เป็นด้านล่าง

=SUM(MID(A1,SMALL(IF(ISNUMBER(--MID($A1,ROW(INDIRECT("1:"&LEN(A1))),1)),
ROW(INDIRECT("1:"&LEN(A1)))),ROW(INDIRECT("1:"&COUNT(--MID($A1,
ROW(INDIRECT("1:"&LEN(A1))),1))))),1)*10^(COUNT(ROW(INDIRECT("1:"&
COUNT(--MID($A1,ROW(INDIRECT("1:"&LEN(A1))),1)))))+1-ROW(INDIRECT(
"1:"&COUNT(--MID($A1,ROW(INDIRECT("1:"&LEN(A1))),1)))))/10)

การกดแป้นให้รับสูตรจะต้องกด 3 แป้นคือ Ctrl+Shift+Enter เนื่องจากเป็นสูตร Array จากนั้น Copy สูตรลงด้านล่าง

ภาพการดึงตัวเลขออกจากตัวอักษรกรณีตัวเลขไม่ติดกันเป็นกลุ่ม

สามารถดาวน์โหลดไฟล์แนบด้านล่างไปศึกษาได้ตามสะดวกครับ 

Revised: June 17, 2018 at 07:11