Search number in a string (excel vba)
 

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

Search number in a string (excel vba)
 

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


"Jac" wrote in message
.. .
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