Excel 2007 : Replace Text Function

[jeliash]

I have spent along time trying to figure this out, perhaps someone can help me with this.

If the TEXT in cell A1 is the same as the TEXT found anywhere on Column B, then replace it with the TEXT that is on Column C (On the side of the corresponding cell in Column B.)

For example, if A1 is the same as B5, then replace A1 with C5.

I hope this makes sense, I had a function for this long time ago, but I can't remember, this is frustrating. :S

Any help is greatly appreciated, thank you!

[zbor]

Please rename title according to the rules rules!

1. Use concise, accurate thread titles. Your post title should describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change the title of the thread, click EDIT on the original post, then click the Go Advanced button, then change the title. If two or more hours have passed, the EDIT button will not appear, and you need to ask a moderator to change the title.

[jeliash]

I will explain it here again:

In Column A I have:
Superfast Car [Lexus]
Boat in the Sea [Whaler,Blue]
Wooden Table [Brown]
Superfast Car [Lexus]
Superfast Car [Lexus]

In Column B:
Superfast Car
Boat in Sea
Wooden Table

In Column C:
Car
Boat
Table

Now, in column D I want:
If Column A contains the word "Superfast Car", then use the word "Car" instead and keep the word [Lexus]. It found 3 matches for the word Superfast Car. Others will be changed too.

Therefore, column D would be as follows:
Car [Lexus]
Boat [Whaler,Blue]
Table [Brown]
Car [Lexus]
Car [Lexus]

Basically it is replacing the keywords in Column A to a short word in Column D.

I did it once long time ago and it was a short formula, but I can't seem to make it work this time.

[Haseeb Avarakkan]

Try this, in D1 copy down,

=IFERROR(LOOKUP(9E300,SEARCH(SUBSTITUTE(B$1:B$3," ","*"),A1),C$1:C$3)&" "&MID(A1,FIND("[",A1),250),"")

[jeliash]

Hi Haseeb,
Thank you, it works for the most part, just one minor thing, not sure how to fix it, on Column A, if there are not brackets next to the keyword, then it leaves it blank.

For example,
In Column A I have:
Superfast Car [Lexus]
Boat in the Sea [Whaler,Blue]
Wooden Table [Brown]
Superfast Car [Lexus]
Superfast Car <--------- If this one has no brackets, therefore it should still show "Superfast Car" in Column D.

[Haseeb Avarakkan]

One way,

=IFERROR(IF(ISERROR(FIND("[",A1)),LOOKUP(9E300,SEARCH(SUBSTITUTE(B$1:B$3," ","*"),A1),B$1:B$3),LOOKUP(9E300,SEARCH(SUBSTITUTE(B$1:B$3," ","*"),A1),C$1:C$3)&" "&MID(A1,FIND("[",A1&"["),250)),"")

[jeliash]

Haseeb, I truly apologize, but I did a mistake while trying to explain to you. I have been trying to make your formula work before I bothered you again, but I give up.

In Column A I have:
Superfast Car [Lexus]
Boat in the Sea [Whaler,Blue]
Wooden Table [Brown]
Superfast Car [Lexus]
Superfast Car <--------- If this one has no brackets, therefore it should still show "Car" in Column D.

It should show "Car" not "Superfast Car".

[Haseeb Avarakkan]

Just add &"[" in FIND,

=IFERROR(LOOKUP(9E300,SEARCH(SUBSTITUTE(B$1:B$3," ","*"),A1),C$1:C$3)&" "&MID(A1,FIND("[",A1&"["),250),"")

Red highlighted is the change

[jeliash]

Haseeb,
Were would I need to change it on this function?

=IFERROR(IF(ISERROR(FIND("[",A1)),LOOKUP(9E300,SEARCH(SUBSTITUTE(B$1:B$3," ","*"),A1),B$1:B$3),LOOKUP(9E300,SEARCH(SUBSTITUTE(B$1:B$3," ","*"),A1),C$1:C$3)&" "&MID(A1,FIND("[",A1&"["),250)),"")

Because this one worked fine. Thank you for helping me out.