Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract numbers from a string ?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract numbers from a string ?
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. -- Regards, Tom Ogilvy "Jello" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract numbers from a string ?
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 "Jello" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract numbers from a string ?
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" wrote: 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. -- Regards, Tom Ogilvy "Jello" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract numbers from a string ?
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 -- Regards, Tom Ogilvy "Jello" wrote in message ... 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" wrote: 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. -- Regards, Tom Ogilvy "Jello" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract numbers from a string ?
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 "Jim Thomlinson" wrote: 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 "Jello" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract numbers from a string ?
On Thu, 3 Mar 2005 11:49:01 -0800, "Jello"
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract numbers from a string of text | Excel Discussion (Misc queries) | |||
Extract numbers from a string of text | Excel Discussion (Misc queries) | |||
HOW DO I EXTRACT NUMBERS FROM TEXT STRING | Excel Discussion (Misc queries) | |||
extract numbers from a string | Excel Worksheet Functions | |||
Only extract numbers from a string of text | Excel Discussion (Misc queries) |