Excel ตัดข้อความ ขึ้นบรรทัดใหม่

สำหรับช่อง Replace with จะไม่ใส่อะไรเลย หรือจะเคาะ spacebar สักหนึ่งครั้งก็ได้นะครับ เวลาข้อมูลมารวมกันจะได้มีเว้นวรรคหนึ่งครั้ง


2. Wrap Text

เลือก column, cell ที่ต้องการจะเคลียร์การขึ้นบรรทัดใหม่ใน cell ถ้าต้องการเคลียร์ทั้ง Worksheet ก็เลือกทั้ง Worksheet เลยก็ได้

ไปที่ Tab Home แล้วก็คลิกที่คำสั่ง Wrap Text ใน กลุ่มของ Alignment

Excel ตัดข้อความ ขึ้นบรรทัดใหม่
1.ก่อนอื่นคลิ้กที่เซลที่ต้องการตัดข้อความที่ยาวทะลุเซล
2.จากนั้นคลิ้กที่เมนู Home
3.ในหัวข้อ Editing คลิ้กที่ปุ่ม Fill
4.จากนั้นคลิ้กเลือกที่ Justify

Excel ตัดข้อความ ขึ้นบรรทัดใหม่
1.จากนั้นจะมีหน้าต่างแจ้งเตือนให้กดปุ่ม OK
2.เพียงเท่านี้ข้อความก็จะถูกตัดให้ไปอยู่คนละแถว โดยเซลไม่ขยายเหมือน Wordwrap แล้วครับ

—– ถ้าต้องการความกว้างของการตัดคำให้กว้างหรือแคบก็ปรับขนาดคอลัมน์ตามต้องการ แล้วเมื่อใช้คำสั่ง Fill Justify ก็จะตัดคำให้มีความยาวเท่ากับความกว้างของคอลัมน์ครับ สำหรับทิป-เทคนิคนี้เหมาะสำหรับการใส่ข้อความในเซลที่ไม่ต้องการขยายความกว้างของเซลในแถวนั้น ซึ่งมักจะเจอยู่บ่อยๆ ดังนั้นศึกษาทิป-เทคนิคนี้ก็ไม่เสียหายครับ รับรองได้ใช้งานแน่นอน —–

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

หากคุณใช้โปรแกรม Excel แล้วจะพิมพ์ข้อความให้ขึ้นบรรทัดใหม่ แต่อยู่ในช่องเดิม คุณไม่สามารถกดปุ่ม Enter เฉยๆ เหมือนกับโปรแกรมอื่นๆ ทั่วไปอย่าง Microsoft Word ได้ (หาดกด Enter ใน Excel มันจะเด้งลงไป Cell ถัดไปที่อยู่ข้างล่างแทน !!)

ใน Microsoft Excel การจะบังคับขึ้นบรรทัดใหม่ได้นั้น ต้องกดปุ่ม Alt+Enter เท่านั้นครับ (กด Alt ค้างไว้ แล้วค่อยกด Enter) เมื่อต้องการจะขึ้นบรรทัดใหม่ตรงไหนให้กดตรงนั้นครับ

แชร์ความรู้ให้เพื่อนๆ ของคุณ

18    

18    

การเขียนสูตร Excel นั้น มีทั้งสูตรที่ง่ายและซับซ้อน บางสูตรต้องใช้ Logic หรือตรรกะในการคิดค่อนข้างสูง แต่ก็มีบางสูตรที่ดูเหมือนว่ามันจะอยู่ในระดับ “พิศดาร” ซึ่งเรียกว่าเหนือ Logic ปกติ หรือบางตัวถึงขั้นเหนือจินตนาการเลยก็ว่าได้ครับเพราะฟังก์ชันหลายตัวมันไม่ได้ถูกออกแบบให้ทำงานนั้นๆ ด้วยซ้ำ แต่ด้วยการประยุกต์ขั้นสุดยอด มันจึงสามารถสร้างผลลัพธ์เจ๋งๆ ได้อย่างน่าอัศจรรย์

