Text Functions

Text Functions คือกลุ่มฟังก์ชั่นที่ใช้ในการจัดการข้อความ เช่นการตัดคำ การนับ การเปลี่ยนแทนค่าเดิมเป็นค่าใหม่ การนับจำนวนอักขระ การแสดงเป็นอักษรตัวใหญ่ แสดงเป็นอักษรตัวเล็ก การ Clean หรือลบอักขระที่เกินจำเป็นเช่นค่าที่ไม่สามารถแสดงทาง เครื่องพิมพ์ทิ้งไป เป็นต้น ซึ่งเป็นประโยชน์อย่างมากกับการจัดการข้อมูลให้เป็นไปตามรูปแบบ ตามค่าที่ต้องการ

Video ด้านล่างนี้อธิบายความหมายและสาธิตการใช้งานฟังก์ชั่นในหมวด Text ซึ่งเป็นฟังก์ชั่นที่ใช้บ่อยครั้งในการทำงานกับ Excel จึงควรศึกษาให้เข้าใจ ใช้ให้เป็น ประโยชน์ที่สำคัญยิ่งอีกประการคือ ฟังก์ชั่นเหล่านี้จะเป็นพื้นฐานในการประยุกต์ใช้งานที่ซับซ้อนในลำดับสูง ๆ ขึ้นไป

Video แสดงการใช้งาน Text Functions

สอบถามปัญหาการใช้งาน Excel และ VBA ได้ที่ snasui.com

การตัดเอาตัวอักษรตัวแรกของข้อความมาแสดง

กรณีมีข้อความอยู่ในเซลล์ใด ๆ แล้วต้องการตัดตัวอักษรโดยมีเงื่อนไขตามด้านล่างคือ

1. ถ้าตัวอักษรแรกเป็น พยัญชนะ ให้ตัดตัวอักษรที่เป็นพยัญชนะตัวแรกนับจากซ้ายมือออกมา
2. ถ้าตัวอักษรแรกเป็น สระ ให้ตัดตัวอักษรที่ถัดจากสระซึ่งเป็นพยัญชนะตัวแรกนับจากซ้ายมือออกมา

ยกตัวอย่างเช่น

ถ้า A1 คือ กาญจนา ผลลัพท์ที่ต้องการ คือ ก
ถ้า A1 คือ เอกวัฒน์ ผลลัพท์ที่ต้องการ คือ อ
ถ้า A1 คือ รุ้งทอง ผลลัพท์ที่ต้องการ คือ ร

เราสามารถเขียนสูตรที่ B1 เพื่อหาอักขระตัวแรกของค่าใน A1 ได้ตามด้านล่างครับ

=MID(A1,MIN(SEARCH(CHAR(ROW($161:$206)),A1&CHAR(ROW($161:$206)))),1)

Ctrl+Shift+Enter

Excel Forum • แสดงกระทู้ - การตัดตัวอักษร แบบมีเงื่อนไข

การรวมข้อมูลโดยอ้างอิงค่าที่เว้นเป็นช่วง ๆ

ปกติรายงานที่ได้มาจากสรุปข้อมูลแล้วมีการเว้นช่วงเอาไว้เช่นรายงานที่ได้จาก PivotTable แล้วมีการ Copy ค่าตามรูปแบบเดิม ๆ มาใช้ จากภาพด้านล่างจะเห็นว่าคอลัมน์ B มีการเว้นข้อมูลไว้เป็นช่วง ๆ การจะให้ง่ายต่อการใช้งานต้องเติม Field ด้านล่างให้เหมือนด้านบน แล้วใช้สูตร Sumif เข้ามาช่วย
แต่หากต้องการจะใช้ความสามารถของสูตรต่าง ๆ เพื่อสรุปข้อมูลโดยไม่ต้องปรับแต่งฐานข้อมูลก็สามารถทำได้แต่สูตรค่อนข้างซับซ้อนและยากต่อการทำความเข้าใจ แต่ก็เป็นประโยชน์ในการนำเสนอแนวความคิด เพื่อนำไปต่อยอดในการใช้งานด้านอื่น ๆ ครับ

ภาพประกอบการรวมข้อมูลโดยอ้างอิงที่เว้นเป็นช่วง ๆ



