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]

[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.

Sorry About that. Here's the summary

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!

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.

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".

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]

I suggest you try to get the required code from the macro recorder :
• first, trim the data to get rid of any leading spaces, then
• do all of your Find/Replace actions (with a leading space) including Nppha/Alpha

 

[Rick Rothstein]

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

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....

If you have hundreds of this code, then I assume you have them stored on a worksheet in a contiguous range... what is the name of that worksheet and what is the address of the first cell with a code in it? Also, are your codes (the ones embed in the text) always surrounded by spaces like your examples show?

 

[gerald24]

If you have hundreds of this code, then I assume you have them stored on a worksheet in a contiguous range... what is the name of that worksheet and what is the address of the first cell with a code in it? Also, are your codes (the ones embed in the text) always surrounded by spaces like your examples show?

Hi Rick,

I don't really have a consolidated list of the codes in a specific sheet because I have the recorded replace macro (below is the 3 codes ive mention, didn't paste all cause it might get too long). but let's just say that I'll put it in "Mapping" tab. First cell that I need to replace is "P2" of "Active" Tab.

Sub replace2()
    Range("P:Q,CN:CN,CS:CS").Select
    Selection.Replace What:=" XZ", Replacement:=" ZZ", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" YP", Replacement:=" MS", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" XY", Replacement:=" JA", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False

End Sub

Yup most of the data I have have spaces in between.