วิธีพิสดาร ใช้แยกตัวเลขออกจากข้อความ

คุณ TeePisit ตั้งคำถามนี้ไว้ในไลน์กลุ่ม Excel Expert Group

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

สมมติว่ามีข้อความ abc defg hi 123 jklm ในเซลล์ B2

คลิกที่นี่เพื่อ Download แฟ้มตัวอย่าง 

ขั้นแรกต้องใช้สูตร Find หาว่าวรรคแต่ละตัวอยู่ที่ตำแหน่งที่ 4, 9, 12, 16, กับ 21 โดยใช้สูตรตามภาพ

ที่แปลกหน่อยซึ่งเป็นวิธีการของผมเองที่ทำให้ได้เลข 21 เกินออกมาด้วยซึ่งอยู่หลังตัว m เป็นเพราะในสูตร Find นั้นผมจะใช้เซลล์ B2&" " ซึ่งเพิ่มวรรคต่อท้ายเข้าไปด้วย เพื่อช่วยให้ขั้นต่อไปเมื่อใช้สูตร Mid แยกคำ jklm ส่วนสุดท้ายหลังเครื่องหมายวรรคตัวสุดท้ายได้ทันที โดยไม่ต้องเปลี่ยนไปใช้ Right

abc แยกออกมาได้จากสูตร D8 : =LEFT( $B$2, D2-1 )
D2-1 = 4-1 = 3 เท่ากับจำนวนตัวอักษร abc ที่มีอยู่ 3 ตัว โดยใช้เลข 4 ที่เป็นตำแหน่งของวรรคแรกมาใช้หาจำนวนตัวของส่วนแรก

แต่ละส่วนก็แยกออกมาได้ด้วยสูตร Mid แบบเดียวกันกับการแยกเลข 123 ตามนี้

เลข 123 แยกออกมาได้จากสูตร D11 : =MID( $B$2, D4+1, D5-D4-1 )
D4+1 = 12+1 = 13 ซึ่งเป็นตำแหน่งเริ่มต้นของเลข 1 ซึ่งอยู่ตัวที่ 13 โดยใช้เลข 12 ที่หามาจาก Find ข้างต้น
D5-D4-1 = 16-12-1 =3 ซึ่งเป็นจำนวนตัวของเลข 123 ที่มีอยู่ 3 ตัว ต้องพึ่งเลข 16 ที่มาจาก Find ข้างต้น พอเอาตำแหน่งของเครื่องหมายวรรคมาลบกันแล้วลบ 1 ก็จะได้ระยะห่างของเครื่องหมายวรรคซึ่งเป็นจำนวน 3 ตัวซึ่งเป็นจำนวนตัวของคำที่อยู่ระหว่างเครื่องหมายวรรคทั้งสองตัวนั่นเอง

พอได้เลข 123 แล้วพบว่าชิดซ้าย ซึ่งแสดงว่ามีสถานะเป็น Text จึงต้องใส่ -- หรือใช้สูตร Value ปรับให้มีสถานะเป็น Number ชิดขวา ตามที่เห็นในเซลล์ D14-D15

กว่าจะได้เลข 123 ออกมา ต้องพึ่งหลายขั้นตอนและใช้หลายเซลล์ หากต้องการใช้สูตรลัดสูตรเดียวเซลล์เดียว ให้ใช้สูตรในเซลล์ D17 ตามนี้ครับ

D17 : =--MID($B$2,FIND(" ",$B$2&" ",FIND(" ",$B$2&" ",FIND(" ",$B$2&" ")+1)+1)+1,FIND(" ",$B$2&" ",FIND(" ",$B$2&" ",FIND(" ",$B$2&" ",FIND(" ",$B$2&" ")+1)+1)+1)-FIND(" ",$B$2&" ",FIND(" ",$B$2&" ",FIND(" ",$B$2&" ")+1)+1)-1)

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

แต่ถ้าอยากได้สูตรลัด C2 : =VALUE( LEFT( RIGHT( SUBSTITUTE( B2, " ", REPT( " ", 100 ) ), 200), 100 ) )
คุณเมษกับคุณวิทยาช่วยหาสูตรมาให้ ผมได้ให้คำแนะนำเพิ่มตามภาพนี้ (คลิกที่ภาพเพื่อขยาย)

สูตรลัด  C2 : =VALUE( LEFT( RIGHT( SUBSTITUTE( B2, " ", REPT( " ", 100 ) ), 200), 100 ) )

สูตรนี้มีวิธีคิดมาได้ยังไง ผมได้พยายามอธิบายไว้ตามภาพครับ

ใช้วิธีที่แปลกมาก แทนที่จะหาตำแหน่งของวรรค กลับทำให้วรรคกว้างขึ้นก่อนโดยใช้สูตร Substitue แทนที่วรรคเดียวด้วยวรรคที่ยาว 100 ตัว ซึ่งสร้างมาจากสูตร Rept ไว้ก่อน (Rept ย่อมาจาก Repeat แปลว่าซ้ำ อยากได้วรรคซ้ำๆๆๆๆๆๆๆๆๆๆๆ 100 ตัว)

พอวรรคกว้างขึ้นแล้วจึงใช้สูตร Right ตัดข้อความจากด้านขวาออกมาเพื่อให้ติดส่วนที่เป็นตัวเลขด้านซ้ายมาด้วย จากนั้นจึงใช้สูตร Left ตัดตัวเลขจากด้านขวาออกมา ไม่ติดตัวอักษรมาด้วย

จากนั้นจึงใช้สูตร Value แปลง วรรค วรรค วรรค วรรค วรรค วรรค วรรค วรรค วรรค ๆๆๆๆๆ 123 ออกมาให้เป็นเลข 123 อย่างเดียวเท่านั้น โดยทำลายวรรคหายไป

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

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

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

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

เชิญมาร่วมไลน์กลุ่ม Excel Expert Group กันครับ

หรือคลิกที่นี่เพื่อเข้าร่วมไลน์กลุ่ม