How to Split Names Using Formula in Excel (4 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

In Microsoft Excel, there must be some cases when you need to split the full name. Excel has several methods to do this. It actually reduces your task because you can do it manually but that is very time-consuming and difficult to concentrate on. This article will show you all the possible methods to split names in Excel using formula. I hope you enjoy this article and gather more knowledge about Excel.


How to Split Names Using Formula in Excel: 4 Easy Methods

We can split names either using formula or using Excel built-in command. In this article, we cover all the possible methods to split names in Excel. One biggest advantages of using formulas for solving any problem is that formulas give dynamic solutions whereas Excel’s other commands give static solutions. To show all the methods to split names in Excel, we take a dataset that includes the full names of some persons. We need to split names into first names, middle names, and last names.


1. Extract First Name

To extract the first name from the full name, we use the LEFT and SEARCH functions.

Steps

  • Select cell C5 where you want to apply the formula to extract the first name.

Split names in Excel Using Formula

  • Write down the following formula in the formula bar.
=LEFT(B5,SEARCH(" ",B5)-1)

  • Press Enter to apply the formula.

  • Drag the Fill Handle icon down the column or double-click on it.

Split names in Excel Using Formula

Breakdown of the Formula

  • SEARCH(” “,B5)-1): Here, the SEARCH function is used to get the blank in cell B5, and -1 used to get text 1 step before the space.
  • LEFT(B5,SEARCH(” “,B5)-1): The LEFT function starts text from the left side and finishes it 1 step before the space.

2. Extract Middle Name

To extract the middle name, we use the combination of IFERROR, MID, and SEARCH functions.

Steps

  • First, select any cell. Here, we select cell D5.

Split names in Excel Using Formula

  • Write down the following formula in the formula bar:
=MID(B5,SEARCH(" ",B5)+1,SEARCH(" ",B5,SEARCH(" ",B5)+1)-SEARCH(" ",B5)-1)

Split names in Excel Using Formula

  • Press Enter to apply the formula.

  • Drag the Fill Handle icon down the column or double-click on it. You may find some results displayed as ‘#VALUE!’. This happens because two of our given full names has actually no middle names.

Split names in Excel Using Formula

  • To solve this error and display the middle name as blank when there is no middle name, we can use the IFERROR function.
  • Replace the previous formula with the following formula:
=IFERROR(MID(B5,SEARCH(" ",B5)+1,SEARCH(" ",B5,SEARCH(" ",B5)+1)-SEARCH(" ",B5)-1),"")
  • Now, drag the Fill Handle icon down the column and you will get the result as blank when there is no middle name.

Split names in Excel Using Formula

Breakdown of the Formula

  • MID(B5,SEARCH(” “,B5)+1,SEARCH(” “,B5,SEARCH(” “,B5)+1)-SEARCH(” “,B5)-1): To get the middle name from the full name, use SEARCH(” “,B5)+1 to extract the name from the next character and place it in the Start_num argument of the MID function. To tell how many characters to extract, you need to subtract the position of the 1st space from the 2nd space position and finally put it in the num_chars argument of the MID function.
  • And if the middle name is missing, the IFERROR function will replace them with an empty string.

3. Extract Last Name

To Extract the last name from the full name, we utilized the RIGHT, SEARCH, SUBSTITUTE, and LEN functions.

 Steps

  • Select cell E5.

  • Write the following formula in the formula bar.
=RIGHT(B5,LEN(B5)-SEARCH("#",SUBSTITUTE(B5," ","#",LEN(B5)-LEN(SUBSTITUTE(B5," ","")))))

  • Press Enter to apply the formula.

Split names in Excel Using Formula

  • Drag the Fill Handle icon down the column or double-click on it.

