Use if on variable text

QuestionEdit

You seemed like a good resource for this question because you are versed both informulas and VBA and I think the answer to my question might need require a VBA solution.

I have two columns of data that appears as follows:

   A        |   B

R01 Base 2 | 24 R01 Height | 32 R02 Base | 28 R02 Height | 19 R03 Length | 5 R03 Width | 23 R04 Base | 7 R04 Width | 74 R05 Base 1 | 3 R05 Base 2 | 6 R05 Height | 19

This is just an excerpt of the data that I'm trying to perform a seemingly simple "IF" function to determine the geometric shape so that I can then calculate the area of the shape. So any value in Column A that ends in "Base 1" is a trapezoid, "Base" is triangle, and "Length" is a rectangle. These values are and the values in Column B are generated by another application which will directly export the data into Excel. The challenge is that there are many, many rows of this data and for me to go through and identify each shape takes forever and is prone to error. Also, the first part of the text string in Column A increments for each shape listed and therefore will be different for each individual geometric object exported into excel. For example "R01 Base 1" will only appear once in the output, but the format will always be the same:

Upper case "R" followed by 2 digits then a space, then either "Base 1", "Base 2", "Base", "Height", "Length" or "Width".

All I need to locate is "Base 1", "Base", and "Length" and I've been trying to figure out a way to do this using nested "IF" statements, but I don't know how to enter in the first part of the text string as 4 characters that should be ignored.

I know from what limited programming experience I've had in the past that something like "R01 Base 1" could be represented as a wildcard string that includes the variable text that would look something like:

????Base_1

Which would mean: any four characters followed by the specific characters "B" "a" "s" "e" <SPACE> "1". However I am not a programmer and although somewhat proficient at formulas, I don't know how to do something of this nature that not something that would be available in theHelp Screens.

Tim

AnswerEdit

For text values in col A, you want to identify those that end "Base 1" as trapezoid, those that end "Base" as triangle, and those that end "Length" as rectangle.

In a cell in a different col, in row 1, enter

=IF(RIGHT(A1,6)="Base 1","trapezoid",IF(RIGHT(A1,4)="Base","triangle",IF(RIGHT(A1,6)="Length","rectangle","")))

and copy down the column.