Nếu bạn sử dụng Excel nhiều, có lẽ bạn đã gặp phải trường hợp bạn có tên trong một ô duy nhất và bạn cần phải tách tên thành các ô khác nhau. Đây là một vấn đề rất phổ biến trong Excel và bạn có thể thực hiện tìm kiếm trên Google và tải xuống^{(Google search and download)} 100 macro khác nhau được viết bởi nhiều người để làm điều đó cho bạn.

Tuy nhiên, trong bài đăng này, tôi sẽ chỉ cho bạn cách thiết lập một công thức để bạn có thể tự làm và thực sự hiểu điều gì đang xảy ra. Nếu bạn sử dụng Excel nhiều, có lẽ bạn nên tìm hiểu một số hàm nâng cao hơn để có thể làm được nhiều điều thú vị hơn với dữ liệu của mình.

Nếu bạn không thích công thức và muốn có một giải pháp nhanh hơn^{(quicker solution)} , hãy cuộn xuống phần Văn bản thành Cột^{(Text to Columns)} , phần này sẽ hướng dẫn bạn cách sử dụng một tính năng Excel^{(Excel feature)} để làm điều tương tự. Ngoài ra, tính năng văn bản thành cột cũng tốt hơn để sử dụng nếu bạn có nhiều hơn hai mục trong một ô mà bạn cần phân tách. Ví dụ, nếu một cột có 6 trường kết hợp với nhau, thì việc sử dụng các công thức dưới đây^{(formulas below)} sẽ trở nên thực sự lộn xộn và phức tạp.

Tên riêng biệt trong Excel

Để bắt đầu, hãy xem cách các tên thường được lưu trữ trong bảng tính Excel^{(Excel spreadsheet)} . Hai cách phổ biến nhất mà tôi đã thấy là họ chỉ ^(firstname) có^(lastname) dấu cách và họ^(lastname) , tên đầu tiên^(firstname) có dấu phẩy phân tách hai cách. Bất cứ khi nào tôi nhìn thấy chữ viết tắt ở giữa, đó thường là họ đầu tiên của chữ ^(firstname) đệm ^(midinitial) như^(lastname) dưới đây :

Sử dụng một số công thức đơn giản và kết hợp một số công thức với nhau, bạn có thể dễ dàng tách họ, tên và chữ đệm thành các ô riêng biệt trong Excel . Hãy bắt đầu với việc trích xuất phần đầu tiên của tên. Trong trường hợp của tôi, chúng tôi sẽ sử dụng hai chức năng: trái và tìm kiếm^{(left and search)} . Về mặt logic, đây là những gì chúng ta cần làm:

Tìm kiếm^(Search) văn bản trong ô cho một khoảng trắng hoặc dấu phẩy^{(space or comma)} , tìm vị trí và sau đó lấy ra tất cả các chữ cái ở bên trái của vị trí đó.

Đây là một công thức đơn giản để hoàn thành công việc một cách chính xác: =LEFT(NN, SEARCH(” “, NN) – 1) , trong đó NN là ô có tên được lưu trong đó. -1 là ở đó để loại bỏ khoảng trắng thừa hoặc dấu phẩy^{(space or comma)} ở cuối chuỗi.

Như bạn có thể thấy, chúng ta bắt đầu với hàm left^{(left function)} , hàm này nhận hai đối số: chuỗi và số ký tự bạn muốn lấy bắt đầu từ đầu chuỗi. Trong trường hợp đầu tiên, chúng tôi tìm kiếm một khoảng trắng bằng cách sử dụng dấu ngoặc kép và đặt một khoảng trắng ở giữa. Trong trường hợp thứ hai, chúng tôi đang tìm kiếm dấu phẩy thay vì dấu cách. Vậy kết quả cho 3 kịch bản tôi đã đề cập là gì?

Chúng tôi có tên đầu tiên từ hàng 3^{(row 3)} , họ từ hàng 5^{(row 5)} và tên đầu tiên từ hàng 7^{(row 7)} . Tuyệt quá! Vì vậy, tùy thuộc vào cách dữ liệu của bạn được lưu trữ, bây giờ bạn đã trích xuất tên hoặc họ. Bây giờ cho phần tiếp theo. Đây là những gì chúng ta cần làm một cách hợp lý bây giờ:

- Tìm kiếm^(Search) văn bản trong ô cho một khoảng trắng hoặc dấu phẩy^{(space or comma)} , tìm vị trí và sau đó trừ vị trí đó ra tổng chiều dài của chuỗi. Đây là công thức sẽ trông như thế nào:

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

Vì vậy, bây giờ chúng ta sử dụng đúng chức năng. Điều này cũng cần đến hai đối số: chuỗi và số ký tự bạn muốn lấy bắt đầu từ cuối chuỗi đi sang trái. Vì vậy, chúng tôi muốn độ dài của chuỗi trừ đi vị trí của khoảng trắng hoặc dấu phẩy^{(space or comma)} . Điều đó sẽ cung cấp cho chúng tôi mọi thứ ở bên phải của dấu cách đầu tiên hoặc dấu phẩy^{(space or comma)} .

