เปิดโปง! เทคนิคการเชื่อม Text จากหลายๆ Cell (มากๆ) เข้าด้วยกัน

เลขลำดับมหัศจรรย์ แม้โดน Filter ก็เรียงต่อเนื่องได้
มาดูกันว่า TRIM ตัดอะไรทิ้งบ้าง?

ผมเชื่อว่าหลายคนต้องเคยเจอปัญหาในการเชื่อม Text จากหลายๆช่องเข้าด้วยกันมาก่อนแน่ๆ เพราะมันไม่มีสูตรสำเร็จรูปเช่นเดียวกับปัญหาอื่นๆ เช่น การบวกหลายๆช่องเข้าด้วยกัน (ฟังก์ชั่น  SUM ทำได้) หรือการ คูณหลายๆช่องเข้าด้วยกัน (ฟังก์ชั่น PRODUCT ทำได้) แต่ทำไม พอจะเชื่อม Text เข้าด้วยกันดันทำไม่ได้!!

แม้ว่า Excel จะมีฟังก์ชั่น CONCATENATE มาให้ แต่มันก็ไม่สามารถใช้แบบ =CONCATENATE(A1:H1) ได้
แต่กลับต้องใส่เป็น =CONCATENATE(A1,B1,C1,D1,E1,F1,G1,H1) หรือ =A1&B1&C1&D1&E1&F1&G1&H1 แทน
ซึ่งเหนื่อยมากๆ โดยเฉพาะหากต้องเชื่อมมากกว่า 10 Cell ขึ้นไป!! (และอาจทำผิดโดยไม่รู้ตัวด้วย)

วันนี้ผมมีเทคนิคแก้ปัญหาดังกล่าวมานำเสนอ โดยไม่จำเป็นต้องใช้ VBA เข้าช่วยด้วย

ทำยังไงมาดูกันครับ ลองทำตามดูนะครับ ดูเหมือนจะเยอะ แต่ ทำจริงๆไม่กี่วินาทีเอง

ก่อนอื่นสมมติว่าผมต้องการเชื่อม Text ในช่อง A1:H1  (มีคำว่า Text1, Text2…Text8) วางอยู่

  • ในช่อง A3 ให้ใส่สูตร =COLUMN(แล้วเลือกช่องแรกที่เราต้องการเชื่อม) ในที่นี้ คือ =COLUMN(A1)
    step1
  • ส่วยใน B3 ใส่สูตร =A3+1 แล้วลาก Autofill ไปจนถึงช่องสุดท้าย (ในที่นี้คือช่อง H จะได้เลข 8)
    ในช่อง A5 ให้ใส่สูตร =ADDRESS(1,A3,4) เพราะว่า

    • row_numใส่เลข 1 เพราะเราต้องการเชื่อมแถวที่1
    • column_num link ไปที่ช่อง A3 เพื่อบอกว่าจะเอาคอลัมน์ที่เท่าไหร่กลับมา
    • abs_num ใส่ 4 ให้เป็นสูตรแบบ Relative
      step2
  • จากนั้น enter ได้เลย จะเห็นสูตรเป็นคำว่า A1 ซึ่งเป็นตัวเริ่มต้นของสิ่งที่เราจะเชื่อม
  • กดลาก Autofill ไปจนถึง H5 จะได้สูตรในช่องสุดท้ายเป็น H1 ซึ่งเป็นตัวสุดท้ายที่เราจะเชื่อม
  • ไปที่ช่องที่เราจะใส่สูตรเพื่อที่จะเชื่อม A1:H1 สมมติว่าเราจะเชื่อมในช่อง A7
  • จากนั้นใส่สูตรว่า =CONCATENATE(A5:H5) เพื่อเลือกช่องที่เราใส่สูตร ADDRESS ไป แต่ยังไม่ต้องกด Enter
  • ให้ลากแถบดำในคำว่า A5:H5 แล้วกด F9 มันจะหลายเป็นคำว่า {“A1″,”B1″,”C1″,”D1″,”E1″,”F1″,”G1″,”H1”}
    step3
  • ให้ Copy ออกไป Paste ที่อื่น(เช่น Notepad หรือ Word) แล้วลบ { } ออก
  • แล้วกด Replace All เครื่องหมาย ” ด้วยค่าว่างเปล่า (ใน Replace with ไม่ต้องใส่อะไรเลย)
    step5
  • จะเหลือแต่คำว่า A1,B1,C1,D1,E1,F1,G1,H1 ให้เอากลับมา Paste ในสูตร CONCATENATE
  • จะได้ว่า =CONCATENATE(A1,B1,C1,D1,E1,F1,G1,H1) เป็นอันจบครับ
    step6 

    step7

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

แล้วถ้าจะให้มีตัวคั่นแต่ละช่องจะทำยังไง??

สมมติถ้าจะให้แต่ละช่องคั่นด้วย / ให้ทำแบบนี้ครับ

  • ในช่อง A7 ให้ใส่สูตรว่า =CONCATENATE(A5:H5&”@/”)  ซึ่ง@ คือเครื่องหมายอะไรก็ได้ที่คิดว่าไม่มีในข้อความเรา แล้วต่อด้วย / ซึ่งคือตัวเชื่อม
  • ลากแถบดำคำว่า A5:H5&”@/” แล้วกด F9
    step3-alter
  • จะได้คำว่า {“A1@/”,”B1@/”,”C1@/”,”D1@/”,”E1@/”,”F1@/”,”G1@/”,”H1@/”} ให้ Copy ไปไว้ที่อื่น
    step4-alter
  • Replace คำว่า ” ด้วยค่าว่างเปล่า
    step5-alter
  • Replace คำว่า @ ด้วยคำว่า ,”
    step5-alter2
  • Replace / ด้วย /”
    step5-alter3
  • ลบคำเกินๆ ออก เช่น {} และ ” / ส่วนท้ายที่เกิน ให้เหลือแค่ A1,”/”,B1,”/”,C1,”/”,D1,”/”,E1,”/”,F1,”/”,G1,”/”,H1
  • เอามาใส่ในสูตรเดิม จะได้เป็น =CONCATENATE(A1,”/”,B1,”/”,C1,”/”,D1,”/”,E1,”/”,F1,”/”,G1,”/”,H1) เป็นอันจบ!
    step6-alter
    step7-alter

ใครมีเทคนิคที่ดีกว่านี้ก็นำเสนอได้เลยนะครับ ตอนนี้ผมคิดออกแค่นี้แหละ อิอิ (จริงๆ ถ้า Microsoft แก้ให้สูตร =CONCATENATE(A1:H1,separater) ได้ก็จบละ เฮ้อ)

........

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

อย่าลืมกดติดตามเทพเอ็กเซลได้ที่ Facebook.com/ThepExcel
และสามารถติดตามคลิปวีดีโอเจ๋งๆ ได้ที่ YouTube Channel ของ Thep Excel ครับ


เลขลำดับมหัศจรรย์ แม้โดน Filter ก็เรียงต่อเนื่องได้
มาดูกันว่า TRIM ตัดอะไรทิ้งบ้าง?

Posted on: July 21, 2014
Tags: ,

Leave a Reply

Your email address will not be published. Required fields are marked *