ถ้าคุณใช้Excelเป็นจำนวนมาก คุณอาจเจอสถานการณ์ที่คุณมีชื่อในเซลล์เดียว และคุณจำเป็นต้องแยกชื่อออกเป็นเซลล์ต่างๆ นี่เป็นปัญหาทั่วไปในExcelและคุณอาจทำการค้นหาโดย Google และดาวน์โหลด^{(Google search and download)}มาโครต่าง ๆ 100 รายการที่เขียนโดยคนจำนวนมากเพื่อทำเพื่อคุณ

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

ถ้าคุณไม่ชอบสูตรและต้องการวิธีแก้ปัญหาที่เร็วกว่า^{(quicker solution)}ให้เลื่อนลงไปที่ส่วนข้อความเป็นคอลัมน์^{(Text to Columns)}ซึ่งจะสอนวิธีใช้ฟีเจอร์ Excel^{(Excel feature)}เพื่อทำสิ่งเดียวกัน นอกจากนี้ คุณลักษณะข้อความเป็นคอลัมน์ยังดีกว่าที่จะใช้ถ้าคุณมีมากกว่าสองรายการในเซลล์ที่คุณต้องการแยก ตัวอย่างเช่น ถ้าหนึ่งคอลัมน์มี 6 ช่องรวมกัน การใช้สูตรด้านล่าง^{(formulas below)}จะกลายเป็นเรื่องยุ่งและซับซ้อนจริงๆ

แยกชื่อใน Excel

ในการเริ่มต้น เรามาดูกันว่าปกติแล้วชื่อจะจัดเก็บในสเปรดชีต Excel^{(Excel spreadsheet)}อย่างไร สองวิธีที่พบบ่อยที่สุดที่ฉันเคยเห็นคือfirstname นามสกุล^(lastname)ด้วยการเว้นวรรคและนามสกุล^(lastname)โดยที่firstnameที่มีเครื่องหมายจุลภาคคั่นระหว่างทั้งสอง เมื่อใดก็ตามที่ฉันเห็นชื่อกลาง มักจะเป็นชื่อย่อ ของ ^(firstname) ชื่อย่อ^(midinitial) ด้าน^(lastname)ล่าง:

การใช้สูตรง่ายๆ และการรวมสูตรสองสูตรเข้าด้วยกัน ทำให้คุณสามารถแยกชื่อ นามสกุล และชื่อย่อกลางเป็นเซลล์ที่แยกจากกันในExcelได้อย่างง่ายดาย เริ่มต้นด้วยการแยกส่วนแรกของชื่อ ในกรณีของฉัน เราจะใช้สองฟังก์ชัน: left และ^{(left and search)} search นี่คือสิ่งที่เราต้องทำตามหลักเหตุผล:

ค้นหา^(Search)ข้อความในเซลล์เพื่อหาช่องว่างหรือเครื่องหมายจุลภาค^{(space or comma)}ค้นหาตำแหน่ง จากนั้นนำตัวอักษรทั้งหมดที่อยู่ด้านซ้ายของตำแหน่งนั้นออก

นี่คือสูตรง่ายๆ ที่ช่วยให้ทำงานได้อย่างถูกต้อง: =LEFT(NN, SEARCH(” “, NN) – 1)โดยที่ NN คือเซลล์ที่มีชื่อจัดเก็บอยู่ในนั้น -1 มีไว้เพื่อลบช่องว่างหรือเครื่องหมายจุลภาค^{(space or comma)}ที่ส่วนท้ายของสตริง

อย่างที่คุณเห็น เราเริ่มต้นด้วยฟังก์ชัน left^{(left function)}ซึ่งรับสองอาร์กิวเมนต์: สตริงและจำนวนอักขระที่คุณต้องการดึงโดยเริ่มจากจุดเริ่มต้นของสตริง ในกรณีแรก เราค้นหาช่องว่างโดยใช้เครื่องหมายคำพูดคู่และเว้นวรรคระหว่าง ในกรณีที่สอง เรากำลังมองหาเครื่องหมายจุลภาคแทนการเว้นวรรค แล้วผลลัพธ์สำหรับ 3 สถานการณ์ที่ฉันพูดถึงคืออะไร?

เราได้ชื่อจากแถวที่ 3^{(row 3)}นามสกุลจากแถวที่ 5^{(row 5)}และชื่อจากแถวที่^{(row 7)} 7 ยอดเยี่ยม! ดังนั้นขึ้นอยู่กับวิธีการจัดเก็บข้อมูลของคุณ ตอนนี้คุณได้แยกชื่อหรือนามสกุลแล้ว ตอนนี้สำหรับส่วนต่อไป ต่อไปนี้คือสิ่งที่เราต้องทำอย่างมีเหตุผล:

– ค้นหา^(Search)ข้อความในเซลล์เพื่อหาช่องว่างหรือเครื่องหมายจุลภาค^{(space or comma)}ค้นหาตำแหน่งแล้วลบตำแหน่งออกจากความยาวทั้งหมดของสตริง นี่คือสิ่งที่สูตรจะมีลักษณะดังนี้:

=RIGHT(NN,LEN(NN) -SEARCH(” “,NN))

ตอนนี้เราใช้ฟังก์ชันที่ถูกต้อง สิ่งนี้ใช้อาร์กิวเมนต์สองอย่างเช่นกัน: สตริงและจำนวนอักขระที่คุณต้องการดึงโดยเริ่มจากจุดสิ้นสุดของสตริงไปทางซ้าย เราต้องการความยาวของสตริงลบตำแหน่งของ ช่อง ว่างหรือเครื่องหมายจุลภาค ^{(space or comma)}ที่จะให้ทุกอย่างแก่เราทางด้านขวาของช่องว่างแรกหรือลูกน้ำ^{(space or comma)}

เยี่ยม^(Great)ตอนนี้เรามีส่วนที่สองของชื่อแล้ว! ในสองกรณีแรก คุณทำเสร็จแล้วค่อนข้างมาก แต่ถ้ามีชื่อกลางในชื่อ คุณจะเห็นว่าผลลัพธ์ยังมีนามสกุลที่มีชื่อกลางด้วย แล้วเราจะเอานามสกุลและชื่อกลางออกได้อย่างไร? ง่าย! เพียง^(Just)เรียกใช้สูตรเดิมอีกครั้งที่เราเคยใช้เพื่อให้ได้ส่วนที่สองของชื่อ

ดังนั้นเราจึงกำลังทำสิ่งที่ถูกต้องและคราวนี้ใช้สูตรบน เซลล์ชื่อ^{(name cell)}กลางและนามสกุลที่รวมกัน มันจะหาช่องว่างหลังอักษรตัวแรกตรงกลางแล้วเอาความยาวลบด้วยตำแหน่งของช่องว่าง^{(space number)}ของจำนวนอักขระที่ส่วนท้ายของสตริง

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

ข้อความเป็นคอลัมน์

นอกจากนี้ยังมีวิธีง่ายๆ อีกวิธีหนึ่งในการแยกข้อความที่รวมเป็นคอลัมน์แยกกันในExcel เป็นคุณลักษณะที่เรียกว่าText to Columnsและทำงานได้ดีมาก นอกจากนี้ยังมีประสิทธิภาพมากขึ้นหากคุณมีคอลัมน์ที่มีข้อมูลมากกว่าสองส่วน

ตัวอย่างเช่น ด้านล่าง ฉันมีข้อมูลบางส่วนโดยที่แถวหนึ่ง^{(one row)}มีข้อมูล 4 ส่วน และอีกแถวหนึ่งมีข้อมูล 5 ส่วน ฉันต้องการแบ่งออกเป็น 4 คอลัมน์และ 5 คอลัมน์ตามลำดับ อย่างที่คุณเห็น การพยายามใช้สูตรข้างต้นอาจไม่ได้ผล

ในExcelให้เลือกคอลัมน์ที่คุณต้องการแยกก่อน จากนั้นไปข้างหน้าและคลิกที่ แท็บ Dataจากนั้นคลิกที่Text to Columns^{( Text to Columns)}

การดำเนินการนี้จะแสดงวิซาร์ด Text to Columns ^{(Columns wizard)}ในขั้นตอนที่ 1^{(step 1)}คุณเลือกว่าฟิลด์จะถูกคั่นหรือความกว้างคงที่ ในกรณีของเรา เราจะเลือกDelimited

ในหน้าจอถัดไป คุณจะเลือกตัวคั่น คุณสามารถเลือกจากแท็บ อัฒภาค จุลภาค เว้นวรรค หรือพิมพ์แบบกำหนด^{(custom one)}เอง

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

คลิกเสร็จสิ้น^(Finish)และดูว่าข้อมูลของคุณถูกแยกออกเป็นคอลัมน์อย่างน่าอัศจรรย์อย่างไร อย่างที่คุณเห็น แถวหนึ่งเปลี่ยนเป็นห้าคอลัมน์ และอีกแถวหนึ่งกลายเป็นสี่คอลัมน์ ฟีเจอร์ Text to Columns มี^{(Columns feature)}ประสิทธิภาพมากและทำให้ชีวิตของคุณง่ายขึ้นมาก

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

How to Separate First and Last Names in Excel

If you use Excel a lot, you have probably run across a situation whеre you hаve a name in a single cell and yоυ need to separate the name into different cells. Τhis is a very common issue in Excel and yoυ can probably do a Google search and download 100 different macros written by various people to do it for you.

However, in this post, I’ll show you how to setup a formula so you can do it yourself and actually understand what is going on. If you use Excel a lot, it’s probably a good idea to learn some of the more advanced functions so that you can do more interesting things with your data.

If you don’t like formulas and want a quicker solution, scroll down to the Text to Columns section, which teaches you how to use an Excel feature to do the same thing. In addition, the text to columns feature is also better to use if you have more than two items in a cell you need to separate. For example, if one column has 6 fields combined together, then using the formulas below will become really messy and complicated.

