Thread: Insert Character and Text within Cell that already contains a word

Hello,

I'd like to know if anyone has a formula to do the following:
Assume I have a bloomberg ticker "BB LN", but I want a formula that will make this word appear like "BB/ LN Equity" (with the slash + "Equity" at this end of the text).
I wrote a formula like =IF(ISERROR(SEARCH("*WG LN*",F46,1)),"","WG/ LN Equity ").
You'll note that this formula is inefficient since I have to write the element WG LN (in the 1st part of the formula) or take it off and simply replace it by F46 (which would give SEARCH(F46,F46,1), which would at least enable me to drag down the column to change the targeted text, BUT here's where it gets complicated: I also want to be able in the last argument of the formula to automatically "WG" to be changed in respect to F46 and insert "/" + "Equity".
It sounds complicated but I'm sure there's a way to fix my problem.

An example is attached in my spreadsheet, should help you better understand my issue

Thank you in advance for your help

Try this
=SUBSTITUTE(A2," ","/ ",1)&" Equity"
Does that work for you?

to get results you have in col b
=LEFT(A2,FIND(" ",A2)-1)&"/ ln equity"

Is A4 and B4 a match?

If not:

=IF(ISNUMBER(SEARCH(TRIM(SUBSTITUTE(SUBSTITUTE(B2,"/",""),"Equity","")),TRIM(A2))),SUBSTITUTE(B2,"Equity","")&"Equity","")

if yes,

=IF(ISNUMBER(SEARCH(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2," ",""),"/","*"),"Equity","")),TRIM(A2))),SUBSTITUTE(B2,"Equity","")&"Equity","")

I guess I misinterpreted as you wanting to compare column A with Column B and see if matches exist... my mistake.