substitute more than one character excel

[chulent]

Hi

I am really new to this so please excuse me if there is a simple way to do this!

I have a string of letters and numbers that I want to change to the next letter/number in the sequence so A becomes B, B becomes C etc. Please can someone show me how this would be done in excel.

I have pasted below what I have:

ACTO032AB1PEND

ACTO032CE1PEND

ACTO032CP1PEND

ACTO032SN1PEND

ALDG019SI1PEND

ALDG028SI1PEND

ALDG030SI1PEND

ANER028CP1PEND

ANER028MB1PEND

ANER028SN1PEND

ARCH018CH1PEND

ARCH018PB1PEND

ARCH025CH1PEND

ARCH025PB1PEND

ARCH040CH1PEND

ARCH040PB1PEND

BATT036CH7DECO

BATT045CH10DECO

BELG022CL1WAL

BELG046CL5DECO

BELG046CL5FLUS

BELG051CL6DECO

BELG082CL6DECO

BELG105CL8DECO

BREN032CH3TABL

BREN032GD3TABL

BREN042CH4DECO

BREN042CH4FLOL

BREN042GD4DECO

BREN042GD4FLOL

BREN065CH6DECO

BREN065GD6DECO

BREN082CH5DECO

BREN082GD5DECO

CANO032CH2WAL

CANO032CP2WAL

CANO036CH7DECO

CANO036CP7DECO

CANO049CH3DECO

CANO049CH3FLUS

CANO049CP3DECO

CANO049CP3FLUS

CANO057CH10DECO

CANO057CH5DECO

CANO057CH5FLUS

CANO057CP10DECO

CANO057CP5DECO

CANO057CP5FLUS

CANO060CH19DECO

CANO060CP19DECO

CANO063CH4FLUS

CANO063CP4FLUS

CANO067CH5FLUS

CANO067CP5FLUS

CHEL038CG3STAT

CHEL038CS3STAT

CHEL053CG5STAT

CHEL053CS5STAT

CHIS025CP1PEND

CHIS026CP1PEND

CHIS030CP1PEND

CHIS036CP1PEND

CROF027CP1PEND

CROF027MB1PEND

CROF027SN1PEND

CROF030CP6PEND

CROF030MB6PEND

CROF030SN6PEND

CRYS030SI4TABL

CRYS037SI4FLOL

CRYS045SI6FLUS

CRYS055SI7DECO

CRYS055SI7FLUS

CRYS085SI6DECO

CRYS094SI3DECO

DOLL030CP1PEND

DOLL030CP1TABL

DOLL030MB1PEND

DOLL030MB1TABL

DOLL040CP1FLOL

DOLL040MB1FLOL

EALI035SG6DECO

EALI040SG7DECO

EALI073SG4DECO

FINC025AB1STAT

FINC030AB1STAT

FINC031AB1STAT

FINC034AB1STAT

FINC038AB1STAT

FINC044AB1STAT

HAMP033BRA3FLUS

HAMP033NC3FLUS

HANW035CP1PEND

HANW035MB1PEND

HANW035SN1PEND

HEND042BL3DECO

HOLB030BL1PEND

HOLB030WH1PEND

HOLB032BL1PEND

HOLB032WH1PEND

HOLL013CH1WAL

HOLL013CP1WAL

HOLL034CH1TABL

HOLL034CP1TABL

HOLL042CH4FLUS

HOLL042CP4FLUS

HOLL058CH3DECO

HOLL058CH3FLUS

HOLL058CP3DECO

HOLL058CP3FLUS

HOLL060CH6DECO

HOLL060CH6FLUS

HOLL060CP6DECO

HOLL060CP6FLUS

HOLL098CH4DECO

HOLL098CP4DECO

HOLL152CH3FLOL

HOLL152CP3FLOL

HOLW041CH5DECO

HOLW045CH4FLUS

HOLW048CH1TABL

HOLW050CH6DECO

HOLW157CH4FLOL

KENS065GD5STAT

KENS065NC5STAT

KENS115GD7STAT

KENS115NC7STAT

KENT018CG1PEND

KENT018CL1PEND

KENT018CP1PEND

KENT018SG1PEND

