Excel - Populating cell with string not in workbook

Lets say I have a column of text that looks like the following:

Test_Drive
Testing_Fly
TestTest_Ride

The text to the left of the underscore can be anything.  The text to the
right of the underscore is always going to be one of the three strings
listed.  How can I auto populate the next column with other text
depending on what follows the underscore?  For instance, I might want to
auto populate the next column with the following strings that will
always match the appropriate text after the underscore:

car
plane
train

Do I have to have that defined in the worksheet somewhere?  Or can I do
some kind of IF statement?

Thanks!




--
bpiela

You could use IF statements, but it can be clumsy and difficult to support/alter/add to/etc.

Better would be a lookup table.

Given what you have written, you could set up a table like:

drive	car
fly	plane
ride	bus
etc.

Assume that table is in J1:K3

Assume your column data starts in A1.

You could then use the formula:

B1:	=VLOOKUP(MID(A1,FIND("_",A1)+1,99),$J$1:$K$3,2,FALSE)

and fill down as needed.  Note that the 99 just needs to be some number longer than the longest total string length of a string in column A

Or you could use the same idea with CHOOSE if you only have a few such as 3