บอกไว้ก่อนว่าการที่เกิดสูตรพิศดารแบบนี้เพราะ Excel ดันยังไม่มีฟังก์ชันเจ๋งๆ ให้ใช้ได้แบบตรงไปตรงมาเหมือนของ Google Sheets น่ะสิ หึ!!

แต่อย่าลืมว่าในชีวิตจริงเราไม่จำเป็นต้องแก้ปัญหาด้วยสูตรเสมอไป ปัญหาทุกข้อที่ผมเอามาเขียนในบทความนี้ สามารถแก้ได้ง่ายๆด้วย Power Query ของ Excel ด้วยซ้ำ แต่ในบางครั้งเราต้องการให้ได้ผลลัพธ์ทันที (รวมถึงต้องการผูกกับ Conditional Format, Data Validation, หรือไม่ก็เพื่อฝึกสมอง 555) ก็เลยต้องเขียนเป็นสูตรออกมา

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

ที่ผมทำบทความนี้เพื่อจะได้รวบรวมสูตรเจ๋งๆ ที่ผมประทับใจไว้ในบทความเดียว (บางสูตรผมก็มีปรับนิดหน่อยตามความชอบของตัวเองด้วย) และจะทำการอธิบายหลักการทำงานของสูตรนั้นๆ ให้ เผื่อบางท่านสงสัยว่ามันทำงานยังไง? จะได้เป็นการเพิ่มความรู้ให้ตนเองได้นะครับ

สารบัญ

  • สูตรดึงข้อความที่มีตัวคั่นหลายตัว
  • สูตรแยกข้อมูลออกมาทีละอักขระ แล้วคัดเอาตัวที่ต้องการ
  • สูตรดึงข้อมูลเฉพาะตัวเลขด้วย NPV
    • แล้วสูตรการเงินแบบ NPV ทำแบบนี้ได้อย่างไร??
  • ดึงข้อมูลเลขที่มีจุดทศนิยมปนอยู่
  • Append ข้อมูลด้วยสูตร FILTERXML
    • ทำความรู้จัก FILTERXML
    • วิธีที่เหมาะกับ Append คอลัมน์เดียว
    • วิธี Append หลายคอลัมน์ ทำได้หลายแบบ
    • วิธีใช้ FILTERXML เลือก item ตัวที่ต้องการ
  • จบแล้ว

สูตรดึงข้อความที่มีตัวคั่นหลายตัว

ปกติแล้วถ้าเรามีข้อมูลที่มีตัวคั่นหลายตัว การแยกด้วยสูตรจะค่อนข้างยุ่งยาก เพราะถ้าคิดแบบตรงไปตรงมาคือต้องค่อยๆ หาตำแหน่งของตัวคั่นแต่ละตัวให้ได้ก่อนแล้วตัดคำที่ต้องการออกมา ซึ่งวิธีที่ผมเคยใช้ในอดีตคือใช้ SUBSTITUTE แทนที่ช่องว่างลำดับต่างๆ ด้วยตัวประหลาดๆ แล้วค่อยหาตำแหน่งของตัวประหลาดนั้นๆ อีกทีด้วย FIND ซึ่งจะค่อนข้างยุ่งยาก (ถ้าใน Google Sheets ใช้ SPLIT, หรือใน Power Query ก็มี Split เช่นกัน ก็จบละ)

Excel ตัดข้อความ ขึ้นบรรทัดใหม่

ซึ่งจะเห็นว่าสูตรดูยุ่งยากมากจริงๆ ดังนั้นมาดูอีกเทคนิคนึงที่เจ๋งมากๆ เลยนั่นก็คือ แทนที่ช่องว่างด้วยช่องว่างเยอะๆ (เมื่อเทียบกับข้อความ) ไปเลย เช่น 20 ตัว แล้วค่อยตัดเลือกสิ่งที่ต้องการมา แล้วค่อย TRIM เอาช่องว่างออกไป

