Advanced formula in Excel / Adding text to a cell based on another cell-CodePudding

I am currently working on a project where I need to add a phrase to one cell based on a character referenced in another cell. For example:

As you can see, cell C1782 had "-pr" at the end and in cell E1782 the phrase "Platinum Reserve" was added mid text.

I am looking to automate this process but I cant seem to find a way to write this out in a formula.

I believe I will have to assign variables, as "PR"/"Platinum reserve" is only one example of added text that had to be done. I have about 5 other ones to assign, such as "-s" which would become "Silver"

I have a list with about 10.4k entries, so you can see why I want to build some kind of automation.

CodePudding user response：

You can use a combination of formulas to achieve this. I would leave column-E alone and add another column. This works assuming you want only one prefix added, which is based on the LAST prefix from column C.

Formula in Cell F2:

=IFERROR(CONCAT(E2,VLOOKUP(RIGHT(C2,LEN(C2)-SEARCH("#",SUBSTITUTE(C2,"-","#",LEN(C2)-LEN(SUBSTITUTE(C2,"-",""))))),$H$1:$I$5,2,FALSE)),E2)

Working inside out, it's doing this:

(1) This part identifies the text after the last hyphen:

RIGHT(C2,LEN(C2)-SEARCH("#",SUBSTITUTE(C2,"-","#",LEN(C2)-LEN(SUBSTITUTE(C2,"-",""))))

(2) Then that text is taken into a VLOOKUP formula against cell range H1:I5.

(3) Finally, if that text is not found, then the formula is wrapped with an IFERROR formula to prevent receiving an "N/A" value. If the IFERROR is triggered, then it simply returns the original title.