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:
Proper: Sentence case
Upper: All Caps
Lower: All Small
Alt+H+FS: Font Size
Alt+H+FF: Font Style
Alt+H, AT/AM/AB: Top Align | Middle Align | Bottom Align
Alt+H,AL/AC/AR: Left | Center | Right
Alt+H+B: Border Setting
Alt+H+H: Fill Color
Alt+H+FC: Font Color
Alt+O,C,A: AutoFit Column Width
Alt+O, H, R: Rename Worksheet
The above will surely help you save a couple of mins to tidy up your work.
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)
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,” “,””)))))
Using the combination of Right and Substitute we will be able to get the Last name. There are 3 parts to this formula:
RIGHT(A3, LEN(A3)-FIND(“#”: Here, the excel will come to the right of the cell and identify the “#” character in the string.
SUBSTITUTE(A3,” “,”#”: Here, the first blank space form right will be replaced with “#”.
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))
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.
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.
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!