Hi everyone,
This is my first post but I've already benefitted greatly from various posts on here, so many thanks to all you more seasoned forum users!
I have a column that looks something like this:
A:20 B:50 C:30 D:15
A:52 E:19 L:6 P:20
C:42 F:65 G:7 N:90
etc., which constitutes a list of recipes for our products.
I need 16 separate columns, A to P, which contain only numbers. So, for example, the first row would look like:
A:20 B:50 C:30 D:15 | 20 | 50 | 30 | 15 | | | | | | | | | | | | |
where | is a cell separator.
Is there a way of automatically extracting these values? I realise it's in a horrible format, and up till now I've been doing it manually, but our latest spreadsheet needs 913 of these done, so it would be fantastic to have a nicer way of doing it.
Thanks in advance for your help
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!
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,
Re: Extracting text into multiple columns
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
Last edited by banana-pumpkin; 08-13-2010 at 05:48 AM. Reason: Omitted important information
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
Hi,
I screwed up the Dummy sheet
Without actually giving you the real sheet, which is strictly prohibited by my company, this is about the clearest I can do.
B to X are the pigment columns.
Pack size can be either 2.5L or 5L.
Base can be L, M or E.
I already have the Base and Pack size columns filled in, so it's only the pigment ones that I need help with.
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?
Manually it's easy, because the base and pack size is always the first information in the cell and the pigment information follows after. Also, all pack sizes are appended with "L" (i.e. litres), whereas pigment numbers are just numbers.
So you could never get e.g. G:10 M:20 M:2.5L, it would always appear as M:2.5L G:10 M:20 in the recipe cell.
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.
That is absolutely amazing! You have saved me hours of tedium and taught me some new formulae as well! Thank you so much
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks