Latest posts by techwriter (see all)
- INFOGRAPHICS – Which Business Entity is Right for You? - September 28, 2016
- 3 Ways to Add Copyright Free Images to Your Blogs, Books and Documents - September 19, 2016
- How to Delete All Hyperlinks in a MS #Word Document through VBA Macro - September 1, 2016
© Ugur Akinci
Imagine you have a long list of first and last names, both of them saved in the same cell.
Your client has asked you to extract the first and last names and list them in adjacent columns. How would you do that easily and quickly, with a single formula?
Step 1: Extracting the First Name
Select cell C2 and where we would like MS Excel to accomplish the following:
“Find the first SPACE in the combined name, select and extract all the letters to the LEFT of it and display it here.”
For that we need to use the LEFT function.
But how would Excel know where the SPACE is? For that, we use the FIND function. Thus our finished compound function would have both the LEFT and FIND functions in it.
Here it is:
In plain language, this is what this formula says:
“Go to cell B2. Find the space. Then take all the text to the left of that space and display it.”
The result is good:
Step 2: Extracting the Last Name
CAUTION: this step is tricky
So how do we extract the last name and display it in cell D2? By using a similar formula, but one made by the RIGHT function, right?
No. You can’t just copy the first formula and replace “LEFT” with “RIGHT.” It won’t work. Because if you do that you’ll get the first letter of the last name chopped off:
QUESTION: Why does this mishap happen?
ANSWER: The FIND function searches only from left to right.
(1) To compensate for that built-in error, we will use the LEN function to first find the LENGTH of a text string.
=LEN(B2) finds the length of the string in B2, which in this case is 13 including the SPACE.
But we don’t want the whole length. We want the length of only the last name. Thus we need to SUBTRACT something from this length. And that “something” is found by the FIND function.
Yet before we can even use the FIND function, we need to substitute the SPACE in the name with a character, let’s say with the percentage character “%”.
(2) And for that, we use SUBSTITUTE function:
This says “replace all spaces in cell B2 string with “%” character“.
(3) Now we can use FIND to find the “%” character:
(4) This is what we will subtract from the overall length of the string:
This will give is the correct length of the last name.
(5) Now all we have to do is read that many characters from the RIGHT of the string and display them:
This gives us the correct last name:
To apply these formulas to the rest of the names, just double-click their respective BLACK SQUARES or drag the formulas down their respective columns: