EXCEL HELP: Extracting Text

[richkoi]

I wrote a huge formula using the left(), mid() and possibly right() functions to extract item codes. It was an experimental file and I have lost it!

I have a table with species names... I would like to create an item code that consists of the first two letters of the GENUS, first to letters of the SPECIES (text for this is in ONE cell) and the ## from size. See example below. Bold is the formula I want to create to extract the text.

Genus species, Size, CODE
Bambusa multiplex, 15g, BAMU15
Bambusa oldhamii, 05g, BAOH05

Can anyone help me come up with a formula to extract this information?

Thanks,

P.S. Notice how the code is in all CAPS...is there a way to integrate that into the formula? I believe it is the UPPER() formula...

[steve0]

I understand the first example.. bit a little confused on the second.. where is the 'OH' coming from..?

Try this quickie hack.. assuming your data starts in the first column, first cell A1, and goes down..

=UPPER(LEFT(A1,2)&MID(A1,SEARCH(" ",A1,3)+1,2)&MID(A1,SEARCH(", ",A1,3)+2,2))

explained:
Get the first 2 characters from the left..
Find the position of the first space, and grab the next two characters.. and append..
Find the comma followed by a space, and grab the next two characters.. and append...

Lather, rinse, repeat..

[richkoi]

BAmbusa OLdhamii

That is where the BAOH comes from.

Thanks, I'll try your formula now.

Rich

[richkoi]

ooops...I guess we have been using the wrong item code for that plant all along. It should be BAOL

THANKS!

Rich

[richkoi]

...your formula gave me an error. I see that the entire formula points to A1, where it should point to A1 and A2. See example below:

http://www.bambooheadquarters.com/temp/excel.gif

Let me know if you can modify the formula to produce the above. Thanks!

Rich

[steve0]

Hi Rich,

I thought ALL the text was in one cell (e.g. A1)..
if the number is in the next cell (B1)..

Go with:

=UPPER(LEFT(A1,2)&MID(A1,SEARCH(" ",A1,3)+1,2)&LEFT(B1,2))

-Steve

[richkoi]

Great! Thanks a lot, works like a charm.

Rich