After I wrote about how to extract characters from the left, right or middle of a text string in Excel, I received a few inquiries about extracting text from strings which don’t seem to have a fixed size, and vary in length.
For example, if the data is something like this
You want to separate out the first word (HQ, LONDON, HOUSTON, SINGAPORE etc.)
Can’t Use LEFT Function
You can’t use a LEFT function, because a left function needs to know the number of characters you want to extract. And this itself is variable… There are 2 characters in HQ, 6 in London, 7 in Houston etc.
One of the ways I have discovered is to find the location of the first Hyphen (-), and extract all characters from the left of it.
Finding the location of the First Hyphen
We can use the FIND function to find the location of the first hyphen.
If A2 contains HQ-1022-PORT, we can use the formula as:
The answer would be 3. This means that the hyphen is the third character in the string. This is perfect. Now we know that we need n-1, that means 3-1, which is 2. We need 2 characters from the Left of this number.
So we can write another formula as
=LEFT(A2, FIND(“-“,A2)-1 )
The resulting answer would be HQ. Copy the formula down to other cells, and you should be able to extract HOUSTON, SINGAPORE etc. without much hassle, and without having to write another, different formula.
One size fits all formula to extract a variable length string from a piece of text. And pretty easily too!
Do you have any other method? How would you solve this challenge?
Please post your answers in the comments below.
P.S. – I am teaching a 2 day course on Data Interpretation & Analysis in Singapore on Feb 9-10, 2015, and another on March 9-10, 2015. If you would like to learn techniques to analyze data and create management reports, you can attend this program.
Awaiting you and your innovative comments below … 🙂 – Vinai
Are you facing any problem in using Excel? Any Question?
You have come to the right place. Tell us your needs. We’ll be glad to help you!