Breakdown of the Formula

  • LEN(SUBSTITUTE(B5,” “,””)): Here, the SUBSTITUTE function uses to eliminate blanks in the full name and count the length using the LEN function without a space.
  • LEN(B5)-LEN(SUBSTITUTE(B5,” “,””)): This provides the length of space in the dataset.
  • SUBSTITUTE(B5,” “,”#”,LEN(B5)-LEN(SUBSTITUTE(B5,” “,””))): The SUBSTITUTE function uses here to substitute space with ‘#’.The instance number in the SUBSTITUTE function denotes the position of your instance. Here, we have 2 spaces and instance number 2 denotes 2nd instance. It replaces the space in the second instance.
  • LEN(B5)-SEARCH(“#”,SUBSTITUTE(B5,” “,”#”,LEN(B5)-LEN(SUBSTITUTE(B5,” “,””)))): The SEARCH function will search the  ‘#’ and return the length value where the  ‘#’ appears. Then delete it from the total length of your full name. In our example, it returns 4. This is the length remaining after  ‘#’.
  • RIGHT(B5,LEN(B5)-SEARCH(“#”,SUBSTITUTE(B5,” “,”#”,LEN(B5)-LEN(SUBSTITUTE(B5,” “,””))))): Now, the RIGHT function will extract the return length text from cell B5 which eventually provides the last name of the given full name.

3 Alternative Ways to Split Names without Using Formula in Excel

1. Utilizing Text to Columns to Split Names

Secondly, you can easily split names in Excel using the Text to Columns command.

Steps

  • First, select the entire column where you put full names.

  • Now, go to the Data tab in the ribbon and select Text to Columns from the Data Tools group.

  • Select Delimited from Original data Type and click on Next.

Split Names in Excel Using Text to Columns

  • Select Space from Delimiters and click on Next.

  • Now, change the Destination where you want to put your results and click on Finish.

Split Names in Excel Using Text to Columns

  • This will split the names and places them into two different columns.

Read More: Separate First and Last Name with Space Using Excel Formula


2. Using Find and Replace Command in Excel

Another easy way to split names is by using Find and Replace command in Excel. We will use it in the upcoming sections.


2.1 Split First Name

Steps

  • Copy column B and paste it into column C where you want to extract the first name from the full name.

  • Select column C.

  • Now, go to the Home tab in the ribbon and click on Find & Select from the Editing group.

Split Names in Excel Using Find & Replace

  • Select Replace from Find & Select drop-down menu.

  • A Find and Replace window will pop up. Place ‘ *’ (put a space then insert an asterisk sign) in the Find what field. This is a wildcard character(*) that can be used in place of any text. Here place space and then a wildcard character. Leave Replace with field as blank. Click on Replace All.

Split Names in Excel Using Find & Replace

  • This will eventually replace all the text after space with blank and just return the first name from the full name.

Read More: How to Separate First Name Middle Name and Last Name in Excel Using Formula


2.2 Split Last Name

Steps

  • Copy column B and paste it into column D where you want to extract the last name from the full name.

  • Select column D.

  • Go to the Home tab in the ribbon and select Find & Select from the Editing group.

  • Select Replace from Find & Select option.

  • A Find and Replace window will pop up. Place ‘* ’ (put an asterisk sign first then insert space) in the Find what field. This is a wildcard character that can be used in place of any text. Here place a wildcard character(*) and then put space. Leave Replace with as blank. Click on Replace All.

Split Names in Excel Using Find & Replace

  • It will replace all the text up to the space and place it as blank. It returns the last name from the full name.

Split Names in Excel Using Find & Replace

Read More: How to Split Names in Excel into Two Columns


3. Split Names Through Flash Fill in Excel

Finally, our last method is based on Flash Fill where you need to put your first or last name correctly then all other rows can be modified using Flash Fill.

Steps

  • Write down the first name and last name of your given full name in cell C5 and cell D5 respectively.

Split Names Using Flash Fill

  • Drag the Fill handle icon down the column for both cases.

  • It will give the same value in all cells. Now, click on the Auto Fill Options and select Flash Fill.

Split Names Using Flash Fill

  • Do it for both cases then you will get the required first and last name from the full names.

Read More: Excel VBA: Split First Name and Last Name


Download Practice Workbook


Conclusion

We have discussed four different methods to split names in Excel. The formula gives us a dynamic solution whereas the other 3 methods give us static solutions. However all the methods are equally effective in splitting names. I hope you enjoy the whole article and if you have any questions, feel free to ask in the comment box.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Durjoy Paul
Durjoy Paul

Durjoy Kumar, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, is a dedicated contributor to the ExcelDemy project. His substantial contributions include crafting numerous articles and demonstrating expertise in Excel and VBA. Durjoy adeptly automates Excel challenges using VBA macros, offering valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, SPSS, C, C++, C#, JavaScript, Python Web Scraping, Data Entry... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo