| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract numbers from string
hello,
I am using the following code to extract numbers from a string http://www.ozgrid.com/VBA/ExtractNum.htm however when I have strings like WS123ABC45cft, I would like to extract only the first set of number"123". The code provided extracts "12345" Do you know how to correct the code? Please note that the number of numbers and letters are variables so I cannot use LEFT or RIGHT. thanks -- caroline |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extract numbers from string
How about:
Function numit(r As Range) As Double Dim s As String, s2 As String, c As String Dim b As Boolean b = False s2 = "" s = r.Value l = Len(s) For i = 1 To l c = Mid(s, i, 1) If c Like "#" Then s2 = s2 & c b = True Else If b = True Then Exit For End If Next numit = --s2 End Function -- Gary''s Student - gsnu200776 "caroline" wrote: hello, I am using the following code to extract numbers from a string http://www.ozgrid.com/VBA/ExtractNum.htm however when I have strings like WS123ABC45cft, I would like to extract only the first set of number"123". The code provided extracts "12345" Do you know how to correct the code? Please note that the number of numbers and letters are variables so I cannot use LEFT or RIGHT. thanks -- caroline |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extract numbers from string
Hi,
This is brilliant thanks. Is there anyway I can also select decimals like in 1.5 in ft1.5drt? I tried to adapt your code If c Like "#" or "." Then but it did not work Thanks again -- caroline "Gary''s Student" wrote: How about: Function numit(r As Range) As Double Dim s As String, s2 As String, c As String Dim b As Boolean b = False s2 = "" s = r.Value l = Len(s) For i = 1 To l c = Mid(s, i, 1) If c Like "#" Then s2 = s2 & c b = True Else If b = True Then Exit For End If Next numit = --s2 End Function -- Gary''s Student - gsnu200776 "caroline" wrote: hello, I am using the following code to extract numbers from a string http://www.ozgrid.com/VBA/ExtractNum.htm however when I have strings like WS123ABC45cft, I would like to extract only the first set of number"123". The code provided extracts "12345" Do you know how to correct the code? Please note that the number of numbers and letters are variables so I cannot use LEFT or RIGHT. thanks -- caroline |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extract numbers from string
You were very, very close:
Function numit(r As Range) As Double Dim s As String, s2 As String, c As String Dim b As Boolean b = False s2 = "" s = r.Value l = Len(s) For i = 1 To l c = Mid(s, i, 1) If c Like "#" Or c = "." Then s2 = s2 & c b = True Else If b = True Then Exit For End If Next numit = --s2 End Function -- Gary''s Student - gsnu200776 "caroline" wrote: Hi, This is brilliant thanks. Is there anyway I can also select decimals like in 1.5 in ft1.5drt? I tried to adapt your code If c Like "#" or "." Then but it did not work Thanks again -- caroline "Gary''s Student" wrote: How about: Function numit(r As Range) As Double Dim s As String, s2 As String, c As String Dim b As Boolean b = False s2 = "" s = r.Value l = Len(s) For i = 1 To l c = Mid(s, i, 1) If c Like "#" Then s2 = s2 & c b = True Else If b = True Then Exit For End If Next numit = --s2 End Function -- Gary''s Student - gsnu200776 "caroline" wrote: hello, I am using the following code to extract numbers from a string http://www.ozgrid.com/VBA/ExtractNum.htm however when I have strings like WS123ABC45cft, I would like to extract only the first set of number"123". The code provided extracts "12345" Do you know how to correct the code? Please note that the number of numbers and letters are variables so I cannot use LEFT or RIGHT. thanks -- caroline |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extract numbers from string
If c Like "#" Or c = "." Then
Or... If c Like "[0-9.]" Then Rick "Gary''s Student" wrote in message ... You were very, very close: Function numit(r As Range) As Double Dim s As String, s2 As String, c As String Dim b As Boolean b = False s2 = "" s = r.Value l = Len(s) For i = 1 To l c = Mid(s, i, 1) If c Like "#" Or c = "." Then s2 = s2 & c b = True Else If b = True Then Exit For End If Next numit = --s2 End Function -- Gary''s Student - gsnu200776 "caroline" wrote: Hi, This is brilliant thanks. Is there anyway I can also select decimals like in 1.5 in ft1.5drt? I tried to adapt your code If c Like "#" or "." Then but it did not work Thanks again -- caroline "Gary''s Student" wrote: How about: Function numit(r As Range) As Double Dim s As String, s2 As String, c As String Dim b As Boolean b = False s2 = "" s = r.Value l = Len(s) For i = 1 To l c = Mid(s, i, 1) If c Like "#" Then s2 = s2 & c b = True Else If b = True Then Exit For End If Next numit = --s2 End Function -- Gary''s Student - gsnu200776 "caroline" wrote: hello, I am using the following code to extract numbers from a string http://www.ozgrid.com/VBA/ExtractNum.htm however when I have strings like WS123ABC45cft, I would like to extract only the first set of number"123". The code provided extracts "12345" Do you know how to correct the code? Please note that the number of numbers and letters are variables so I cannot use LEFT or RIGHT. thanks -- caroline |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extract numbers from string
Thanks!
-- Gary''s Student - gsnu200776 "Rick Rothstein (MVP - VB)" wrote: If c Like "#" Or c = "." Then Or... If c Like "[0-9.]" Then Rick "Gary''s Student" wrote in message ... You were very, very close: Function numit(r As Range) As Double Dim s As String, s2 As String, c As String Dim b As Boolean b = False s2 = "" s = r.Value l = Len(s) For i = 1 To l c = Mid(s, i, 1) If c Like "#" Or c = "." Then s2 = s2 & c b = True Else If b = True Then Exit For End If Next numit = --s2 End Function -- Gary''s Student - gsnu200776 "caroline" wrote: Hi, This is brilliant thanks. Is there anyway I can also select decimals like in 1.5 in ft1.5drt? I tried to adapt your code If c Like "#" or "." Then but it did not work Thanks again -- caroline "Gary''s Student" wrote: How about: Function numit(r As Range) As Double Dim s As String, s2 As String, c As String Dim b As Boolean b = False s2 = "" s = r.Value l = Len(s) For i = 1 To l c = Mid(s, i, 1) If c Like "#" Then s2 = s2 & c b = True Else If b = True Then Exit For End If Next numit = --s2 End Function -- Gary''s Student - gsnu200776 "caroline" wrote: hello, I am using the following code to extract numbers from a string http://www.ozgrid.com/VBA/ExtractNum.htm however when I have strings like WS123ABC45cft, I would like to extract only the first set of number"123". The code provided extracts "12345" Do you know how to correct the code? Please note that the number of numbers and letters are variables so I cannot use LEFT or RIGHT. thanks -- caroline |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extract numbers from string
On Mar 27, 12:02*pm, caroline
wrote: hello, I am using the following code to extract numbers from a stringhttp://www.ozgrid.com/VBA/ExtractNum.htm however when I have strings like WS123ABC45cft, I would like to extract only the first set of number"123". The code provided extracts "12345" Do you know how to correct the code? Please note that the number of numbers and letters are variables so I cannot use LEFT or RIGHT. thanks -- caroline If you need to you can use left and right ... because it's easy to get the length of a string ( = len(strText) ) Why don't you use this little bit of code if you're never going to see decimals and negative signs ... Option Explicit Private Sub GetFirstNumber() Dim strText As String Dim I As Integer Dim bnOK As Boolean strText = "WS123ABC45cft" I = 1 bnOK = False Do Until IsNumeric(strText) = True If IsNumeric(Mid(strText, I, 1)) = False Then If bnOK = False Then strText = Mid(strText, 2) Else strText = Left(strText, I - 1) End If Else I = I + 1 bnOK = True End If Loop End Sub Of course changing my hard-coded strText = "WS123ABC45cft" with a cell ... cheers Chris |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extract numbers from string
On Mar 27, 12:02*pm, caroline
wrote: hello, I am using the following code to extract numbers from a stringhttp://www.ozgrid.com/VBA/ExtractNum.htm however when I have strings like WS123ABC45cft, I would like to extract only the first set of number"123". The code provided extracts "12345" Do you know how to correct the code? Please note that the number of numbers and letters are variables so I cannot use LEFT or RIGHT. thanks -- caroline Note: if you like, you could add the code as a public function ... then when you're in Excel you can just enter in " =getfirstnumber(K54) " and get the value ! The difference is just Public Function GetFirstNumber(strText As String) and this at the last row of the function: GetFirstNumber = CSng(strText) HTH Chris |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extract numbers from string
Here is my offering...
Function ExtractNumber(rCell As Range) As Double Dim X As Long For X = 1 To Len(rCell.Value) If Mid$(rCell.Value, X, 1) Like "*[0-9.]" Then ExtractNumber = Val(Mid$(rCell.Value, X)) Exit For End If Next End Function Rick "caroline" wrote in message ... hello, I am using the following code to extract numbers from a string http://www.ozgrid.com/VBA/ExtractNum.htm however when I have strings like WS123ABC45cft, I would like to extract only the first set of number"123". The code provided extracts "12345" Do you know how to correct the code? Please note that the number of numbers and letters are variables so I cannot use LEFT or RIGHT. thanks -- caroline |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extract numbers from string
I guess I should mention... if the number can contain a decimal point and
the region settings are for the decimal point to be a comma, or if the number contains "thousands separators", then the function, as posted, won't work. This one will for either decimal point setting (provided the number still doesn't have thousands separators in it)... Function ExtractNumber(rCell As Range) As Double Dim X As Long For X = 1 To Len(rCell.Value) If Mid$(rCell.Value, X, 1) Like "*[0-9.]" Then ExtractNumber = Val(Replace(Mid$(rCell.Value, X), ",", ".")) Exit For End If Next End Function Rick "Rick Rothstein (MVP - VB)" wrote in message ... Here is my offering... Function ExtractNumber(rCell As Range) As Double Dim X As Long For X = 1 To Len(rCell.Value) If Mid$(rCell.Value, X, 1) Like "*[0-9.]" Then ExtractNumber = Val(Mid$(rCell.Value, X)) Exit For End If Next End Function Rick "caroline" wrote in message ... hello, I am using the following code to extract numbers from a string http://www.ozgrid.com/VBA/ExtractNum.htm however when I have strings like WS123ABC45cft, I would like to extract only the first set of number"123". The code provided extracts "12345" Do you know how to correct the code? Please note that the number of numbers and letters are variables so I cannot use LEFT or RIGHT. thanks -- caroline |
| 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 | Excel Worksheet Functions | |||
| Only extract numbers from a string of text | Excel Discussion (Misc queries) | |||
| Extract Numbers From String | Excel Programming | |||
| Extract numbers from a string ? | Excel Programming | |||
Hybrid Mode
