Matching text at the end of a string

[bpiela]

In one cell, I have a string that looks like A_B_C, where A, B and C are strings. The string that makes up C can be one of 10 different strings. I would like to do some soft of comparison of what C is and then depending on its value, append a different string to the end of a different cell. Let me include an example. Suppose C=aa so that the total string in the cell would look like A_B_aa. In my next cell, since the first cell ends in "aa", I would like the next cell to end in "_endsinaa". I guess I would need to create a table somewhere matching the possible values of C with what text I would like to put in the next cell. I am not sure how to do something like this. I am also not an expert by any means. Any help is greatly appreciated.

 

[mjbeam]

I put the following in a worksheet:

A1: abc_def_aaa
B1: 123
C1: =CONCATENATE(B1,"_",VLOOKUP(MID(MID(A1,FIND("_",A1)+1,LEN(A1)),FIND("_",MID(A1,FIND("_",A1)+1,LEN(A1)))+1,LEN(A1)),I:J,2,FALSE))

I1: aaa
J1: endsinaaa

I2: bbb
J2: endsinbbb

I3: ccc
J3: endsinccc


The formula in cell C1 extracts the text after the last underscore character from cell A1, then does a VLOOKUP for that value (in this case 'aaa'), in the table in columns I:J and returns the matching value from column J. The CONCATENATE part of the function causes it to concatenate the value it finds (in this case 'endsinaaa') to the value in cell B1, so that cell C1 contains the text '123_endsinaaa'.


Hope this helps.

 

[bpiela]

This helps a great deal. If you don't mind, I would like to work out this formula so that I completely understand it.

I believe the good stuff here is this part of the formula:
MID(MID(A1,FIND("_",A1)+1,LEN(A1)),FIND("_",MID(A1,FIND("_",A1)+1,LEN(A1)))+1,LEN(A1))

First, this part is found twice in the formula:
FIND("_",A1)+1,LEN(A1)
This looks like it returns the location of the first character after the first "_" in cell A1, which is character location 5.

Next, you use the MID function to extract the substring on the contents in cell A1 starting at the 5th character to the end of the string, which is def_aaa.
MID(A1,FIND("_",A1)+1,LEN(A1))

Next, you use the FIND function on the string def_aaa to get the location of the first character after the next "_". This is 5.
FIND("_",MID(A1,FIND("_",A1)+1,LEN(A1)))+1

Now, I get confused. You do a MID the text string "def_aaa ", starting at character 5 for a length of 11 characters, so that should return "aaa" plus 8 spaces. I guess the extra spaces get dropped? Am I understanding this correctly? I believe I understand the CONCATENATE and VLOOKUP parts.

Thanks.

 

[mjbeam]

Put small sections of the formula in cells and see what they return. Then you can see what they are doing.

 

[Michael M]

Or look at the Evaluate function in Excel
Formulas Tab>>Formula Auditing>>Evaluate formula
This will allow you to step through the formual and see the result of each step

 

[bpiela]

Thanks very much for the suggestions and the assistance. Very much appreciated!