ซึ่งสรุปแล้วสามารถเขียนสูตรได้แบบนี้ ซึ้งสั้นกว่าเยอะ ใช้ง่ายด้วย แต่มีความลึกซึ้ง

=TRIM(MID(SUBSTITUTE(ข้อความ," ",REPT(" ",เลขเยอะ)),เลขเยอะ*(ลำดับ-1),เลขเยอะ))
Excel ตัดข้อความ ขึ้นบรรทัดใหม่

ใครสนใจรายละเอียดเทคนิคนี้ ลองดูคลิปของ Excel Wizard ได้ครับ (ใช้แยกที่อยู่ ดีมากๆ)

นอกจากวิธีนี้แล้ว เรายังสามารถใช้ฟังก์ชัน FILTERXML มาช่วยเลือกเอา item ลำดับที่ต้องการได้ด้วย รายละเอียดอยู่ใน section หลังครับ

สูตรแยกข้อมูลออกมาทีละอักขระ แล้วคัดเอาตัวที่ต้องการ

การทำงานแบบนี้ ถ้าจะทำง่ายๆ ใน Excel ก็สามารถทำได้ด้วย Flash Fill หรือไม่ก็ Text.Select ของ Power Query แต่ถ้าเราจะเขียนด้วยสูตรจริงๆ ล่ะ จะทำไงดี?

ถ้าเรามีคำอยู่ แล้วต้องการแยกแต่ละอักขระออกมาให้เป็นคนละ item กัน เพื่อที่จะเอาไปทำงานต่อ เราสามารถใช้ MID มาช่วย

=MID(text,start_num,num_chars)

โดยใส่ข้อมูลใน start_num แบบ Array เป็นเลขลำดับได้ โดยจะใช้ SEQUENCE หรือพวก ROW สร้างขึ้นมาก็ได้

ถ้ามี SEQUENCE ให้ใช้ (Excel 365)

MID(ข้อความ,SEQUENCE(LEN(ข้อความ)),1)

ถ้าไม่มี SEQUENCE

MID(ข้อความ,ROW($A$1:$A$เลขเยอะๆ),1)

เท่านี้เราจะสามารถแยกข้อความออกมาทีละอักระได้แล้ว

Excel ตัดข้อความ ขึ้นบรรทัดใหม่

คราวนี้เราสามารถคัดเลือกเฉพาะสิ่งที่ต้องการได้ แล้วค่อยเอามารวมกันด้วย CONCAT หรือ TEXTJOIN (ต้องมี Excel ใหม่ๆ)

สมมติผมอยากได้เฉพาะภาษาไทยเท่านั้น ผมก็สามารถเขียนเงื่อนไขว่าถ้าเป็นภาษาไทยให้เก็บไว้ ถ้าไม่ใช่ให้เป็น “” (blank text) ไป ซึ่งสามารถใช้ CODE() มาช่วยเช็คได้ ซึ่งตัวเลข ตัวหนังสือ แต่ละอันจะมี Code ต่างกัน เช่น ถ้าอยากได้แต่ภาษาไทย code >=161 ก็น่าจะพอ

Excel ตัดข้อความ ขึ้นบรรทัดใหม่

แต่ถ้าจะเอา space กับ . ด้วย ผมอาจเช็คว่า ต้องมี code >=161 หรือ <=46 (คราวนี้ขอใช้ LET เพื่อจะได้ไม่ต้องเขียนสูตรเดิมๆ ซ้ำหลายรอบ แต่ถ้าไม่มีก็สามารถเขียนซ้ำๆ ได้นะ)

Excel ตัดข้อความ ขึ้นบรรทัดใหม่

จากนั้นค่อยใช้ CONCAT หรือ TEXTJOIN มาเชื่อมก็ได้ แล้วเอา TRIM ครอบอีกที ดังนี้

