Formula that takes takes out a specific word/number from text string

**ncalvelo** · 11-29-2012, 08:56 AM

Hello all,

I need a hand creating a formula that will take the 2nd word/number/code in a text which has "-" seperating each word/number/code.

For example, in cell A1, there would be the text "EC12345-TASK01-TESTA"

I would then want cell B1 to *** out "TASK01" only

I originally used the "text to columns" menu option to separate the text and just delete the columns I didn't need. However, I've been told dumb down (i.e. remove extra steps) the sheet so that as soon as the original text is entered in one column, the 2nd word/number/code in each string is shown in the next column.

I've attached an example sheet with some dummy data. column A shows the text and column B should show the 2nd word/number/code (first row has been down as an example of the result expected).

P.S > I have Excel 2010

Regards

Nic

**benishiryo** · 11-29-2012, 09:00 AM

hi Nic, welcome to the forum. how about:
=TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",50)),51,50))

**Richard Buttrey** · 11-29-2012, 09:04 AM

Hi

Formula:

Please Login or Register  to view this content.

**ncalvelo** · 11-29-2012, 09:39 AM

Originally Posted by benishiryo

hi Nic, welcome to the forum. how about:
=TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",50)),51,50))

This worked perfectly, thanks!

I've tried breaking down the formula to understand how it works, but I am a bit confused. What I got so far is that you replaced a number of characters in the cell with spaces using SUBSTITUTE and MID so that you were only left with the desired set of characters that makes up the second word. then used TRIM to remove the spaces.

I don't understand how the MID and SUBSTITUTE part of the formula worked, any chance you (or someone) could explain that part of the formula?

Thanks again for the help

Nic

**benishiryo** · 11-29-2012, 09:55 AM

you can use the F9 key to highlight parts of the formula to see the results. for eg,
SUBSTITUTE(A2,"-",REPT(" ",50))
this portion will replace all "-" with 50 spaces & give you:
"EC12345 TASK01 TEST"

using the MID formula to retrieve the 51st character onwards & obtain 50 characters will return me a bunch of spaces & the texts in between:
" TASK01 "

the TRIM formula will then take care of all extra spaces

Edit: the post seemed to have disallowed my illustration of many spaces. so please try this formula by using the F9 key inside the formula