macro to update last character

[maryj]

We are using Excel 2007. We have a large list of id numbers that will need
the last character changed from a 1 to 2. The length of the list will always
vary but will always be in Column G.

For example:
11323674US01
11321507US01
11378181US01
need to be updated to:
11323674US02
11321507US02
11378181US02

Thanks for your help!

 

[maryj]

Sorry about the double post.I got an error the first time so didn't think it
got posted.

 

[Mike H]

Hi,

Try this. Change Sht variable to the name of your sheet

Sub last_Char()
Dim LastRow As Long
Dim MyRange As Range, c As Range
Set sht = Sheets("Sheet2") ' change to suit
LastRow = sht.Cells(Cells.Rows.Count, "G").End(xlUp).Row
Set MyRange = sht.Range("G1:G" & LastRow)
For Each c In MyRange
c.Value = Left(c.Value, Len(c.Value) - 1) & _
Right(c.Value, 1) + 1
Next
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Mike H]

Hi,

On reflection I should have included a chack to ensure the rightmost
character was numeric

Sub last_Char()
Dim LastRow As Long
Dim MyRange As Range, c As Range
Set sht = Sheets("Sheet2") ' change to suit
LastRow = sht.Cells(Cells.Rows.Count, "G").End(xlUp).Row
Set MyRange = sht.Range("G1:G" & LastRow)
For Each c In MyRange
If IsNumeric(Right(c.Value, 1)) Then
c.Value = Left(c.Value, Len(c.Value) - 1) & _
Right(c.Value, 1) + 1
End If
Next
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[IgorM]

Assuming the values are always stored in column G why don't you add (in any
other empty column) formula as follows:
=IF(NOT(ISBLANK(G1)),CONCATENATE(LEFT(G1,LEN(G1)-1),"2"),"")
The formula shows empty string if there is now value in column G and it also
assumes that the length of string in column G may vary.

Hope it helps.

Kind regards
IgorM

 

[maryj]

Thanks Mike! Work like a dream!!!
--
maryj

Hi,

Try this. Change Sht variable to the name of your sheet

Sub last_Char()
Dim LastRow As Long
Dim MyRange As Range, c As Range
Set sht = Sheets("Sheet2") ' change to suit
LastRow = sht.Cells(Cells.Rows.Count, "G").End(xlUp).Row
Set MyRange = sht.Range("G1:G" & LastRow)
For Each c In MyRange
c.Value = Left(c.Value, Len(c.Value) - 1) & _
Right(c.Value, 1) + 1
Next
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Mike H]

Glad I could help and thanks for the feedback
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.