Excel ตัดข้อความ ขึ้นบรรทัดใหม่

สูตรดึงข้อมูลเฉพาะตัวเลขด้วย NPV

การที่เราได้ข้อมูลที่ปนมาทั้งตัวหนังสือและตัวเลข แล้วเราต้องการเฉพาะตัวเลขนั้น ถ้าแก้ปัญหาด้วย Logic ปกติ ก็อาจจะพยายามแยกอักขระแต่ละตัวมาพิจารณา แล้วคัดเลือกเอาไว้เฉพาะ 0-9 เท่านั้น คล้ายๆ วิธีในข้อข้างบน

แต่หากทำด้วยท่าพิศดาร ปรากฏว่ามันสามารถใช้ฟังก์ชันทางการเงินที่ชื่อว่า NPV (Net Present Value) มาช่วยได้เฉยเลย และดีกว่าวิธีข้างบนตรงที่ไม่จำเป็นต้องใช้ Excel365 ด้วย (ไม่จำเป็นต้องมี CONCAT หรือ TEXTJOIN) ซึ่งทำได้ดังนี้

=NPV(9,ช่วงข้องมูล)*10^COUNT(ช่วงข้อมูล)

โดยที่ถ้าข้อมูลปนกันมาใน Cell เดียวเลย จะยากขึ้น คือต้องใช้สูตรแตกอักระออกมาให้ได้ก่อน (เหมือนหัวข้อที่แล้ว) แล้วค่อยใช้ NPV ดังนี้

=NPV(9,IFERROR(--MID(ข้อความ,ROW($A$1:$A$เลขเยอะๆ),1),""))
*10^COUNT(--MID(ข้อความ,ROW($A$1:$A$เลขเยอะๆ),1))
Excel ตัดข้อความ ขึ้นบรรทัดใหม่

แล้วสูตรการเงินแบบ NPV ทำแบบนี้ได้อย่างไร??

สิ่งที่สูตร NPV ทำคือเอาตัวเลขแต่ละตัวไปหารด้วย (1+อัตราดอกเบี้ย)^งวด ซึ่งเป็นการ Discount มูลค่าเงินตามกาลเวลามาอยู่ที่ปัจจุบันนั่นเอง

ซึ่งพอเราพลิกแพลงใส่อัตราดอกเบี้ยเป็นเลข 9 (ดอกเบี้ย 900%) มันจะได้เป็นเอาแต่ละตัวไปหารด้วย (1+9)^งวด หรือ 10^งวดนั่นเอง จะได้แบบนี้ ตอนแรกมันจะออกมาเป็นทศนิยม ถ้าต้องการทำเป็นเลขปกติ เราแค่เอาไปคูณ 10^จำนวนตัวเลข ก็จะได้ผลตามต้องการแล้วล่ะ

Excel ตัดข้อความ ขึ้นบรรทัดใหม่

ใครสนใจรายละเอียดเทคนิคนี้ ลองดูคลิปของ Excel Wizard ได้ครับ

ดึงข้อมูลเลขที่มีจุดทศนิยมปนอยู่

ในเคสนี้ เราจะใช้สูตร NPV จะใช้ตรงๆ ไม่ได้ เพราะมันจะสนแค่ตัวเลขอย่างเดียว

ทางแก้นึงที่ทำได้ (หากดึงดันว่ายังจะใช้ NPV ต่อไป) ก็คือ เปลี่ยนทศนิยมเป็นเลขประหลาดๆ ซักชุดนึง แล้วค่อยเปลี่ยนกลับเป็น . ทีหลัง เช่น

=--SUBSTITUTE(
NPV(9,IFERROR(--MID(SUBSTITUTE(ข้อความ,".","เลขประหลาด"),ROW($A$1:$A$เลขเยอะ),1),""))
*10^COUNT(--MID(SUBSTITUTE(ข้อความ,".","เลขประหลาด"),ROW($A$1:$A$เลขเยอะ),1)),
"เลขประหลาด",".")
Excel ตัดข้อความ ขึ้นบรรทัดใหม่