LAMB028CH3TABL

LAMB040CH4FLOL

LAMB040CH5DECO

LEWI035CP3DECO

LEWI039CP2WAL

LEWI051CP7DECO

LEWI092CP4DECO

LEYT012CH1PEND

LEYT033CH3DECO

LEYT039CH5DECO

LEYT062CH3DECO

MERT038CP1PEND

MERT038MB1PEND

MERT038SN1PEND

MERT050CP1PEND

MERT050MB1PEND

MERT050SN1PEND

MOOR018AC1PEND

MOOR025AC1PEND

MOOR026AC1PEND

MOOR040AC1PEND

NOTT050SI6STAT

NOTT060SI12STAT

NOTT050BRA6STAT

NOTT080BRA10STAT

NOTT080SI10STAT

PLAI023MB1PEND

PLAI023WH1PEND

PLAI025MB1TABL

PLAI025WH1TABL

PLAI030MB1PEND

PLAI030WH1PEND

PLAI040MB1PEND

PLAI040WH1PEND

PLUM014CH1PEND

PLUM014CP1PEND

PLUM020CH1WAL

PLUM020CP1WAL

PLUM042CH7DECO

PLUM042CP7DECO

PLUM062CH3DECO

PLUM062CP3DECO

RICH025CS10DECO

RICH036CS3TABL

RICH055CS10DECO

RICH125CS10DECO

STAM045AB1PEND

STAM045CE1PEND

STAM045CP1PEND

STAM045SN1PEND

VICT036AB4PEND

VICT036NC4PEND

VICT043AB4PEND

VICT043NC4PEND

WIMB031CL2WAL

WIMB062CL8DECO

WIMB064CL7FLUS

WIMB076CL12DECO

<colgroup><col></colgroup><tbody>
</tbody>

Any help would really be appreciated.

 

[mabalo76]

You can create a UDF to loop through the string and replace

 

[chulent]

Hi mabalo76

Thanks so much for replying.

I really do not know much about these things. Would you be able to explain or show me how this is done?

Thanks so much

 

[MickG]

Try this for results in column "B".

[COLOR="Navy"]Sub[/COLOR] MG30Apr48
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("A1", Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
[COLOR="Navy"]For[/COLOR] n = 1 To Len(Dn.Value)
   [COLOR="Navy"]If[/COLOR] Not IsNumeric(Mid(Dn.Value, n, 1)) [COLOR="Navy"]Then[/COLOR]
        Txt = Txt & Chr(Asc(Dn.Characters(n, 1).Text) + 1)
   [COLOR="Navy"]Else[/COLOR]
       Txt = Txt & Dn.Characters(n, 1).Text
   [COLOR="Navy"]End[/COLOR] If
 [COLOR="Navy"]Next[/COLOR] n
    Dn.Offset(, 1).Value = Txt: Txt = ""
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

This may Help !!!
To Save and Run Code:-
Copy code from Thread
In Your Data sheet , Click "Alt+F11",:- Vb Window appears.
From the VBWindow toolbar, Click "Insert" ,"Module":- New VBwindow appears .
Paste Code into this window.
Close Vbwindow.
On sheet Click "Developer tab", Click "Macro". Macro dialog box appears.
Select Macro (with same name) from List.
On the right of Dialog box Click "Run"
The Sheet should now be updated.
Regrds Mick

Regards Mick

 

[chulent]

Mick

That is amazing. Thank you so much.

Regards

 

[mabalo76]

Function substitute_string(s As String) As String
Dim j As Integer
Dim str As String
For j = 1 To Len(s)
str = str + Chr(Asc(Mid(s, j, 1)) + 1)
Next
substitute_string = str
End Function

 

[mabalo76]

Modified function:

replace 9 with 0
replace Z with A
others with the next


Function substitute_string(s As String) As String
Dim j As Integer
Dim str As String
For j = 1 To Len(s)
If Mid(s, j, 1) = "9" Then
str = str + "0"
ElseIf Mid(s, j, 1) = "Z" Then
str = str + "A"
Else
str = str + Chr(Asc(Mid(s, j, 1)) + 1)
End If
Next
substitute_string = str
End Function

 

[MickG]

You're welcome