Macro to replace last character based on its value

T

tape deck

Board Regular
Joined
Jan 17, 2003
Messages
51
I've been pecking around for a while and have had no luck.

I have a column which is filled with text. The cells have between two and five characters. All cells end in either "A" or "R". I need to replace all of the last characters - all A's become N's, and all R's become A's. Problem being, A and R appear in the text in places other than the last character.

AA
SA
ABEAR
ATAA
MMFR
SHSR
OSSR

Does anyone have a quick macro solution for this? Thank you very much!
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
Why do you need a macro? Here is a formula solution:

=if(right(a1,1)="A",left(a1,len(a1)-1)&"N",if(right(a1,1)="R",left(a1,len(a1)-1)&"A",A1))

Hope that helps if you really need a maco, what column will this be used on and what row would it start at?
 
Upvote 0
Macro not necessary...A formula can do it..

=REPLACE(A1,LEN(A1),1,IF(RIGHT(A1,1)="A","N","A"))
 
Upvote 0
Code: 
Sub Quick_n_Dirty()
Dim End_Row As Long, n As Long

End_Row = Range("A" & Rows.Count).End(xlUp).Row

For n = 2 To End_Row

With Cells(n, 1)
If Right(.Value, 1) = "A" Then .Value = Left(.Value, Len(.Value) - 1) & "N"
If Right(.Value, 1) = "R" Then .Value = Left(.Value, Len(.Value) - 1) & "A"
End With
Next n


End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

megera716
  • Solved
Remove characters from string only if they are certain characters
Replies
3
Views
465
Fluff
Fluff
I
  • Solved
Using InStr to Find Matches Between Columns
Replies
1
Views
544
Farscape2020
F
M
  • Question
Transfer columns from comparable workbook if cell value exists
Replies
2
Views
352
markkuznetsov1
M
R
  • Solved
graph point labeling via macro on Mac version of Excel
Replies
7
Views
415
roscoe
R
W
  • Solved
Macro to Find Last Cell in Column and Use that Cell to Fill in Formula Row of Other Columns
Replies
5
Views
701
leturc
L

Forum statistics

Threads
1,214,920
Messages
6,122,279
Members
449,075
Latest member
staticfluids

Share this page

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
Back
Top