อย่างไรก็ตาม ถ้ามีจุดทศนิยมด้วยแบบนี้ ทางคุณโบแนะนำว่าใช้วิธีพิศดารอีกอันดีกว่า (แต่ก็โคตรลึกซึ้งนะ) ซึ่งก็คือวิธีนี้ครับ

Excel ตัดข้อความ ขึ้นบรรทัดใหม่

พอรวบสูตรแล้วจะเป็นแบบนี้

=-LOOKUP(0,-MID(ข้อความ,MIN(FIND({0,1,2,3,4,5,6,7,8,9},ข้อความ&"0123456789")),{1,2,3,4,5,6,7,8,9,10,11,12}))

โดยที่สามารถทำให้สั้นลงได้อีก เป็นแบบนี้ (มันคือเรื่องเดียวกัน)

=-LOOKUP(0,-MID(A2,MIN(FIND(ROW($A$1:$A$10)-1,A2&1/17)),ROW($A$1:$A$เลขเยอะๆ)))
  • {0,1,2,3,4,5,6,7,8,9} ทดแทนได้ด้วย ROW($A$1:$A$10)-1 หรือแทนด้วย {0,1,2,3,4}+{0;5} ก็ได้
    Excel ตัดข้อความ ขึ้นบรรทัดใหม่
  • {1,2,3,4,5,6,7,8,9,10,11,12,…} ทดแทนได้ด้วย ROW($A$1:$A$เลขเยอะๆ)
  • &”0123456789″ ทดแทนได้ด้วย &1/17
    • เพราะว่า 1/17 จะได้เลขครบตั้งแต่ 0-9 เลย คือ 0.0588235294117647 (เหอๆ ใครคิดมาเนี่ย…)

ใครสนใจรายละเอียดเทคนิคนี้ ลองดูคลิปของ Excel Wizard ได้ครับ

Append ข้อมูลด้วยสูตร FILTERXML

สิ่งนึงที่สูตร Excel ยังทำได้ค่อนข้างลำบาก ทั้งๆ ที่เป็นเรื่องที่ควรทำได้ง่ายๆ ก็คือการเอาข้อมูล 2 ตารางมาต่อแถวกันด้วยสูตร ซึ่งถ้าใช้ Power Query ก็จะมีคำสั่ง Append Query ให้ทำได้แบบชิลๆ เลย หรือถ้าเป็นใน DAX ก็จะมี UNION ให้ใช้แบบสบายๆ เช่นกัน

แต่ใน Excel ดันไม่มีวิธี Append ที่ง่ายๆ เลย นอกจากการประยุกต์เอาฟังก์ชัน FILTERXML มาใช้

ทำความรู้จัก FILTERXML

ปกติแล้วฟังก์ชัน FILTERXML เอาไว้ใช้งานกับข้อมูล XML หรือแม้แต่ HTML บนเว็บไซต์ซึ่งจะมี Tag เปิด ปิด แต่ละ Element ที่เป็นองค์ประกอบของเว็บนั้นๆ อยู่ด้วย ซึ่งจะมีวิธีการเขียนประมาณนี้ ซึ่งสังเกตว่าจะมีการเปิด และปิด tag ด้วย

<tag>ข้อมูล</tag>

ซึ่งเราสามารถใส่ tag ต่างๆ ซ้อนกันได้เรื่อยๆ เช่น

=MID(text,start_num,num_chars)
0

เจ้าฟังก์ชัน FILTERXML นั้น สามารถคัดเลือกเอาข้อมูลเฉพาะใน tag ที่ต้องการได้ โดยระบุเข้าไปใน xpath ของสูตร FILTERXML

=MID(text,start_num,num_chars)
1

