Excel: แยกกลุ่มตัวเลขออกจากตัวอักษร

การใช้สูตรในการแยกตัวเลขออกจากตัวอักษร

ปัญหา

ข้อมูลประกอบด้วยตัวอักษรและตัวเลขอีก ๔ ตัว ตัวอักษรจะบอกลักษณะการใช้ประโยชน์ ซึ่ง ถ้ามันเป็นตัวอักษรตัวเดียวหมดก็จะไม่มีปัญหา ใช้ฟังค์ชั่น “ข้อความเป็นคอลัมน์” (text to column) ได้ แต่ปัญหาคือมีข้อมูลบางตัวเป็นตัวอักษร ๒ ตัว ทำให้แยกด้วยวิธีนี้ไม่ได้

แนวการแก้ปัญหา

๑) หาทางกำหนดว่าอักขระนั้นเป็นตัวเลขหรือตัวอักษร

๒) แยกตัวเลขออกจากตัวอักษร

การใช้สูตร

สูตรที่ ๑) =LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&”0123456789"))-1)

ให้ A1 คือตำแหน่งข้อมูลที่ต้องการแยก

LEFT

ฟังค์ชั่นหลักที่ใช้คือ LEFT หรือการตัดอักขระจากทางซ้าย รูปแบบของฟังค์ชั่น LEFT คือ LEFT(text,[character_num]) โดย Text คือข้อความที่จะหา [character_num] คือจำนวนอักขระที่ต้องการ

อย่างเช่น =LEFT(A1,4) หมายถึงต้องการตัวอักขระ ๔ ตัวแรกจากทางซ้ายในช่อง A1 เช่นในช่อง A1 มีคำว่า anthracite18coal สูตรนี้จะให้ผลลัพท์ตัวอักขระ ๔ ตัวแรกนับจากทางซ้ายมือคือ anth

จะใช้ฟังค์ชั่น LEFT ในการแยกตัวอักษรออกจากตัวเลข เพราะตัวอักษรจะอยู่ทางซ้ายมือเท่านั้น (สำหรับตัวอย่างนี้) แต่ปัญหาต่อไปคือ เราจะให้มันเริ่มที่อักขระที่เท่าไหร่ เพราะข้อมูลบางตัวมี ๑ อักขระ บางตัวมี ๒ อักขระ

จะใช้วิธีการหาตำแหน่งแรกของตัวเลข โดยใช้ฟังค์ชั่น FIND ในการหาตำแหน่งของตัวเลข

FIND

ฟังค์ชั่น FIND จะอยู่ในรูปแบบ FIND(find_text, within_text, [start_num]) find_text คือ -ข้อความที่ต้องการหา ส่วน within_text คือให้หาข้อความนั้นจากไหน และ Start_num คือตำแหน่งเริ่มต้น ตำแหน่งที่เท่าไหร่ ถ้าไม่ใส่ไว้จะมีค่าเท่ากับ ๑ หรือตำแหน่งแรก และเมื่อหาเจอแล้วจะส่งลำดับของตำแหน่งที่พบตัวแรก

ยกตัวอย่างเช่น =FIND(“c”,A1) เราจะหาตัว c จากข้อความใน A1 คือข้อความ anthracite18coal จะได้ผลลัพท์เป็น ๗ เพราะตัว c ตัวแรกที่เจออยู่ในตำแหน่งที่ ๗

ฟังค์ชั่นนี้จะแสดงตำแหน่งแรกที่เจอ อย่างข้อความในตัวอย่างมีตัว c ๒ ตัว จะบอกแต่ตำแหน่งของตัวแรกที่เจอเท่านั้น แต่ถ้าเปลี่ยนสูตรเป็น =FIND(“c”,A1,8) ผลลัพท์จะกลายเป็น ๑๓ เพราะจะไปค้นหาเริ่มจากอักขระตัวที่ ๘ เป็นต้นไป (ข้าม c ตัวแรกเพราะตัวแรกอยู่ในลำดับที่ ๗)

