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)
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:
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))
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.
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!
Please comment the function which you would want to learn and explore… I shall get the same ready for your perusal…