Separate Names in Excel

To get started, let’s see how names are usually stored in a Excel spreadsheet. The most common two ways I have seen are firstname lastname with just a space and lastname, firstname with a comma separating the two. Whenever I have seen a middle initial, it’s usually firstname midinitial lastname like below:

Using some simple formulas and combining a couple of them together, you can easily separate the first name, last name and middle initial into separate cells in Excel. Let’s start with extracting the first part of the name. In my case, we’re going to use two functions: left and search. Logically here’s what we need to do:

Search the text in the cell for a space or comma, find the position and then take out all the letters to the left of that position.

Here’s a simple formula that gets the job done correctly: =LEFT(NN, SEARCH(” “, NN) – 1), where NN is the cell that has the name stored in it. The -1 is there to remove the extra space or comma at the end of the string.

As you can see, we start out with the left function, which takes two arguments: the string and the number of characters you want to grab starting from the beginning of the string. In the first case, we search for a space by using double quotes and putting a space in-between. In the second case, we are looking for a comma instead of a space. So what is the result for the 3 scenarios I have mentioned?

We got the first name from row 3, the last name from row 5 and the first name from row 7. Great! So depending on how your data is stored, you have now extracted either the first name or the last name. Now for the next part. Here’s what we need to do logically now:

– Search the text in the cell for a space or comma, find the position and then subtract the position from total length of the string. Here’s what the formula would look like:

=RIGHT(NN,LEN(NN) -SEARCH(” “,NN))

So now we use the right function. This takes two arguments also: the string and the number of characters you want to grab starting from the end of the string going left. So we want the length of the string minus the position of the space or comma. That will give us everything to the right of the first space or comma.

Great, now we have the second part of the name! In the first two cases, you’re pretty much done, but if there is a middle initial in the name, you can see that the result still includes the last name with the middle initial. So how do we just get the last name and get rid of the middle initial? Easy! Just run the same formula again that we used to get the second section of the name.

So we are just doing another right and this time applying the formula on the combined middle initial and last name cell. It will find the space after the middle initial and then take the length minus the position of the space number of characters off the end of the string.

So there you have it! You have now split the first name and last name into separate columns using a few simple formulas in Excel! Obviously, not everyone will have their text formatted in this way, but you can easily edit it to suit your needs.

Text to Columns

There is also another easy way you can separate combined text into separate columns in Excel. It’s a featured called Text to Columns and it works very well. It’s also much more efficient if you have a column that has more than two pieces of data.

For example, below I have some data where one row has 4 pieces of data and the other row has 5 pieces of data. I would like to split that into 4 columns and 5 columns, respectively. As you can see, trying to use the formulas above would be impractical.

In Excel, first select the column you want to separate. Then, go ahead and click on the Data tab and then click on Text to Columns.

This will bring up the Text to Columns wizard. In step 1, you choose whether the field is delimited or fixed width. In our case, we’ll choose Delimited.

On the next screen, you will choose the delimiter. You can pick from tab, semicolon, comma, space or type a custom one in.

Finally, you choose the data format for the column. Normally, General will work just fine for most types of data. If you have something specific like dates, then choose that format.

Click Finish and watch how your data is magically separated into columns. As you can see, one row turned into five columns and the other one into four columns. The Text to Columns feature is very powerful and can make your life a lot easier.

If you are having problems separating names not in the format I have above, post a comment with your data and I’ll try to help. Enjoy!

Related posts

• เพิ่ม Regression Trendline แนวตรงไปยัง Excel Scatter Plot

• วิธีทำ Histogram ใน Excel

• วิธีที่จะปลอดภัย Password Protect Excel File

• วิธีสร้าง Flowchart ใน Word and Excel

• วิธีการ Create Labels ใน Word จาก Excel Spreadsheet

• วิธีการคำนวณ Variance ใน Excel

• ใช้ Excel เป็นเครื่องมือในการคัดลอกข้อมูลจากเว็บ

• วิธีการลบวันที่ใน Excel

• จัดรูปแบบเซลล์โดยใช้การจัดรูปแบบตามเงื่อนไขใน Excel

• แทรกแผ่นงาน Excel ลงใน Word Doc

• วิธีใช้ VLOOKUP ใน Excel

• วิธีซ่อนชีต เซลล์ คอลัมน์ และสูตรใน Excel

• วิธีการ Automatically Backup Word Document เพื่อวันไดรฟ์

• วิธีเปิด Excel หลายอินสแตนซ์

• วิธีค้นหาค่าที่ตรงกันใน Excel

• วิธีสลับระหว่างแผ่นงานใน Excel

• วิธีการสร้าง Greeting Card กับ MS Word

• วิธีการสร้าง Drop Down List ใน Excel

• วิธีใช้คุณลักษณะการกู้คืนอัตโนมัติของ Excel และการสำรองข้อมูลอัตโนมัติ

• วิธีใช้ Sparklines ใน Excel