VBA Replace

[gerald24]

Hi Guys,

Suppose I have a list on text that I want to replace a specific part of it with something.


Column A
11346 XZ Alpha
91970 XZ Alpha
113974 YP Alpha
1135 YP Alpha
99786 XY Alpha
99786 XY


from XZ to ZZ
from YP to MS
from XY to JA

In my current code, I recorded a find/replace code.

What I desire to accomplish is for it to replace those codes in the middle or even if the word "Alpha" is not present, I still want to replace the "XY". I have hundreds of this code, but I just need to have a sample how can I code this?

Thanks!

 

[gerald24]

The reason why I need to move from "Find / Repalce" to a better code is that some text in the first part of the cell value contains "XZ". For Example, 11XZ39 YP Alpha, I just need it to be 11XZ39 MS ALPHA and not 11ZZ39 Alpha.

 

[footoo]

Instead of find "XZ" replace "ZZ" use :

find " XZ" replace " ZZ"

 

[Rick Rothstein]

Don't replace "YP", rather, replace " YP " (note the surrounding spaces) with " MS " (again, not the surrounding spaces). Do the same for your other replacements.

 

[gerald24]

Hi Guys,

I actually did that too already, but forgot to mention, I also have "AL to NP", which make my Alpha to "Nppha". Tried to set Case Sensitive to True, but my data contains "Alpha" and "ALPHA".

 

[footoo]

Hi Guys,

I actually did that too already, but forgot to mention, I also have "AL to NP", which make my Alpha to "Nppha". Tried to set Case Sensitive to True, but my data contains "Alpha" and "ALPHA".

I suppose you could perform a final Find Replace of "Nppha" with "Alpha".

 

[Rick Rothstein]

I also have "AL to NP", which make my Alpha to "Nppha".

That cannot happen with what I suggested in Message #4 (a space character on EACH side of your two letters).

 

[footoo]

That cannot happen with what I suggested in Message #4 (a space character on EACH side of your two letters).

But your suggestion doesn't work on the last example in post #1 : 99786 XY

 

[gerald24]

But your suggestion doesn't work on the last example in post #1 : 99786 XY

Another thing is that, XZ might appear on the first part of the text. like XZ1346 YP ALPHA, in which " XZ" might replace. which means the "space" technique might get the best of my data.


I am supposing that I can use the below formula

=IF(RIGHT(A1,6)=" ALPHA",REPLACE(LEFT(A1,LEN(A1)-6),LEN(A1)-7,2,VLOOKUP(RIGHT(LEFT(A1,LEN(A1)-6),2),$A$3:$B$5,2,0)&" ALPHA"),REPLACE(A1,LEN(A1)-1,2,VLOOKUP(RIGHT(A1,2),$A$3:$B$5,2,0)))

let's say that my text is in A1 and I have a mapping in A3:C5.

I just don't know how to put this in codes. I don't really prefer maintaining a mapping.

 

[footoo]

[COLOR=#333333]Another thing is that, XZ might appear on the first part of the text. like XZ1346 YP ALPHA, in which " XZ" might replace. which means the "space" technique might get the best of my data.[/COLOR]

If you need help, you will have to stop moving the goalposts.