Instructions for separating First and Last Name on Microsoft Excel

For those who have studied Microsoft Excel, you will find it extremely "magic" because it contains hundreds of calculation formulas to perform all the simple word procedures such as separating the name column, calculating the number of days, etc. to complex as the electric charge function . Excel itself can replace any accounting software if you know how to use it properly, it even does more than you think and Excel is also one of the great data management tools.

Excel spreadsheet tool gradually became unpopular among the office world, especially Accounting, but there are plenty of things without Excel that you cannot do. These may be very simple functions, but if you study them they will be very rewarding for your current job no matter what your industry. Below we will guide you how to separate first and last name in Excel extremely simple.

Instructions on how to separate first and last name in Excel

Method 1: Use Replace

Step 1:

If you are following our instructions, create a spreadsheet with 3 columns including First and Last, First and Last . Then enter some first and last names. If you already have a file with your full name, open it to continue to the second step below.

Step 2:

Copy the entire first and last names in the last name column and the newly created name to the column name. Here you can highlight or select the entire name just created then press Ctrl + H to open the Replace window .

Step 3:

In the Replace window , enter the following: The Find What box enters the * character and the space means to enter a space: * [space]. Look under the picture to see details. When finished importing, click Replace All.

Next window asking if you are sure you want to replace the number of characters above or not, click OK .

Right now the column name will only display the correct name of the first and last name list you have entered. The principle of operation of this formula is to take only the last letter so it will be completely accurate and you can not get the middle name if you have 2 words like: Hoang Anh, Quynh Hoa, etc.

We continue to separate the first part, which means that the rest do not include first and last names as follows:

Step 1:

In the first cell of their column, use the LEFT function.

Please enter the formula: = LEFT (A2, LEN (A2) -LEN (C2)).

Explain:

• Left function (the text to be cut from the left to the left, the number of characters to be cut).
• Len function (word): the function takes the length of the text.

This formula applies to our created spreadsheet, so you can review the address of your spreadsheet to replace the address according to the correct formula. After finishing typing enter you will get the desired results.

Step 2:

Click the mouse on the corner of the cell to which the result appears, adding a black plus sign to the entire column.

Method 2: Use the RIGHT function

Step 1: To separate a name from the First and Last column, do the following:

In cell C2 enter the following formula: = RIGHT ( A2 , LEN ( A2 ) -FIND ("@", SUBSTITUTE ( A2 , "", "@", LEN ( A2 ) -LEN (SUBSTITUTE ( A2 , "", " ")))))

Note: Replace the blue cells with the corresponding "First name" boxes in your Excel file.

Step 2: For the remaining cells in column C, just copy the formula by dragging down from cell C2. So you have completed the separation of names in Excel:

Step 3: Use the Text to Columns tool to combine the OFFSET function

Step 1: Copy the original list of first and last names to another column, suppose copy to column F, then select the entire first and last name of column F. Switch to Excel's Data Tab , select Text to Columns.

Step 2: The Convert Text to Columns Wizard window appears, select Next.

Step 3: Next, uncheck the box in Tab and check the box Space , then click Next . Leave the default parameters and click Finish.

Step 4: So you see all the words in the first name and the first name will be separated into each column (each word will enter a column). They will be on the same column, but Names will be on different columns. So you have the Last name, and the Name part needs to use the OFFSET function to get:

Copy column F data to the Last column. In column D and column C you enter in turn the following formula:

• Split Name: = OFFSET (F2,0, LEN (A2) -LEN (SUBSTITUTE (A2, "", "")))

• Split Middle Name: = TRIM (MID (A2, LEN (B2) + 2, LEN (A2) -LEN (B2) -LEN (D2) -1)))

Step 5: For the remaining cells in columns C and D, just copy the formula by dragging from cells C2 and D2 down. So, you have separate first name, middle name and last name.

Video tutorial on how to separate first and last name in Excel

Above we have instructed you how to separate the first and last name column quite simply, hope the above guide will help you quickly complete your work as well as have 1 more tricks in Excel to apply in public. job.

I wish you successful implementation.