การใช้งาน excel : การแยกชื่อกับนามสกุล ที่อยู่ใน Cell เดียวกัน

excel สูตร vba

การแยกชื่อกับนามสกุล ที่อยู่ใน Cell เดียวกัน

เมื่อวาน น้องนัท น้องที่เคยทำงานด้วยกันโทรมาถามเรื่องการใช้งาน excel
ปัญหาของเขามีอยู่ว่า ที่ทำงานต้องการสร้างอีเมล์ให้กับพนักงานทั้งหมด
โดยที่เอา ชื่อ ตามด้วยเครื่องหมาย Under Scroll และนามสกุลสามตัวอักษรแรก
จากนั้นก็ต่อท้ายด้วย @ และตามด้วย domain name

ยกตัวอย่างเช่น
name surname จะได้เป็น [email protected]

ปัญหาคือ ไฟล์ข้อมูลที่เป็น excel ชื่อ และนามสกุลอยู่ใน cell เดียวกัน โดยที่มีช่องว่างกั้นกลางดังภาพด้านล่าง

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

วิธีแรก ใช้สูตรของ Excel

ผมสร้างตารางแบบนี้ครับ เพื่อให้เข้าใจง่ายๆในระหว่างที่ทำทีละขั้นตอน

อธิบายตารางที่สร้างขึ้นมา

ที่เซล C2 เราสารมารถใส่ โดเมนอะไรก็ได้ จะมีผลต่อรายชื่อทั้งหมด
ในช่อง Blank Address จะเป็นตำแหน่งของช่องว่าง ระหว่างชื่อกับนามสกุล
ช่อง name และ surname เป็นช่องที่แยกชื่อและนามสกุลออกจากกัน
ช่อง Results คือผลสุดท้ายที่ต้องการ เช่น [email protected]

เริ่มการทำงาน

ขั้นแรกให้ทำการหาตำแหน่งของช่องว่างระหว่างชื่อ กับนามสกุลโดยใช้ Function FIND
รูปแบบการใช้งาน : FIND(อักษรที่ต้องการหา,ข้อความต้นฉบับ,ตำแหน่งเริ่มต้น)

ผลที่ได้ คือ เลข 6 คือตำแหน่งของช่องว่าง

จากนั้นใช้ Function LEFT เพือทำการตัดข้อความโดยเริ่มจาดด้านซ้ายสุด

รูปแบบการใช้งาน LEFT(ข้อความต้นฉบับ,ความยาวอักษรที่ต้องการ)

จากรูปจะเห็นว่าความยาวตัวอักษรที่ต้องการ(Num_chars)มี การ ลบ1
เนื้อจากเซล C5 คือตำแหน่งที่เป็นช่องว่างที่เราไม่ต้องการ ต้องนับถอยไปหนึ่งตำแหน่ง จึงต้อง ลบ1

ผลที่ได้คือชื่อ ดังภาพด้านล่าง

ต่อไปคือการหานามสกุล 3 ตัวอักษรแรก โดยการใช้ Function MID เพื่อการตัดตัวอักษรออกมาจากข้อความต้นฉบับ

รูปแบบการใช้งาน MID(ข้อความต้นฉบับ,ตำแหน่งเริ่มต้น,จำนวนตัวอักรที่ต้องการ)

จากรูปด้านบน Text คือข้อความต้นฉบับ ,
Start_num คือ ตำแหน่งเริ่มต้น จะเห็นว่ามีการ บวก1 เข้าไป เนื่องจากเซล C5 คือ ตำแหน่งของช่องว่าง
Num_chars คือจำนวนตัวอักษรที่ต้องการทั้งหมดสามตัวอักษร

เมื่อเสร็จแล้วจะได้ นามสกุลสามตัวแรก อยู๋ในคอลัมภ์ของ Surname แบบภาพด้านล่าง

