ผมเชื่อว่าหลายคนต้องเคยเจอปัญหาในการเชื่อม 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) ได้ก็จบละ เฮ้อ)