Change Last Comma To First Name In Excel

Many people struggle with rearranging names in Excel, especially when they are listed in a single cell as "Last Name, First Name". Fortunately, there are a few methods to change the format to "First Name Last Name" in Excel.

In this article, we will explore the different techniques to achieve this, including using formulas, text functions, and Power Query. Whether you are a beginner or an advanced Excel user, you will find a method that suits your needs.

Method 1: Using the TEXT TO COLUMNS Feature

One of the easiest ways to separate the names is by using the Text to Columns feature in Excel. This method is particularly useful when you have a large dataset and want to avoid using formulas.

To use this feature, follow these steps:

1. Select the cells containing the names.
2. Go to the Data tab in the ribbon.
3. Click on the Text to Columns button.
4. In the Convert Text to Columns Wizard, select the Delimited Text option and click Next.
5. In the next step, select the Comma checkbox and click Finish.

This will separate the names into two columns, with the first name in one column and the last name in another. You can then rearrange the columns to get the desired format.

Method 2: Using the LEFT and RIGHT Functions

If you prefer using formulas, you can use the LEFT and RIGHT functions to extract the first and last names from the original cell.

Here is an example formula:

=RIGHT(A1,LEN(A1)-FIND(",",A1)-1) & " " & LEFT(A1,FIND(",",A1)-1)

This formula assumes that the names are in cell A1. Here's how it works:

1. The RIGHT function extracts the first name by finding the position of the comma and then taking the remaining characters to the right.
2. The LEN function calculates the length of the original string.
3. The FIND function locates the position of the comma.
4. The LEFT function extracts the last name by taking the characters to the left of the comma.
5. The ampersand (&) symbol is used to concatenate the first and last names with a space in between.

Method 3: Using Power Query

If you are using Excel 2010 or later, you can use Power Query to change the format of the names. Power Query is a powerful data manipulation tool that allows you to transform and clean your data with ease.

To use Power Query, follow these steps:

1. Select the cells containing the names.
2. Go to the Data tab in the ribbon.
3. Click on the From Table/Range button.
4. In the Power Query Editor, select the column containing the names.
5. Click on the Split Column button and select the Delimited Text option.
6. In the next step, select the Comma checkbox and click OK.
7. Drag the new columns to the desired position to rearrange the names.

Tips and Variations

Here are some additional tips and variations to keep in mind:

• If you have multiple commas in your data, you may need to adjust the formulas or Power Query steps accordingly.
• You can use the TRIM function to remove any leading or trailing spaces from the names.
• If you want to keep the original format but add a new column with the rearranged names, you can use the formulas or Power Query steps to create a new column.
• You can also use the Flash Fill feature in Excel 2013 and later to automatically fill in the rearranged names.

Gallery of Related Images

Related Images

We hope this article has helped you learn how to change the format of names in Excel from "Last Name, First Name" to "First Name Last Name". Whether you use formulas, text functions, or Power Query, you can easily rearrange the names to suit your needs.