{"id":276,"date":"2015-02-09T23:25:21","date_gmt":"2015-02-09T15:25:21","guid":{"rendered":"http:\/\/excelchamp.net\/?p=276"},"modified":"2022-07-11T06:27:40","modified_gmt":"2022-07-10T22:27:40","slug":"extracting-text-variable-length-strings","status":"publish","type":"post","link":"https:\/\/www.excelchamp.net\/extracting-text-variable-length-strings\/","title":{"rendered":"Extracting Text From Variable Length Strings"},"content":{"rendered":"

After I wrote about how to extract characters from the left, right or middle of a text string in Excel<\/a>, I received a few inquiries about extracting text from strings which don’t seem to have a fixed size, and vary in length.<\/p>\n

For example, if the data is something like this<\/p>\n

HQ-1022-PORT
\nLONDON-4053-LANDED
\nHOUSTON-2488-WEST
\nSINGAPORE-3133-LEEDON<\/p>\n

You want to separate out the first word (HQ, LONDON, HOUSTON, SINGAPORE etc.)<\/p>\n

Can’t Use LEFT Function<\/strong>
\nYou 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.<\/p>\n

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.
\n\t\t

\n\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

Want to Improve Your Excel Skills?<\/strong><\/span><\/p>

Learn the Key Features of Excel Quickly & Easily, by <\/span>Joining the
Online Training<\/strong> on <\/u>Basic \/ Intermediate Excel<\/u>.<\/strong><\/a><\/span><\/p>

To Get Most out of Excel, Learn the\u00a0 Pivot Table techniques in our
Pivot Table Masterclass Training<\/a><\/u><\/b>!<\/strong><\/a><\/span><\/p><\/div>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<\/div>\n\t\t
\nFinding the location of the First Hyphen
\n<\/strong>We can use the FIND function to find the location of the first hyphen.<\/p>\n

If A2 contains\u00a0HQ-1022-PORT, we can use the formula as:<\/p>\n

=FIND(“-“,A2)<\/strong><\/p>\n

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.<\/p>\n

So we can write another formula as<\/p>\n

=LEFT(A2, \u00a0FIND(“-“,A2)-1<\/em> )<\/strong><\/p>\n

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.<\/p>\n

One size fits all formula to extract a variable length string from a piece of text. And pretty easily too!<\/p>\n

Do you have any other method? How would you solve this challenge?<\/strong><\/p>\n

Please post your answers in the comments below.<\/p>\n

Cheers,
\nVinai Prakash<\/p>\n

P.S. – I am teaching a 2 day<\/em><\/strong> course on Data Interpretation & Analysis in Singapore<\/a><\/strong> on Feb 9-10, 2015<\/strong><\/em>, and another on March 9-10, 2015<\/em><\/strong>. If you would like to learn techniques to analyze data and create management reports, you can attend this program.<\/p>\n

Awaiting you and your innovative comments below … \ud83d\ude42 – Vinai
\n\t\t

\n\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t

Are you facing any problem in using Excel? Any Question?<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

You have come to the right place<\/span>. Tell us your needs. We’ll be glad to help you!<\/span><\/strong><\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

\n\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t\n\t\t\t\n\t\t\t\n\n\t\t\t\n\t\t\t
\n\t\t\t\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t