“Excel มีฟังก์ชันที่ตัดเฉพาะตัวเลขออกจากข้อความไหมครับ?” ลูกศิษย์ถาม

“ไม่มีฟังก์ชันโดยตรงครับ ต้องเขียนสูตรขึ้นมาเอง ปัญหาคือสูตรยาวและซับซ้อนมาก”

คือคำตอบของผมก่อนหน้านี้

แต่ตอนนี้คำตอบเปลี่ยนไปแล้ว เพราะ Excel มีฟังก์ชันใหม่ชื่อว่า TEXTJOIN

TEXTJOIN คือฟังก์ชันใหม่ที่เริ่มใช้ใน Excel 2016

(Excel 2003, 2007, 2010, 2013 หมดสิทธิ์ T_T)

ใช้เชื่อมตัวหนังสือหรือข้อความเข้าด้วยกัน ความเจ๋งคือสามารถกำหนดอักขระที่ใช้คั่นระหว่างข้อความได้ด้วย

มาดูตัวอย่างการใช้งานกันครับ

โครงสร้างฟังก์ชันคือ

=TEXTJOIN( delimiter, ignore_empty, text1, [text2],…)

delimiter คือ อักขระที่ใช้คั่นระหว่างข้อความ (ถ้าต้องการให้มี) เช่น “,” (comma) “/” (slash) ” ” (เว้นช่องว่าง)

(ถ้าต้องการให้ข้อความติดกัน ไม่ต้องใส่ delimiter หรืออาจใส่เป็น “” ก็ได้)

ignore_empty ใส่ได้ 2 แบบ คือ TRUE, FALSE

TRUE แปลว่า ถ้าข้อความที่เลือกมีค่าว่าง ให้ข้ามค่าว่างนั้นไปเลย

FALSE แปลว่า ถ้าข้อความที่เลือกมีค่าว่าง ให้แสดงค่าว่างนั้นเป็นช่องว่าง

text1, [text2],… คือ เซลล์ที่ต้องการเชื่อมเข้าด้วยกัน

ความเจ๋งของ text1, [text2] คือ สามารถใส่เป็นช่วงเซลล์ได้

เช่น A1:D1

แปลว่า ตอนเขียนสูตร เราลากช่วงข้อมูลได้โดยตรงเลย ไม่ต้องเลือกทีละเซลล์แล้วคั่นด้วยเครื่องหมาย , (comma)

ซึ่งความสามารถนี้ ฟังก์ชัน CONCATENATE ทำไม่ได้ !

(ฟังก์ชัน CONCATENATE ต้องคั่นด้วย , (comma) หรือต้องระบุเป็น A1, B1, C1, D1 ก่อความรำคาญจิตอย่างมาก T_T )

.

คราวนี้ มาดูการประยุกต์ใช้ฟังก์ชัน TEXTJOIN เพื่อตัดตัวเลขออกจากข้อความกัน

สมมติในเซลล์ A2 มีข้อความว่า

There are 25 girls

ต้องการตัดเฉพาะตัวเลข หรือต้องการผลลัพธ์เท่ากับ 25

(ตัดตัวหนังสือทิ้งไปให้หมด)

ฟังก์ชัน TEXTJOIN อาจใช้ตรงๆเพื่อหาคำตอบแบบนี้ไม่ได้ (อ้าว!)

แต่ถ้าประยุกต์นิดหน่อย จะได้คำตอบที่ต้องการ แถมสูตรไม่ยาวด้วย

สูตรคือ

{=–TEXTJOIN(,TRUE,IFERROR(–MID(A2,ROW(INDIRECT(“1:100″)),1),””))}

(ต้องกด Ctrl+Shft+Enter ด้วยเพราะเป็นสูตรอาร์เรย์)

(ถ้าไม่ใช้ TEXTJOIN สูตรยาวกว่านี้อีกนะ ^^)

มาดูผลลัพธ์กันครับ

