Increment/decrement a number in text cell

[PhilosophersSage]

I have cells with text and numbers in XX#-XXX or XX#-XXX# and I would like to
increment or decrement just the number(s) in the cells selected with a macro.
Thank you for your help!

 

[PhilosophersSage]

I forgot there is a third format as well XX#-XXXX I don't know if that makes
any diffrence

 

[Rick Rothstein]

Give this macro a try...

Sub IncrementNumbers()
Dim X As Long, Z As Long, Cell As Range, Numbers() As String
For Each Cell In Selection
Numbers = Split(Cell.Value, "-")
For X = 0 To 1
If Numbers(X) Like "*#" Then
For Z = Len(Numbers(X)) To 1 Step -1
If Mid(Numbers(X), Z, 1) Like "[!0-9]" Then
Numbers(X) = Left(Numbers(X), Z) & (1 + Mid(Numbers(X), Z + 1))
Exit For
End If
Next
End If
Next
Cell.Value = Numbers(0) & "-" & Numbers(1)
Next
End Sub

 

[Gary''s Student]

With values like:
12#-345 in your cells, select the cells and run:

Sub Inkrement()
For Each r In Selection
v = r.Value
s = Split(r, "#-")
s(0) = s(0) + 1
s(1) = s(1) + 1
r.Value = Join(s, "#-")
Next
End Sub

 

[Rick Rothstein]

Hmm, in my response, I assumed the X's were text (non-digits) and the #
signs were the numbers.

 

[PhilosophersSage]

I tried it no joy... gives me a type mismatch. It may be because the values
I am working with are like:
TB#-1
TB#-05F#
PR#-WHT
TR#-SHLD
All items have two leading letters and then a number, and it is only the (#)
number that I want to increment or decrement. Then a few have a trailing
number that also need to be changed as well.

 

[Rick Rothstein]

Did you try the code I posted elsewhere in this thread? It works for all of
these examples that you just posted.