Extracting text into multiple columns

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

teylyn, thanks for your quick help!

I've attached a dummy spreadsheet to better illustrate my point.

Our products are paints, and the recipe line lists first the base and pack size, and then all pigments required to be added. The base and pack size have been manually extracted, as shown. I've filled in red to give the result I want to achieve - I will then go on and use the values to calculate costs based on the different costs of different pigments.

As you can see, the numbers may be 1, 2 or 3 digits, but never more than 3.

Correction: Having manually scanned the spreadsheet, there are in fact some 4-digit values too. Also please note that "M" can be either a base or a pigment, which might further complicate the text searching :S

Will the values ever be only one or two digits? Then this solution may work

=IF(ISNUMBER(SEARCH(B$1&":",$A2)),MID($A2,FIND(B$1,$A2)+2,2)*1,"")

see it applied in the attached file.

If your numeric values can be from 1 to 3 or more digits, then we need to re-think.

cheers,

What's with "5L" in your data sample, cell B2? How should that be treated? Include the "L"? Chop it off and leave just the number?

Can you provide a data sample that lists all possible variations for column B and manually put in what the expected result would be in E to T?

Otherwise, we're pretty much left to guessing, which does not benefit either you or the willing (unpaid) helpers here.

cheers

How do you distinguish between

M:5L to go into Pack Size in column D

and

M:20 to go into the column for the M value?

OK, see what you can do with the attached.

the formula for column C, applied in row 9 is

=LEFT(B9,1)

for column D, applied in row 9 is

=MID(B9,3,FIND(" ",B9)-2)

for columns E to T

=IF($C9=E$1,"",IF(ISERROR(FIND(E$1&":",$B9)),"",MID($B9,FIND(E$1,$B9)+2,FIND(" ",$B9&" ",FIND(E$1,$B9)+2)-(FIND(E$1,$B9)+2))*1))

Will that work for you? See attached file, row 9ff.