Tuyệt vời^(Great) , bây giờ chúng ta có phần thứ hai của tên! Trong hai trường hợp đầu tiên, bạn đã hoàn thành khá nhiều việc, nhưng nếu có chữ viết tắt ở giữa trong tên, bạn có thể thấy rằng kết quả vẫn bao gồm họ với chữ viết tắt ở giữa. Vì vậy, làm thế nào để chúng ta chỉ cần lấy họ và loại bỏ chữ viết tắt ở giữa? Dễ! Chỉ cần^(Just) chạy lại cùng một công thức mà chúng ta đã sử dụng để lấy phần thứ hai của tên.

Vì vậy, chúng tôi chỉ thực hiện một quyền khác và lần này là áp dụng công thức trên ô họ và tên^{(name cell)} đệm kết hợp . Nó sẽ tìm khoảng trống sau ký tự đầu ở giữa và sau đó lấy chiều dài trừ đi vị trí của số^{(space number)} ký tự khoảng trắng ở cuối chuỗi.

Vì vậy, bạn có nó! Bây giờ bạn đã tách họ và tên thành các cột riêng biệt bằng cách sử dụng một vài công thức đơn giản trong Excel ! Rõ ràng, không phải ai cũng có định dạng văn bản theo cách này, nhưng bạn có thể dễ dàng chỉnh sửa nó cho phù hợp với nhu cầu của mình.

Văn bản thành cột

Ngoài ra còn có một cách dễ dàng khác để bạn có thể tách văn bản kết hợp thành các cột riêng biệt trong Excel . Đó là một tính năng được gọi là Văn bản thành Cột^{( Text to Columns)} và nó hoạt động rất tốt. Nó cũng hiệu quả hơn nhiều nếu bạn có một cột có nhiều hơn hai phần dữ liệu.

Ví dụ: bên dưới tôi có một số dữ liệu trong đó một hàng^{(one row)} có 4 phần dữ liệu và hàng còn lại có 5 phần dữ liệu. Tôi muốn chia nó thành 4 cột và 5 cột, tương ứng. Như bạn có thể thấy, cố gắng sử dụng các công thức trên sẽ không thực tế.

Trong Excel , trước tiên hãy chọn cột bạn muốn tách. Sau đó, hãy tiếp tục và nhấp vào tab Dữ liệu^(Data) và sau đó nhấp vào Văn bản thành Cột^{( Text to Columns)} .

Thao tác này sẽ hiển thị trình hướng dẫn Chuyển văn bản thành Cột^{(Columns wizard)} . Trong bước 1^{(step 1)} , bạn chọn trường được phân cách hoặc chiều rộng cố định. Trong trường hợp của chúng tôi, chúng tôi sẽ chọn Delimited .

Trên màn hình tiếp theo, bạn sẽ chọn dấu phân cách. Bạn có thể chọn từ tab, dấu chấm phẩy, dấu phẩy, dấu cách hoặc nhập một tùy chỉnh^{(custom one)} vào.

Cuối cùng, bạn chọn định dạng dữ liệu cho cột. Thông thường, General sẽ hoạt động tốt đối với hầu hết các loại dữ liệu. Nếu bạn có một cái gì đó cụ thể như ngày tháng, thì hãy chọn định dạng đó.

Nhấp vào Kết thúc^(Finish) và xem cách dữ liệu của bạn được phân tách thành các cột một cách kỳ diệu. Như bạn có thể thấy, một hàng biến thành năm cột và hàng kia thành bốn cột. Tính năng Text to Columns^{(Columns feature)} rất mạnh mẽ và có thể giúp cuộc sống của bạn dễ dàng hơn rất nhiều.

Nếu bạn đang gặp sự cố khi tách các tên không theo định dạng tôi nêu ở trên, hãy đăng nhận xét kèm theo dữ liệu của bạn và tôi sẽ cố gắng trợ giúp. Vui thích!

How to Separate First and Last Names in Excel

If you use Excel a lot, you have probаbly run across a situation where you have a name in a single cell and yoυ need to separate the namе intо different cells. This is a very cоmmon issue in Excel аnd you can probably do a Google sеarch and download 100 different macros written by varioυs people to do it for yoυ.

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

• Thêm một Regression Trendline tuyến tính đến một Excel Scatter Plot

• Cách tạo Histogram trong Excel

• Cách an toàn Password Protect An Excel File

• Cách tạo Flowchart trong Word and Excel

• Cách Create Labels trong Word từ Excel Spreadsheet

• Cách tạo Drop Down List trong Excel

• Cách trừ ngày trong Excel

• Cách so sánh hai tệp Excel và làm nổi bật sự khác biệt

• Liên kết các ô giữa Trang tính và Sổ làm việc trong Excel

• Định dạng ô bằng cách sử dụng định dạng có điều kiện trong Excel

• 40 phím tắt Microsoft Excel tốt nhất

• Cách chuyển đổi giữa các bảng tính trong Excel

• Cách sử dụng COUNTIFS, SUMIFS, AVERAGEIFS trong Excel

• Cách Group Worksheets trong Excel

• Cách tìm các giá trị phù hợp trong Excel

• Độ rộng cột tự động bổ sung và độ cao hàng trong Excel

• Làm thế nào để Quay Macro trong Excel

• Cách thêm nhận xét vào ô trang tính Excel

• Cách sử dụng hàm YEARFRAC trong Excel

• Cách sử dụng các tính năng AutoRecover và AutoBackup của Excel