แอ่น แอน แอ๊น….

สุดยอดเลยใช่ไหมล่ะครับ ^__^

พรึม พรึม พรึม !!! (เสียงจุดพลุ ^^)

มาอธิบายสูตรกันนิดนึง

จากโครงสร้างของฟังก์ชัน TEXTJOIN

=TEXTJOIN( delimiter, ignore_empty, text1, [text2],…)

เทียบกับสูตรประยุกต์เมื่อกี๊

{=–TEXTJOIN(, TRUE, IFERROR(–MID(A2,ROW(INDIRECT(“1:100″)),1),””))}

ลองเทียบกันแบบ ตัวต่อตัว ปอนด์ต่อปอนด์ หมัดต่อหมัด

delimiter สูตรนี้ปล่อยเว้นไป (ไม่ระบุ) เพราะไม่ต้องการให้มีอักขระใดมาคั่นตัวเลข (ตัวเลขต้องติดกันทั้งหมด)

ignore_empty คือ TRUE

text1 คือ IFERROR(–MID(A2,ROW(INDIRECT(“1:100″)),1),””)

(สูตรนี้ไม่มี text2, text3, ….   มีแค่ text1 อย่างเดียว)

คีย์หลักก็คือ IFERROR(–MID(A2,ROW(INDIRECT(“1:100″)),1),””) นั่นเอง

มาทำความเข้าใจกันนิดนึงครับ ^__^

INDIRECT(“1:100”) คือการแปลง “1:100” ให้เป็นการอ้างอิงบรรทัดที่ 1:100

ROW(INDIRECT(“1:100”)) เพื่อทำให้เป็น ROW(1:100)

หรือได้ผลลัพธ์เป็น

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59;60;61;62;63;64;65;66;67;68;69;70;71;72;73;74;75;76;77;78;79;80;81;82;83;84;85;86;87;88;89;90;91;92;93;94;95;96;97;98;99;100}

MID(A2,ROW(INDIRECT(“1:100”)),1) คือการตัดตัวอักษรในเซลล์ A2 ทีละ 1 ตัว ทั้งหมด 100 ครั้ง

หรือได้ผลลัพธ์เป็น

{“T”;”h”;”e”;”r”;”e”;” “;”a”;”r”;”e”;” “;”2″;”5″;” “;”g”;”i”;”r”;”l”;”s”;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””}

การใส่ — หน้า MID

–MID(A2,ROW(INDIRECT(“1:100”)),1)

เพื่อแปลง Text ให้เป็นตัวเลข

(“2” และ “5” คือ Text ไม่ใช่ตัวเลข)

(–“2” ได้ผลลัพธ์เท่ากับ 2 )

การใส่ IFERROR ครอบ –MID

IFERROR(–MID(A2,ROW(INDIRECT(“1:100″)),1),””)

เพื่อทำให้ตัวเลขยังคงเป็นตัวเลข และเปลี่ยนตัวหนังสือกับ “” ให้เป็น “”

(–“2” ได้ผลลัพธ์เท่ากับ 2 เมื่อครอบด้วย IFERROR จึงได้ 2 เช่นเดิม)

