Need Code to add number to text

[lylegross]

Can someone show me a way to write a code to add a one number to a text.

Example W001 change to W002.

Thanks for the help

[karedog]

Hi,

First, select the cell where the action want to performed, then run this sub.

Sub Test()
  Dim str1 As String, i As Long
  str1 = Selection.Value
  For i = 1 To Len(str1)
      If IsNumeric(Mid(str1, i, 1)) Then
         str1 = Left(str1, i - 1) & Right(String$(255, "0") & Val(Mid(str1, i)) + 1, Len(str1) - i + 1)
         Selection.Value = str1
         Exit Sub
      End If
  Next i
End Sub

Regards

[JimBobBowie]

Hi, Here is a formula that will add 1 to your code (column A) if the cell to the right in column B is not empty. This formula goes in column a row 2. You will need to type in a starting code in cell A1.

=IF(B2<>"",LEFT(A1,1) & TEXT(RIGHT(A1,LEN(A1)-1)+1,"000"),"")

[Kamboj]

Dear lylegross,
If you'll use drag & fill function with mouse then it will add value.
suppose you have w001,w002,w003 and if you drag the value then it will add further and you can use these values by copy paste

[JOHN H. DAVIS]

Assumes A1 has the text string.

Sub lylegross()
Dim x As Long
x = Right([A1], 1)
[A1] = Left([A1], Len([A1]) - 1) & x + 1
End Sub

[lylegross]

Thanks for all your help
I used the code but did a little modification and it works great.

Thanks again