Replace last letter in column A using VBA

aaronr

New Member
Joined
Jul 8, 2010
Messages
34
I'm trying to replace the last letter in column A with out replacing any other letter using VBA.

Example - I'm trying to replace the last letter which is "A" in column A to be "B" with out changing any other letters

Column A
TAD106A
TAD107A
TAD108A
TAD109A
TA101A
TA102A
TA103A
TA104A
TA105A

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

Thanks for the help
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,711
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Maybe this and copy down in adjacent column....no VBA

Code:
=SUBSTITUTE(A2,RIGHT(A2,1),"B",2)
 
Last edited:

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
Give this a try. Copy to a standard module.

Requires the use of another column, I used column D. That D column can be changed to any other to suit your sheet, if it is now in use.

Howard

Code:
Option Explicit
Sub AARONR_A_B()
  Dim lRowCount&
 
  lRowCount = Cells(Rows.Count, "A").End(xlUp).Row - 1
  
  With Range("D2").Resize(lRowCount)
    .Formula = "=IF(RIGHT(A2,1)=CHAR(65),LEFT(A2,LEN(A2)-1)&""B"",A2)": .Value = .Value
    .Copy Range("A2")
  End With
  
  [D:D].ClearContents
  
End Sub
 

aaronr

New Member
Joined
Jul 8, 2010
Messages
34
L. Howard thanks for the code and works verify well. I'll added code delete that the whole column D when I'm done. Is there easy way to delete the whole Column D. Again thanks for your help.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,680
Office Version
  1. 2010
Platform
  1. Windows
L. Howard thanks for the code and works verify well. I'll added code delete that the whole column D when I'm done. Is there easy way to delete the whole Column D. Again thanks for your help.
Here is a macro that does not involve any other columns...
Code:
[table="width: 500"]
[tr]
	[td]Sub ReplaceTrailingAwithB()
  Dim Addr As String
  Addr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
  Range(Addr) = Evaluate(Replace("IF(RIGHT(@)=""A"",LEFT(@,LEN(@)-1)&""B"",@)", "@", Addr))
End Sub[/td]
[/tr]
[/table]
 

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
You're welcome.

Be sure to check out Rick's code. No helper column needed.

Howard
 

Watch MrExcel Video

Forum statistics

Threads
1,122,553
Messages
5,596,807
Members
414,104
Latest member
imamalidadashzada

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