WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Excel Split Name

Excel Split Name

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Split Name in Excel

It is quite common that we have full name values in cells but often times we need to split those into First Name, Last Name, and Middle Name. When the data is in excel, we can split names into different columns by using different ways. We have a variety of ways; some of them are quite complicated. In this article, we will show you how to split names in excel.

How to Separate Names in Excel?

We have different methods to split names in excel; we will see each method now in detail.

You can download this Split Name Excel Template here – Split Name Excel Template

#1 – Text to Column Method

We have below the full name list in excel.

Split Name Example 1

In the above data, we have FULL NAME of cricket players across different nations. We need to extract First Name & Last Name.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course
  • Select the FULL NAME data first.

Split Name Example 1-1

  • Now go to the DATA tab and click on the “Text to Column in Excel” option.

Split Name Example 1-2

  • This will open up “Text to Column Wizard.”

Split Name Example 1-3

  • Make sure “Delimited” is selected.

Split Name Example 1-4

Click on “Next” to go to the next step.

  • In the next step, we need to select the “Delimiter” type, i.e., in the FULL NAME, what is the delimiter character which is separating the first name and last name. In our data, the “space” character is separating names, so choose “Space” as the delimiter option.

Split Name Example 1-5

Click on Next, and it will go to Step 3.

  • In the next step, choose the cell where we need to store our First Name and Last Name.

Split Name Example 1-6

  • Now click on “Finish,” and we will have names in separate columns.

Split Name Example 1-7

Look in the case of row number 6 & 7, we have three names as “First Name, Last Name, and Middle Name,” so the third name is extracted to the extra column.

#2 – Formula Method

We can also separate names in excel based on formulas as well. We will use LEFT, RIGHT, LEN, and FIND methods.

  • Using the LEFT function in excel, we can extract characters from the left side of the FULL NAME. First, open the LEFT function for the B2 cells.

Formula Method 1

  • Text is nothing but from which text we need to extract values, so choose A2 cell.

Formula Method 1-1

  • Next, we need to mention how many characters we need to extract from the left side of the selected text. So in the name “Virat Kohli,” we need to extract 5 characters to extract the first name.

Formula Method 1-2

  • So this will give the first name as “Virat.”

Formula Method 1-3

For the next name also we have only 5 characters, but for the next names, we have different characters, so this is where supplying numbers manually to extract from the left side. So we need to use the “Find” function to find the first space character in the name.

  • Open function to understand how it works.

Formula Method 1-4

  • Find Text is the first argument, so we need to find space characters and enter the same.

Formula Method 1-5

  • In which cell we need to find the space character is Within Text, so choose A2 cell.

Formula Method 1-6

  • The last argument is Start Num, so the first space character we need to find, so enter 1.

Formula Method 1-7

  • So in the A2 cell first space character position is 6, so using this, we can find how many characters need to be extracted from the left side.

Formula Method 1-8

Note: We have provided -1 because FIND function excel returns the space character position, but we don’t need space character as our first name, so used -1 to extract less than one character supplied by the FIND function.
  • Now we need to extract the last name, which is from the RIGHT side, so open the RIGHT function in excel.

Formula Method 1-9

  • For the RIGHT function to we don’t know how many characters are to be extracted as the last name, so for this, also we need to use FIND & LEN in excel as supporting functions.

Formula Method 1-10

This time we have used LEN because the LEN function returns how many characters are there in the entire text, and FIND will find the space character, so from the overall number of characters, we need to ignore space positon and after space, we need to extract the last name.

Note: if there is the middle name, it will extract middle and last name as last name only.

Things to Remember

  • Middle Name extraction is complicated using formula.
  • FIND will find the supplied character position in the supplied text.
  • LEN will return the number of characters in the supplied text value.

Recommended Articles

This has been a guide to Excel Split Name. Here we learn how to separate names in excel using two methods 1) text to column method and 2) formula method with detailed examples and downloadable excel templates. You may learn more about excel from the following articles –

  • How to Split Cells in Excel?
  • Divide a Cell in Excel
  • Split Function in VBA
  • Split String into Array using VBA
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Download Coursera IPO Financial Model

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Split Name Excel Template

Coursera IPO Financial Model & Valuation Free Download