substitute more than one character excel

chulent

New Member
Joined
Apr 30, 2018
Messages
3
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.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

chulent

New Member
Joined
Apr 30, 2018
Messages
3
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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this for results in column "B".
Code:
[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

New Member
Joined
Apr 30, 2018
Messages
3
Mick

That is amazing. Thank you so much.

Regards
 

mabalo76

New Member
Joined
Apr 10, 2018
Messages
27
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

New Member
Joined
Apr 10, 2018
Messages
27
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
 

Forum statistics

Threads
1,171,899
Messages
5,878,100
Members
433,317
Latest member
KenTClark

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top