การที่เราจะรวมข้อมูลในอลัมน์ D จึงต้องประยุกต์สูตรต่าง ๆ มาใช้ โดยคีย์สูตรที่ D5 ดังนี้

=IF(AND(LOOKUP(CHAR(255),$B$2:B5)=LOOKUP(CHAR(255),$B$2:B6),C6=""),SUM(OFFSET($C$1,MATCH(9.99999999999999E+307,C:C),0):OFFSET($C$1,MATCH(9.99999999999999E+307,$D$2:D4)+1,0)),IF(LOOKUP(CHAR(255),$B$2:B5)<>LOOKUP(CHAR(255),$B$2:B6),SUM(OFFSET($C$1,MATCH(CHAR(255),$B$2:B5),0):OFFSET($C$1,MATCH(CHAR(255),$B$2:B6)-1,0)),""))

โดยมีสูตรสำคัญ ๆ ดังนี้

1. LOOKUP(CHAR(255),$B$2:B5) เป็นการหาค่าอักขระสุดท้ายในช่วง B2:B5
2. OFFSET(…):OFFSET(…) เป็นการนำผลลัพธ์ของสูตร OFFSET มาเป็นช่วงข้อมูลใหม่
3. MATCH(CHAR(255),$B$2:B5),0) เป็นการหาตำแหน่งของอักขระสุดท้ายในช่วง B2:B5
4. MATCH(9.99999999999999E+307,C:C) เป็นการหาตำแหน่งสุดท้ายที่พบตัวเลขในช่วง C:C

การเชื่อมข้อความแล้วให้ขึ้นบรรทัดใหม่

การเชื่อมข้อความเข้าด้วยกันสามารถใช้เครื่องหมาย & ช่วยในการเชื่อมได้ แต่หากเชื่อมแล้วต้องการให้ข้อความที่เชื่อมกันนั้นขึ้นบรรทัดใหม่ สามารถใช้ Char(10) เข้ามาคั่นกลางระหว่างแต่ละข้อความอีกทีครับ เช่น ต้องการเชื่อมข้อความ A1, B1 และ C1 เข้าด้วยกัน และต้องการให้แต่ละข้อความขึ้นบรรทัดใหม่

สามารถทำได้ดังนี้

1. ที่เซลล์ใด ๆ คีย์
   =A1&Char(10)&B1&Char(10)&C1
2. จากนั้นจัดข้อความโดยคลิกขวาลงในเซลล์ตามข้อ 1 เลือก Format cells แล้วคลิก Tab Alignment ทำเครื่องหมายที่ Wraptext > OK

จากนั้นกำหนดความสูงและความกว้างเซลล์ตามต้องการ

แยกภาษาไทยออกจากอังกฤษหรืออังกฤษออกจากไทย

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

1. เซลล์ A1 มีคำว่า Bangkokกรุงเทพมหานคร ต้องการแยก Bangkok และกรุงเทพมหานครให้อยู่คนละเซลล์สามารถทำได้ดังนี้
1. เซลล์ B1 แยก Bangkok ออกมาแสดงโดยคีย์สูตร
   =LEFT(A1,MATCH("zzz",MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))
   Ctrl+Shift+Enter
2. เซลล์ C1 แยกกรุงเทพมหานครออกมาแสดงโดยคีย์สูตร
   =Substitute(A1,B1,"")
   Enter
2. เซลล์ A1 มีคำว่า กรุงเทพมหานครBangkok ต้องการแยก กรุงเทพมหานคร และ Bangkok ให้อยู่คนละเซลล์สามารถทำได้ดังนี้
1. เซลล์ B1 แยกกรุงเทพมหานครออกมาโดยคีย์สูตร
   =LEFT(A1,MIN(SEARCH(CHAR(ROW(INDIRECT("65:90"))),A1&"abcdefghijklmnopqrstuvwxyz")-1))
   Ctrl+Shift+Enter
2. เซลล์ C1 แยก Bangkok ออกมาแสดงโดยคีย์สูตร
   =Substitute(A1,B1,"")
   Enter

Note: สูตรที่ B1 ทั้งสองสูตรเป็นสูตรแบบอาร์เรย์ การคีย์ให้รับสูตรจะต้อง Ctrl+Shift+Enter ครับ