How to get part of a text string even when that part's position in the string varies.

Sometimes it can be useful to get only part of a text string. That is what the MID function is for. But MID alone is not enough if the part of the string you want isn't always in the same place. For example, you may want to separate a list of full names into separate first and last names. But using the SEARCH, SUBSTITUTE, and/or LEN functions with the MID function can make it much more versitile, as I will show below.

1. The MID function takes part of a text string based on the starting character (parameter 2) and the number of characters (parameter 3). To grab only the first word in A1, you can use the SEARCH function to tell the MID function the position of the first space, then subtract 1 so that it takes the correct number of characters.

First Word

 

2. To get the second word, you have to use the SUBSTITUTE function in conjunction with the SEARCH function. The substitute function replaces a certain character that you specify with another function that you specify. In this case I used the @ symbol because it wasn't used anywhere else, and I specifically told it to replace only the 2nd space. This way I was able to SEARCH for the @ symbol and calculate how many characters to specify for the MID function. And I used the SEARCH function again to find the position of the 1st space.

Second Word

 

3. To find the last word, you need to use the LEN function, which tells you the total number of characters in the text string. I also had to use the SEARCH and SUBSTITUTE combination twice to find the position of the 2nd space.

Last Word

 

The advantage of this is that you could, for example, use this to transform a list of thousands of names in the form [first] [last] into the form [last], [first] with one formula copied down through the list. So even though the formulas get a bit messy to read, this technique can be a huge time saver.

 

 

Back to Resources page.