how to find string in a cell and display results

[djc225]

I'm working with product data and column a has product names and descriptions all lumped together. There is no pattern to the names or number of characters. How do i create a formula that looks in each column a cell for 1 of 5 character strings (looking for: "extra small", "small", "medium", "large", "extra large") and return in column b a set value determining which string is found ( if "extra small" return "XS", if "small" return "S", ...)

I'm comfortable working with number formulas but am completely lost when working with text formulas. Please help point me in the right direction.

thanks

[jraj1106]

Hello..
Upload a sample file

[jeffreybrown]

There are a few ways to handle this, but it depends on what you want for a setup...

One way...build a table and use =VLOOKUP(D1,$A$1:$B$5,2,0)

Please Login or Register  to view this content.

You could also use =LOOKUP(D1,A1:B5)

but notice the list is sorted A-Z

Please Login or Register  to view this content.

or you could imbed the values...

=LOOKUP(D1,{"Extra Large","XL";"Extra Small","XS";"Large","L";"Medium","M";"Small","S"})

[JBeaucaire]

This is a fun one. First, create a "table" that lists the strings to search in the first column and the values to return in the second. It's important that the LONGER strings be at the bottom of this table... so "small" would be near the top and "extra small" (a longer version of "small") is below it.

Let's create that table in columns I:J

Please Login or Register  to view this content.

Then, assuming the first string to find is in cell A1, this formula in B1:

=LOOKUP(2, 1/(ISNUMBER(SEARCH($I$1:$I$5, A1))), $J$1:$J$5)



EDIT: This is for pulling the "size" out of a longer text string, like The extra small shirt is blue. If the cell A1 really only holds the value "Small" or "extra small", then use the VLOOKUP suggested above.

[ChemistB]

Or, you could just use a lot of nested IF's like so. The "Extra" sizes need to be before the regular sizes

Please Login or Register  to view this content.

[djc225]

thanks everyone for the suggetions. the values were nested inside other content in the same cell so i used the solution from JBeaucaire, worked great. I didnt even think to set up a lookup table for this, was originally thinking along the lines of nested ifs, as the last poster suggested.

thanks again everyone.

[JBeaucaire]

If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.