#160: Excel String Functions: The Sequel

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

=LEFT(A3,(FIND(” “,A3)-1))

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.

=RIGHT(A3,(LEN(A3)-(FIND(” “,A3))))


Helpful Hint:
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.

Here’s a sample copy you can download and explore further if you like.

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.

Advertisements

One thought on “#160: Excel String Functions: The Sequel

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s