สำหรับการหาตัวเลข เราจะกำหนดตัวเลข ๐ — ๙ และกำหนดว่าให้หาเฉพาะใน ๐ — ๙ เท่านั้นคือ =FIND({0,1,2,3,4,5,6,7,8,9},A1&”0123456789") ปัญหาของสูตรนี้คือจะส่งกลับค่าน้อยที่สุดก่อน คือค่า ๐ แต่ตำแหน่งของตัวเลขตัวแรกนั้นไม่ใช่ ๐ จึงต้องนำฟังค์ชั่น MIN หรือค่าต่ำสุดมาสวมอีกครั้ง

*ในที่นี้ ฟังค์ชั่น MIN จะแสดงตำแหน่งลำดับที่น้อยที่สุด ไม่ใช่ค่าตัวเลขที่น้อยที่สุด เพราะ FIND ส่งกลับลำดับ โดยแสดงลำดับที่มีค่าน้อยที่สุดก่อน (งงมั้ย?)

เช่นค่า ๙๒๓๔๕๖๗๘๑๐ ถ้าเราหาค่าโดยใช้สูตร FIND ข้างต้น ผลลัพท์จะเป็น ๑๐ (คือตัว ๐ อยู่ลำดับที่ ๑๐ การคำนวณคือ ๙ = ๑, ๒ = ๒… ๐ = ๑๐ สูตร FIND จะหาตำแหน่งของตัวเลขน้อยที่สุดคือ ๐ มาแสดง ผลลัพท์จึงเป็น ๑๐ (และถ้าไม่มีเลข ๐ มันจะแสดงเหมือนมีเลข ๐ อยู่หลังสุดเสมอ เช่นค่า ๙๒๓๔๕๖๗๘๑ ก็จะได้ผลลัพท์เป็น ๑๐ เช่นกัน)

แต่ถ้า MIN ไปครอบไว้ ค่า MIN จะส่งกลับผลการจัดลำดับของ FIND ที่ต่ำสุดคือเลข ๙ อยู่ในตำแหน่งที่ ๑

ดังนั้นเราจะได้ =MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&”0123456789")) ผลลัพท์จะแสดงตำแหน่งตัวเลขตัวแรก ไปใส่ในในสูตรของ LEFT

แต่อย่าลืมว่า ตำแหน่งที่ได้มา เป็นตำแหน่งแรกของตัวเลข แต่เราไม่ต้องการตัวเลขเราจึงต้อง -๑ คือร่นระยะไปอีก ๑ ตำแหน่ง

เช่น AB1205 สูตร =MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&”0123456789")) จะได้ ๓ ถ้าเป็นสูตร LEFT (A1, 3) มันจะได้ผลลัพท์ AB1 แต่เราต้องการเฉพาะตัวอักษรดังนั้นเราจึงต้อง -๑ ให้รู้ว่าเอาตัวก่อนหน้าตัวเลขตัวแรกเท่านั้น

สูตรที่ ๒) =RIGHT(A1,LEN(A1)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&”0123456789"))+1)

ต่อมาแยกตัวเลขออกจากกลุ่ม ใช้หลักการเดียวกัน คือหาตัวเลขตัวแรก แล้วใช้ฟังค์ชั่น RIGHT (เพราะตัวเลขอยู่ทางขวามือ)

โดยจะใช้ฟังค์ชั่น LEN นับจำนวนอักขระทั้งหมดของข้อมูล เช่น AB1205 จะได้ผลลัพธ์เป็น ๖ แล้วลบออกด้วย ตำแหน่งแรกของตัวเลข (ตรงนี้ต้องบวกเพิ่มไปอีก ๑ อักขระ) เช่น AB1205 ถ้าหาตำแหน่งแรกจะเป็น ๓ เอา ๖ — ๓ จะได้ ๓ ถ้าใช้ Right (A1,3) ผลลัพธ์จะเป็น ๒๐๕ ดังนั้นเราต้อง +๑ เข้าไป เพื่อให้เป็นตำแหน่งสุดท้ายของตัวอักษร จะได้ตัดเอาตัวเลขมาทั้งหมด