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!