I have a spreadsheet with various words and numbers in each cell. The
arrangement of numbers in the string can vary. I want to extract from the
text string, the first 3 numbers as numbers.

Example

dog a cat 45672 223 12.42 mouse caught

I want to parse the above string (which is in a cell) and get
45672
223
12.42

Dim s as String, s1 as String, schr as String
Dim v as Variant, i as long
s = ActiveCell.Text
for i = 1 to len(s)
schr = Mid(s,i,1)
if isnumeric(schr) or schr = "." or schr = "" then
s1 = s1 & schr
end if
Next
v = application.split(s1," ")
for i = lbound(v) to ubound(v)
msgbox i & ": " & v(i)
Next
--
Regards,
Tom Ogilvy




"Micah" wrote:

I have a spreadsheet with various words and numbers in each cell. The
arrangement of numbers in the string can vary. I want to extract from the
text string, the first 3 numbers as numbers.

Example

dog a cat 45672 223 12.42 mouse caught

I want to parse the above string (which is in a cell) and get
45672
223
12.42

On Tue, 30 May 2006 13:39:01 -0700, Micah
wrote:

I have a spreadsheet with various words and numbers in each cell. The
arrangement of numbers in the string can vary. I want to extract from the
text string, the first 3 numbers as numbers.

Example

dog a cat 45672 223 12.42 mouse caught

I want to parse the above string (which is in a cell) and get
45672
223
12.42

One easy method is to download and install Longre's free morefunc.xll add-in
from http://xcell05.free.fr/

Then use regular expressions to define the numbers, and choose the first three
instances.

A regular expression which will define positive numbers of the form in your
example is: "(\d*\.)?\d+"

So the formula to extract the first instance would be:

=REGEX.MID($A$1,"(\d*\.)?\d+",ROWS($1:1))

The ROWS($1:1) subfunction identifies the instance (1=1st number, 2= 2nd
number, etc). So if you copy/drag down the formula, that function will
sequentially return 1, 2, etc as excel adjust the cell reference, giving you
the various instances.

If the number might be preceded by a "-" which you want to return, then use the
regex: "-?(\d*\.)?\d+" in place of the one in the above formula.


--ron

This will get the numbers into 1 cell for you:

Sub Parse()
Dim checkstr As String
checkstr = Range("A1").Text

Range("A2").Value = Trim(StripOutCharType(checkstr, False, ". "))
End Sub

Public Function StripOutCharType(checkstr As String, Optional KillNumbers As
Boolean = True, _
Optional AllowedChar As String, Optional NeverAllow As String) As String


' Function by Patrick Matthews
' For the given string, the function removes all numeric characters
(KillNumbers=True) or
' all non-numeric characters (KillNumbers=False). Use AllowedChar to build
a string of override
' characters that are always allowed. For example, "$,." would indicate
that the dollar sign,
' comma, and period should all be allowed, even if KillNumbers=False;
likewise, "9" would indicate
' that nines should be kept even if KillNumbers=True. NeverAllow is a
string of override
' characters that are never allowed. The "never allowed" characters are
processed before the
' "always allowed" characters, and so if any characters are in both strings
Never allow takes
' precedence


Dim Counter As Long
Dim TestChar As String
Dim TestAsc As Long


' Loop through characters
For Counter = 1 To Len(checkstr)


' Get current character and its ANSI number
TestChar = Mid(checkstr, Counter, 1)
TestAsc = Asc(TestChar)


' Test first to see if current character is never allowed
If InStr(1, NeverAllow, TestChar, vbTextCompare) 0 Then
' do nothing


' If current character is in AllowedChar, keep it
ElseIf InStr(1, AllowedChar, TestChar, vbTextCompare) 0 Then
StripOutCharType = StripOutCharType & TestChar


' If KillNumbers=True, test for not being in numeric range for ANSI
ElseIf KillNumbers Then 'only allow non-numbers
If TestAsc < 48 Or TestAsc 57 Then
StripOutCharType = StripOutCharType & TestChar
End If


