top of page
• Canute Fernandes

# Split names in Excel

How comfortable are you with Excel?

Do you struggle with the basics or you manage to scrape through…!

I will share a function that will help you with your data analysis, a small function but a powerful one…

We often download reports and start working on them, now the catch here is the most required or necessary column is the “Name”, right?

I am sure you agree… But not every system will give you the desired output unless the report had been developed as per your primary requirement… Even if it were developed many-a-times due to change in the process the output is not in the desired format….

We will check how can we split the name with a formula… You must have been using a Tex-to-column function or doing it manually if there were few records…

Before we see the function of splitting the name, I will share with you a few formatting shortcuts:

1. Proper: Sentence case

2. Upper: All Caps

3. Lower: All Small

4. Alt+H+FS: Font Size

5. Alt+H+FF: Font Style

6. Alt+H, AT/AM/AB: Top Align | Middle Align | Bottom Align

7. Alt+H,AL/AC/AR: Left | Center | Right

8. Alt+H+B: Border Setting

9. Alt+H+H: Fill Color

10. Alt+H+FC: Font Color

11. Alt+O,C,A: AutoFit Column Width

12. Alt+O, H, R: Rename Worksheet

Now, let’s see the Split Function. No, don’t look up for a split function in a literal sense this is a combination of formulas…

Left, Right, and Substitute

### I am wanting to split my name i.e. Canute Vernon Fernandes.

First Name: =LEFT(A3,FIND(” “,A3,1)-1)

Output: Canut“e”

Explanation:

We command the Excel to start from the left and find the first blank space (” “) which will be the last character and subtract the blank space by inserting -1.

**If you do not insert -1 it will include the space after “e” and give an output of 7 characters.

Last Name: =RIGHT(A3,LEN(A3)-FIND(“#”,SUBSTITUTE(A3,” “,”#”,LEN(A3)-LEN(SUBSTITUTE(A3,” “,””)))))

Output: Fernandes

Explanation:

Using the combination of Right and Substitute we will be able to get the Last name. There are 3 parts to this formula:

1. RIGHT(A3, LEN(A3)-FIND(“#”: Here, the excel will come to the right of the cell and identify the “#” character in the string.

2. SUBSTITUTE(A3,” “,”#”: Here, the first blank space form right will be replaced with “#”.

3. LEN(A3)-LEN(SUBSTITUTE(A3,” “,””)))))Here, it will give the output, which will be considered until the first blank space, now replaced with a “#” subtracted from the output.

Middle Name: =LEFT(RIGHT(A3,LEN(A3)-FIND(” “,A3,1)),FIND(” “,RIGHT(A3,LEN(A3)-FIND(” “,A3,1)),1))

Output: Vernon

Explanation:

We will use the Left & Right function to arrive at the text that is in the middle of the name. This function has 2 parts to it.

1. LEFT(RIGHT(A3, LEN(A3)-FIND(” “, A3,1)): Here, excel will start the command from the left but will consider only the data to the right of the first blank space from the left.

2. FIND(” “, RIGHT(A3, LEN(A3)-FIND(” “, A3,1)),1)): Here, the excel will start the command from the right but will consider only the data to the left of the first blank space from the right.

Try this by yourself!

Please comment the function which you would want to learn and explore… I shall get the same ready for your perusal…