(–“T” ได้ผลลัพธ์เป็น #VALUE! จึงใส่ IFERROR เพื่อเปลี่ยน #VALUE! ให้เป็น “”)

(–“” ได้ผลลัพธ์เป็น #VALUE! จึงใส่ IFERROR เพื่อเปลี่ยน #VALUE! ให้เป็น “”)

ผลลัพธ์ของ IFERROR(–MID(A2,ROW(INDIRECT(“1:100″)),1),””) คือ

{“”;””;””;””;””;””;””;””;””;””;2;5;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””;””}

เมื่อครอบด้วยฟังก์ชัน TEXTJOIN

TEXTJOIN(,TRUE,IFERROR(–MID(A2,ROW(INDIRECT(“1:100″)),1),””))

ผลลัพธ์ที่ได้คือ “25”

แต่ “25” เป็น Text จึงใส่ — หน้า TEXTJOIN เพื่อแปลง Text ให้เป็นตัวเลข

(–“25” = 25 )

สูตรนี้ถือเป็นสูตรอาร์เรย์ (เพราะ ROW, MID, IFERROR ต้องทำทั้งหมด 100 ครั้ง) จึงต้องกด Ctrl+Shft+Enter

{=–TEXTJOIN(,TRUE,IFERROR(–MID(A2,ROW(INDIRECT(“1:100″)),1),””))}

(วงเล็บปีกกา {  } ไม่ได้เกิดจากการพิมพ์ แต่เกิดจากการกด Ctrl+Shft+Enter)

ผลลัพธ์ทีได้ก็คือ 25 นั่นเอง

ผ่าม ผ่าม ผ่าม !

ตู้ม ตู้ม ตู้ม !

ปะรัม ปะรัม ปะรัม !

^^

.

เป็นยังไงกันบ้างเอ่ย

สูตรนี้อาจซับซ้อน ซ้อนกันหลายฟังก์ชัน แถมฟีเจอร์ริ่งกันหลายเทคนิค

มองเผินๆเหมือนจะยาวนิดนึง

แต่ถ้ามองในแง่การเขียนสูตรเพื่อตัดตัวเลขออกจากข้อความ การใช้ TEXTJOIN ทำให้สูตรสั้นลงเยอะมาก (จริงๆนะ ^^)

เทคนิคการประยุกต์ใช้ฟังก์ชัน TEXTJOIN แบบนี้ ผมไม่ได้คิดเอง แต่ได้ไอเดียมาจากบทความนี้ครับ

https://exceljet.net/formula/strip-non-numeric-characters

ผู้เขียนคือคุณ Dave Bruns เป็นคนที่เก่งระดับสุดยอด แถมใจดีอีกต่างหาก

(เว็บ https://exceljet.net ดีมากๆครับ ใครชอบสูตรประยุกต์ แนะนำเว็บนี้อย่างแรง ^__^)

ผมอ่านแล้วชอบมากๆๆ ชอบจนอยากเขียนมา “เล่า” ให้ฟังเป็นภาษาไทย เรียบเรียงตามสไตล์ของผม

รายละเอียดอื่นๆของฟังก์ชัน TEXTJOIN อ้างอิงจาก 2 บทความนี่้ครับ

https://exceljet.net/excel-functions/excel-textjoin-function

http://www.excelfunctions.net/excel-textjoin-function.html

เริ่มรักฟังก์ชัน TEXTJOIN กันแล้วใช่ไหมล่ะ ^__^

(ฟังก์ชัน TEXTJOIN ใช้ได้เฉพาะ Excel 2016 เวอร์ชันก่อนหน้านั้นใช้ไม่ได้)

 

ฟังก์ชัน TEXTJOIN ยังประยุกต์ใช้ได้อีกหลายแบบนะครับ

บางแบบสุดยอดมากๆ เรียกว่า “เข็มขัดสั้น” กันเลยทีเดียว (คาดไม่ถึง ^^)

แต่บทความวันนี้ยาว(มาก) แล้ว ขอยกยอดเล่าให้ฟังในบทความอื่นนะครับ ^__^

 

ใครสนใจไฟล์ตัวอย่างของบทความนี้ ดาวน์โหลดที่ลิงค์ด้านล่างได้เลยครับ

TEXTJOIN_StripNumberOnly_171116

 

หากคุณชอบบทความแนวนี้ สามารถอัพเดตบทความใหม่ๆโดยคลิก Like เฟสบุ๊คแฟนเพจ วิศวกรรีพอร์ต หรือคลิก ที่นี่

อย่าลืมแชร์ให้เพื่อนอ่านเพื่อเป็นกำลังใจให้คนเขียนด้วยนะครับ ^__^