คราวนี้ก็มาถึงการใส่ Domain ที่ต้องการ ยกตัวอย่างเช่นภาพด้านล่าง

ขั้นสุดท้ายคือการนำข้อมูลทั้งหมดมารวมกันที่คอลัมภ์ Result
โดยในตำแหน่งของ C2 ต้องการทำ lockไว้ด้วย

เมื่อทำเสร็จแล้วจะได้ดังภาพ

ทดสอบโดยการพิมพ์รายชื่อ

ทำการ Drag เซลที่เป็นสูตร และ คลิกขวา เลือก copy แบบภาพด้านล่าง

เลือกช่วงเซลที่ต้องการ Paste สูตร คลิกขวา แล้วเลือก Paste Special

เลือก Formulas แล้ว คลิก ok

เสร็จแล้ว จะได้ดังภาพด้านล่าง
ที่ช่อง Results คือผลสุดท้ายที่ต้องการ คือ [email protected]

จบการทำงานโดยการใช้สูตรของ excel

วิธีที่สอง ใช้ VBA

สร้างชีทหน้าตาแบบในรูปด้านล่าง โดยตั้งชื่อชีทว่า "DATA"

เปิดตัว Visual Basic Editor ขึ้นมา

สร้างโมดูลขึ้นมาใหม่ และพิมพ์โค๊ดลงไปดังภาพ

VBA Code

Sub Generate_Email()
    ' อ่าน โดเมนเนม มาเก็บไว้ในตัวแปรชื่อ Domain_Name
            DOMAIN_NAME = Sheets("DATA").Range("C2")
    ' กำหนด Row ที่ต้องการทำงานเริมต้น ในที่นี้คือ ข้อมูล ชื่อแรก เก็บไว้ในตัวแปร Y
            Y = 5
  ' อ่านข้อมูลต้นฉบับในเซล Row = Y , Column = 2   เก็บไว้ในตัวแปรชื่อ DATA_NAME
            DATA_NAME = Sheets("DATA").Cells(Y, 2).Value
    ' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    ' เริ่มทำการ วนลูปไปจนกระทั่ง ข้อมูลต้นฉบับ ( DATA_NAME ) ไม่ใช่ค่าว่าง
    While DATA_NAME <> ""
           ' หาตำแหน่งของช่องว่างใน DATA_NAME เก็บไว้ในตัวแปรชื่อ BLANK_ADDRESS
                        BLANK_ADDRESS = InStr(1, DATA_NAME, " ")
            ' หาชื่อ เก็บไว้ในตัวแปรชื่อ FIRST_NAME
                        FIRST_NAME = Left(DATA_NAME, BLANK_ADDRESS - 1)
            ' หานามสกุล เก็บไว้ในตัวแปรชื่อ SURE_NAME
                        SURE_NAME = Mid(DATA_NAME, BLANK_ADDRESS + 1, 3)
            ' เอาชื่อ ต่อด้วย เครื่องหมาย _ (Under Scroll) ต่อด้วย นามสกุล ต่อด้วย "@" และ โดเมนเนม เก็บไว้ในตัวแปร EMAIL_NAME
                        EMAIL_NAME = FIRST_NAME & "_" & SURE_NAME & "@" & DOMAIN_NAME
            ' เอาคำตอบในตัวแปร EMAIL_NAME ไปใส่ใน Row = Y , Column = 3
                        Sheets("DATA").Cells(Y, 3).Value = EMAIL_NAME
            ' เพิ่มค่า Y เข้าไปอีก 1
                        Y = Y + 1
            ' อ่านข้อมูลต้นฉบับ เก็บไว้ในตัวแปร DATA_NAME )
                        DATA_NAME = Sheets("DATA").Cells(Y, 2).Value
    Wend
    ' สิ้นสุดการทำงานใน ลูป เมื่อขอมูลต้นฉบับเป็นค่าว่าง
    ' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
End Sub

เมื่อรัน vba แล้วจะได้คำตอบดังภาพ