Search number in a string (excel vba)

  • Thread starter Thread starter Jac
  • Start date Start date
J

Jac

Hello,
I have a string in cell(1,1) with a number in. For example: "This is
sensor_01" (it's not always te same string, it can be longer or shorter).
Now I want to copy this to cell(2,1) en increase the numer in the string
"This is sensor_02".
Does anybody know how to search for a number in a string, increase it and
put it back?
Jac
 
Jac,

Try something like the following:

Dim S As String
Dim Pos As Integer
Dim N As Integer

S = Range("A1").Text
Pos = InStr(1, S, "_")
N = CInt(Mid(S, Pos + 1))
S = Left(S, Pos) & Format(N + 1, "00")
Range("A2").Value = S


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Back
Top