Strings and things
Think spreadsheets and you normally think numbers. However they are good at many processing tasks including text processing. Here we use a simple example from by Sumit Bansal in trumpexcel.com, extracting the first word from a cell. The article shows a number of ways of extracting the first word, but only one of them uses a formula. Other approaches involve processing the cells operationally.
The value of using a formula is that the process involved is embodied in the language of formula. That means that if it is not doing what you wanted you can update the formula. For example, the formula explicitly states what happens when there is no first word to extract.
The first word
So the formula described by Sumit is =IFERROR(LEFT(A1,FIND(” “,A1)-1),A1)
This involves:
- FIND(” “,A1) – that finds the position of the first space in A1
- Reduce that value by 1, so it will be the position just before the space … FIND(” “,A1)-1
- Get just the left part of A1 up to that position … LEFT(A1,FIND(” “,A1)-1)
- This gives you the first word, however you have to consider what happens if there is no space at all. That will mean an error is produced. Sumit protects against that by putting it all in an IFERROR statement. The final argument of IFERROR is A1. That means if the find and left do produce an error, A1 will be given as the answer (and the error will not be seen).
To help see this in action we’ve a short video showing it working with the EQUS add-in. The add-in shows how the separate steps fit together to get the job done.
And the next word
Along with that we also show a second formula working. This one picks what is left after the first word.
=IFERROR(RIGHT(A1,LEN(A1)-FIND(” “,A1)),””)
See if you can see what’s going on.