macro to update last character

  • Thread starter Thread starter maryj
  • Start date Start date
M

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!
 
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.
 
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.
 
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
 
Thanks Mike! Work like a dream!!!
--
maryj


Mike H said:
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.
Click to expand...
 
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.
 
Back
Top