' If KillNumbers=False, test for being in numeric ANSI range
Else 'only allow numbers
If TestAsc = 48 And TestAsc <= 57 Then
StripOutCharType = StripOutCharType & TestChar
End If
End If
Next
End Function

Mike F
"Micah" wrote in message
...
I have a spreadsheet with various words and numbers in each cell. The
arrangement of numbers in the string can vary. I want to extract from the
text string, the first 3 numbers as numbers.

Example

dog a cat 45672 223 12.42 mouse caught

I want to parse the above string (which is in a cell) and get
45672
223
12.42

try this to get numbers, spaces and periods ( . )

45672 223 12.42


Sub ExtractNumbersFromText()
For Each c In Range("d2:d4")
ms = ""
For i = 1 To Len(c.Value)
x = Mid(c.Value, i, 1)
If x Like "*[0-9]*" _
Or x = " " Or x = "." Then
ms = ms & Mid(c, i, 1)
End If
Next i
MsgBox Application.Trim(ms)
Next c
End Sub

--
Don Guillett
SalesAid Software

"Micah" wrote in message
...
I have a spreadsheet with various words and numbers in each cell. The
arrangement of numbers in the string can vary. I want to extract from the
text string, the first 3 numbers as numbers.

Example

dog a cat 45672 223 12.42 mouse caught

I want to parse the above string (which is in a cell) and get
45672
223
12.42

this line is flawed/has a typo
if isnumeric(schr) or schr = "." or schr = "" then

should be

if isnumeric(schr) or schr = "." or schr = " " then

with a space in the last double quotes.

--
Regards,
Tom Ogilvy
"Tom Ogilvy" wrote in message
...
Dim s as String, s1 as String, schr as String
Dim v as Variant, i as long
s = ActiveCell.Text
for i = 1 to len(s)
schr = Mid(s,i,1)
if isnumeric(schr) or schr = "." or schr = "" then
s1 = s1 & schr
end if
Next
v = application.split(s1," ")
for i = lbound(v) to ubound(v)
msgbox i & ": " & v(i)
Next
--
Regards,
Tom Ogilvy




"Micah" wrote:

I have a spreadsheet with various words and numbers in each cell. The
arrangement of numbers in the string can vary. I want to extract from
the
text string, the first 3 numbers as numbers.

Example

dog a cat 45672 223 12.42 mouse caught

I want to parse the above string (which is in a cell) and get
45672
223
12.42

On Tue, 30 May 2006 17:46:04 -0400, Ron Rosenfeld
wrote:

On Tue, 30 May 2006 13:39:01 -0700, Micah
wrote:

I have a spreadsheet with various words and numbers in each cell. The
arrangement of numbers in the string can vary. I want to extract from the
text string, the first 3 numbers as numbers.

Example

dog a cat 45672 223 12.42 mouse caught

I want to parse the above string (which is in a cell) and get
45672
223
12.42

One easy method is to download and install Longre's free morefunc.xll add-in
from http://xcell05.free.fr/

Then use regular expressions to define the numbers, and choose the first three
instances.

A regular expression which will define positive numbers of the form in your
example is: "(\d*\.)?\d+"

So the formula to extract the first instance would be:

=REGEX.MID($A$1,"(\d*\.)?\d+",ROWS($1:1))

The ROWS($1:1) subfunction identifies the instance (1=1st number, 2= 2nd
number, etc). So if you copy/drag down the formula, that function will
sequentially return 1, 2, etc as excel adjust the cell reference, giving you
the various instances.

If the number might be preceded by a "-" which you want to return, then use the
regex: "-?(\d*\.)?\d+" in place of the one in the above formula.


--ron

An example of doing the same thing within VBA, (again after installing
morefunc.xll) would be with this routine:

=========================
Sub ParseNums()
Dim str As String
Dim i As Long

Const pattern As String = "-?(\d*\.)?\d+"

str = [A1].Text

For i = 1 To Run([regex.count], str, pattern)
Debug.Print Run([regex.mid], str, pattern, i)
Next i

End Sub
=======================


--ron