จริงๆ แล้ว xpath สามารถกำหนดเงื่อนไขได้มากมาย (ลองดูได้ที่นี่) แต่ถ้าเอาแบบง่ายสุด หากเราต้องการ tag ที่ชื่อว่า tagB ก็เขียน xpath แบบนี้ได้เลย “//tagB”

เช่น ถ้าเรามีข้อมูล xml แบบนี้ในช่อง A1

=MID(text,start_num,num_chars)
2

เราจะดึงองค์ประกอบแต่ละส่วนได้แบบนี้เลย ซึ่งจะเห็นว่ามี tag ย่อยชื่อว่า name, topic, level ให้เราสามารถดึงได้

Excel ตัดข้อความ ขึ้นบรรทัดใหม่

วิธีที่เหมาะกับ Append คอลัมน์เดียว

แบบง่าย คือ Append ข้อมูลที่มีแค่คอลัมน์เดียว เช่นแบบนี้

Excel ตัดข้อความ ขึ้นบรรทัดใหม่

ซึ่งรวบสูตรได้เป็นแบบนี้

=MID(text,start_num,num_chars)
3

สามารถเขียนในรูปทั่วไปได้ว่า

=MID(text,start_num,num_chars)
4

วิธี Append หลายคอลัมน์ ทำได้หลายแบบ

ใช้ CHOOSE (เหมาะกับคอลัมน์แค่ 2)

ถ้ามีแค่ 2 คอลัมน์ เราจะใช้วิธีคล้ายเดิม แต่ผสมกับ CHOOSE อีกทีเพื่อทำการรวม 2 คอลัมน์เข้าด้วยกัน ตามนี้

Excel ตัดข้อความ ขึ้นบรรทัดใหม่

ถ้ารวบสูตรก็จะได้ประมาณนี้

=MID(text,start_num,num_chars)
5

ใช้ INDEX+SEQUENCE (วิธีนี้ใช้ได้กับกี่คอลัมน์ก็ได้)

แต่ถ้าจำนวนคอลัมน์มากกว่านี้ ผมแนะนำวิธีของคุณโบ Excel Wizard ซึ่งรองรับหลายคอลัมน์ (แต่จำนวนแถวอาจจะได้แค่ประมาณหลักร้อยบรรทัด เพราะติดข้อจำกัดการเก็บข้อมูล text ของ Excel)

ซึ่งของคุณโบทำได้ด้วยวิธีนี้ (ผมมีดัดแปลงตอนจบนิดหน่อย ให้เปลี่ยน range ได้สะดวกขึ้น)

Excel ตัดข้อความ ขึ้นบรรทัดใหม่

ถ้ารวบสูตร และทำให้ Range เหลือแค่ 2 ตัว ให้อ้างอิงง่ายๆ ด้วย LET ก็ทำได้ตามนี้ (ซึ่ง Range จะใช้เป็น Table ก็ได้ จะได้งอกตามได้ง่ายๆ)

=MID(text,start_num,num_chars)
6
Excel ตัดข้อความ ขึ้นบรรทัดใหม่

ใครสนใจรายละเอียดเทคนิคนี้ ลองดูคลิปของ Excel Wizard ได้ครับ

วิธีใช้ FILTERXML เลือก item ตัวที่ต้องการ

จริงๆ แล้วเราสามารถใช้ FILTERXML ให้ได้ผลลัพธ์ทุกตัว แล้วใช้ INDEX เลือกเอาตัวที่ต้องการมาอีกทีก็ได้ แต่มันยุ่งยากไปวิธีที่ดีกว่าคือใส่ xpath ให้เอาตัวนั้นๆ กลับมาให้เราเลย

เราสามารถใส่ xpath ว่า //tag[ลำดับitem] เพื่อดึงเอาเฉพาะ item ลำดับที่ต้องการได้เลย แปลว่าสามารถใช้แทนวิธีแทนด้วยช่องว่างเยอะๆ ได้ทันทีครับ

Excel ตัดข้อความ ขึ้นบรรทัดใหม่

จบแล้ว

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