Extract numbers from a string ?

[Guest]

I have serached the String class but cant see any function that can identify
integers within a string ?
I want to replace all instances of "Mystringwith10integer" with
"Mystringwith11integer" etc.
Is there such a funtion ?

Many thanks, Jello

 

[Tom Ogilvy]

select the cells with this information and do

Edit=>Replace

What Mystringwith10integer
With Mystringwith11integer

then click OK. Do this with the macro recorder turned on to see how to
implement in code.

 

[Guest]

Here is a function to find the first instance of a number within a string.
You can modify it to your little hearts content if it is not quite what you
were looking for.

HTH

 

[Guest]

Apologies Tom I wasn't clear enough with my example.

With the string "Mystringwith10integer" I do not want to search for the
specific text, nor do I wish to search for a specific number - I just wish to
increment the integer portion contained within the string to be +1
The contents of the string don't really matter and could be anything, and so
could the integer !

Regards, Jello.

 

[Tom Ogilvy]

This works on the selected cells.

Sub IncrementNumber()
Dim cell As Range, bFnd As Boolean
Dim sStr As String, sChr As String
Dim sNum As String, i As Long
Dim iloc As Long
For Each cell In Selection
sStr = cell.Text
bFnd = False
sNum = ""
For i = 1 To Len(sStr)
sChr = Mid(sStr, i, 1)
If IsNumeric(sChr) Then
sNum = sNum & sChr
If bFnd = False Then
iloc = i
End If
bFnd = True
ElseIf bFnd = True Then
Exit For
End If
Next
If sNum <> "" Then
cell.Value = Replace(sStr, sNum, CLng(sNum) + 1)
End If
Next cell
End Sub

 

[Guest]

Sorry I just noticed that I did not attach the code...

Public Function FirstNumber(ByVal InputString As String) As Integer
Dim intCounter As Integer
Dim intStringLength As Integer
Dim intReturnValue As Integer

intReturnValue = -1
intStringLength = Len(InputString)

For intCounter = 1 To intStringLength
If IsNumeric(Mid(InputString, intCounter, 1)) Then
intReturnValue = intCounter
Exit For
End If
Next intCounter

FirstNumber = intReturnValue

End Function

The function returns the spot of the first digit...

HTH

 

[Ron Rosenfeld]

I have serached the String class but cant see any function that can identify
integers within a string ?
I want to replace all instances of "Mystringwith10integer" with
"Mystringwith11integer" etc.
Is there such a funtion ?

Many thanks, Jello

Something like:

==============
Sub IncrNumInStr()
Const str = "Mystringwith10integer"

Dim i As Long
Dim FirstPart As String
Dim LastPart As String
Dim Num As Long

For i = 1 To Len(str)
If IsNumeric(Mid(str, i, 1)) Then
FirstPart = Left(str, i - 1)
Num = Val(Mid(str, i, 255))
Exit For
End If
Next i

For i = i To Len(str)
If Not IsNumeric(Mid(str, i, 1)) Then
LastPart = Mid(str, i, 255)
Exit For
End If
Next i

Debug.Print FirstPart & Num + 1 & LastPart

End Sub
==================
--ron