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 undersco

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!

On Fri, 8 Jun 2012 17:11:32 +0000, bpiela wrote:


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 undersco

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,FAL SE)

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

On Friday, June 8, 2012 12:11:32 PM UTC-5, bpiela wrote:
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 undersco

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!




--
bpiela

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