OK, I have a confession to make. Last week when I showed the before and after examples, I “baited the hook” and intentionally left part of it unexplained. The part I did explain last week was how to extract a certain number of characters from a string from the left, right and middle. However, these only work if you always want the same number of characters. For example, when extracting the area code you always want the first three numbers.
What do you do when the length of what you want to extract varies? Give up? Manually re-type them? I hope not! All you need is a couple simple functions and little basic math. Here’s the before and after so you can see where we’re headed
How Its Done:
Grabbing the first name
Part 1:To grab the first name we first need to use the =FIND function to determine the location of the space. In our example, this tells us that the space is the 10th character in the cell.
=FIND(text to find,text to search,starting place)
Part 2: Once we know where the space is we simply use the =LEFT function to specify that we everything prior where the space is, or the first 9 characters. This gets a little bit more complex because we’re now nesting a function within a function
Grabbing the last name
Part 1: Getting the last name is slightly different and requires us to calculate how many total characters are in the cell and then simply subtract all other characters up to where the space is.
* Remember that in our example, we’ve already determined that the space is in the 10th spot using the =FIND() function
Part 2: To determine the total number of characters we’ll use the =LEN() function and nest one more formula to get this one.
When learning these functions it is probably easier to do each step in it’s own cell. Keeping track of those nested formulas can be very mind bending at times.
If your head hurts don’t worry. We’ll return to something MUCH more easy on the brain next week? Got anything you’re wrestling with? Click here to send me a note and let me know.