Populating cell with string not in workbook

[bpiela]

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 th
right of the underscore is always going to be one of the three string
listed. How can I auto populate the next column with other tex
depending on what follows the underscore? For instance, I might want t
auto populate the next column with the following strings that wil
always match the appropriate text after the underscore:

car
plane
train

"car" would always match "Drive", "plane" would always match "Fly", etc
Do I have to have that defined in the worksheet somewhere? Or can I d
some kind of IF statement?

Thanks

 

[Ron Rosenfeld]

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

"car" would always match "Drive", "plane" would always match "Fly", etc.
Do I have to have that defined in the worksheet somewhere? Or can I do
some kind of IF statement?

Thanks!

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

 

[Don Guillett]

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

"car" would always match "Drive", "plane" would always match "Fly", etc.
Do I have to have that defined in the worksheet somewhere? Or can I do
some kind of IF statement?

Thanks!

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