If you are not familiar with Power Query, it is a tool that facilitates the extraction and transformation of data in Excel. You can simply add the steps discussed in this section to your Power Query data cleaning process and avoid having to repeat it every time you work with names in your data. This method can be a little more complex and lengthier compared to the other methods discussed so far, but you will find it helpful in the longer run, especially if you use Power Query as part of your data processing toolkit. Copy the range of cells containing the list of names and paste them as they are in the two columns (for First Name and Last Name) as shown below:Īlso read: Switch First and Last Name with Comma in Excel (Flip Names) Method 4: Using Power Query to Separate Names in Excel.To use the Find and Replace feature to separate names in Excel, follow the steps below: ![]() You cannot use it to extract the middle names. Unfortunately, this method can only help you extract first and last names. When you combine the asterisk wildcard with the Find and Replace feature, you can extract all characters that follow or precede a delimiter, like a space character or comma. So, if you want to look for all names that start with a ‘B’, you can use ‘B*’ as the search string. For example, the asterisk symbol (*) is a wildcard that represents any number of characters in a string. The best thing about it is that it lets you use wildcards to target specific patterns in your data.Ī wildcard is a symbol that represents a set of characters. Also read: Remove Middle Name from Full Name in Excel Method 3: Using Find and Replace (using wildcard characters)Įxcel’s Find and Replace feature is a versatile one since it lets you do a lot more than just find and replace things. There could be some instances where Flash Fill is not able to recognize the pattern and may give you the wrong result. ![]() Note: Flash Fill works well in most cases, but make sure to scan the result and ensure everything looks good. This is because Flash Fill can identify the pattern and can recognize that you’re trying to extract the first word of the name or the last word of the name. Unlike the Text to Columns method, the Flash Fill method to split names in Excel would work fine even when you have a mix of names (with and without a middle name).
0 Comments
Leave a Reply. |