สมาชิก
แลกเปลี่ยน

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

 excel สูตร vba 

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

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

ยกตัวอย่างเช่น
name surname จะได้เป็น name_sur@domain.com

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

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

 

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

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

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

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

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

ขั้นแรกให้ทำการหาตำแหน่งของช่องว่างระหว่างชื่อ กับนามสกุลโดยใช้ 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 คือผลสุดท้ายที่ต้องการ คือ name_sur@domain.com

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

 

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

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

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

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

VBA Code
Sub Generate_Email()
    ' อ่าน โดเมนเนม มาเก็บไว้ในตัวแปรชื่อ Domain_Name

            DOMAIN_NAME = Sheets("DATA").Range("C
2")

    ' กำหนด 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 แล้วจะได้คำตอบดังภาพ

 

 

  เสร็จสิ้นการทำงาน

 

หวังว่าท่านผู้อ่านคงจะได้ประโยชน์ ไม่มากก็น้อยนะครับ

บันทึกนี้เขียนที่ GotoKnow โดย 

· คำสำคัญ: excel สูตร vba 
· หมายเลขบันทึก: 253142 · เขียน:  
· ความเห็น:
6
 · อ่าน: แสดง 
· สัญญาอนุญาต: ไม่สงวนสิทธิ์ใดๆ
 แจ้งลบ
 
 แจ้งลบ
บันทึกก่อนนี้
บันทึกใหม่กว่า
suksom
เขียนเมื่อ Fri Apr 03 2009 22:37:27 GMT+0700 (ICT)

รูปไม่ขึ้นอ่ะครับ ชอบมากเลย ต้องการศึกษาอ่ะครับ

RITTICHAI
เขียนเมื่อ Fri Jul 03 2009 16:03:08 GMT+0700 (ICT)

รูปไม่มีครับ แก้ไขด้วบ เพื่อการศึกษา

RITTICHAI
เขียนเมื่อ Sat Jul 04 2009 07:09:22 GMT+0700 (ICT)

มาชมแล้วครับ เป็นการนำเสนอที่ดีมากครับ

wimon
IP: xxx.158.176.200
เขียนเมื่อ Wed Aug 26 2009 14:25:47 GMT+0700 (ICT)

เยี่ยมไปเลย ขอบคุณนะครับ ท่าน

poipet resort
IP: xxx.164.97.122
เขียนเมื่อ Tue Nov 02 2010 22:21:38 GMT+0700 (ICT)

โฮสุดยอดเลยครับ ขอนำไปใช้ต่อนะครับ

จากเด็ก <a href="http://www.grandexclusive.net/index.html">Poipet Resort</a>

ดอนบ้านดิน
เขียนเมื่อ Tue Nov 02 2010 22:25:39 GMT+0700 (ICT)

ยินดีแบ่งปันครับผม

อนุญาตให้แสดงความเห็นได้เฉพาะสมาชิก
ไม่อนุญาตให้แสดงความเห็น
{{ kv.current_user.preferred_name }} - เพิ่มความเห็นเพิ่มความเห็น
 ใส่รูปหรือไฟล์