Exracting numerical data.

Sorry, I recently mistakenly posted this to excel.charting.

I recently had to extract the numerical data from text in
the cells of a fairly long column. Since my problem was
something I don't expect to have to do frequently, I simply
copied the column to Word and used its wild-cards in Replace
instead of Excel 2002's pitifully small list.

I know there are available add-in user functions based on a loop
and ISNUMBER but has anyone got a favorite method using built-in
worksheet or formatting functions that might be better than the
method I used?

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.comcast.not

Biff
Fri Sep 08 13:35:21 CDT 2006

It would all depend on what the strings looked like. It might be very easy,
but it depends.........Can you post several representative samples?

> I simply copied the column to Word and used its wild-cards
> in Replace instead of Excel 2002's pitifully small list.

Yeah well, EXCEL isn't a text word processor! Let's see you do a matrix
calculation in WORD!!!!!

Biff

"James Silverton" <not.jim.silverton@comcast.not> wrote in message
news:O3HN2220GHA.3464@TK2MSFTNGP03.phx.gbl...
> Sorry, I recently mistakenly posted this to excel.charting.
>
> I recently had to extract the numerical data from text in
> the cells of a fairly long column. Since my problem was something I don't
> expect to have to do frequently, I simply copied the column to Word and
> used its wild-cards in Replace instead of Excel 2002's pitifully small
> list.
>
> I know there are available add-in user functions based on a loop and
> ISNUMBER but has anyone got a favorite method using built-in worksheet or
> formatting functions that might be better than the method I used?
>
> James Silverton
> Potomac, Maryland
>
> E-mail, with obvious alterations:
> not.jim.silverton.at.comcast.not

Bob
Fri Sep 08 13:40:07 CDT 2006

Haven't seen your lst of numbers in text, but assuming it's something like:
xxxxx765fffffff
jhgajshgdjhaghsdgas8888ygquwygduygquwygd
etc. and you want to extract the 765 and the 8888, then this set of formulas
will do it.
Assuming your first # is in A1, enter this in B1 via ctrl/shift/enter:
=MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),255)
and enter this in C1 via ctrl/shift/enter:
=1*MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),MA
TCH(TRUE,ISERROR(1*MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1)),0)-1)
fill these both down, and col C will contain the numeric portion.
HTH
Bob Umlas

"James Silverton" <not.jim.silverton@comcast.not> wrote in message
news:O3HN2220GHA.3464@TK2MSFTNGP03.phx.gbl...
> Sorry, I recently mistakenly posted this to excel.charting.
>
> I recently had to extract the numerical data from text in
> the cells of a fairly long column. Since my problem was
> something I don't expect to have to do frequently, I simply
> copied the column to Word and used its wild-cards in Replace
> instead of Excel 2002's pitifully small list.
>
> I know there are available add-in user functions based on a loop
> and ISNUMBER but has anyone got a favorite method using built-in
> worksheet or formatting functions that might be better than the
> method I used?
>
> James Silverton
> Potomac, Maryland
>
> E-mail, with obvious alterations:
> not.jim.silverton.at.comcast.not
>

Gord
Fri Sep 08 15:08:14 CDT 2006

James

With data like so.........qwer/.;'=cnvbf7896.`]][=\

You can pull out numerics only using this UDF

Function DeleteNonNumerics(ByVal sStr As String) As Long
Dim i As Long
If sStr Like "*[0-9]*" Then
For i = 1 To Len(sStr)
If Mid(sStr, i, 1) Like "[0-9]" Then
DeleteNonNumerics = DeleteNonNumerics & Mid(sStr, i, 1)
End If
Next i
Else
DeleteNonNumerics = sStr
End If
End Function

OR use a macro.............................

Sub RemoveAlphas()
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String

Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)

For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR
End Sub


Gord Dibben MS Excel MVP

On Fri, 8 Sep 2006 13:39:20 -0400, "James Silverton"
<not.jim.silverton@comcast.not> wrote:

>Sorry, I recently mistakenly posted this to excel.charting.
>
>I recently had to extract the numerical data from text in
>the cells of a fairly long column. Since my problem was
>something I don't expect to have to do frequently, I simply
>copied the column to Word and used its wild-cards in Replace
>instead of Excel 2002's pitifully small list.
>
>I know there are available add-in user functions based on a loop
>and ISNUMBER but has anyone got a favorite method using built-in
>worksheet or formatting functions that might be better than the
>method I used?
>
> James Silverton
>Potomac, Maryland
>
>E-mail, with obvious alterations:
>not.jim.silverton.at.comcast.not

James
Fri Sep 08 15:09:23 CDT 2006

"Bob Umlas" <Excel_Trickster@msn.com> wrote in message
news:e1kk2Y30GHA.4976@TK2MSFTNGP02.phx.gbl...
> Haven't seen your lst of numbers in text, but assuming it's
> something like:
> xxxxx765fffffff
> jhgajshgdjhaghsdgas8888ygquwygduygquwygd
> etc. and you want to extract the 765 and the 8888, then this
> set of formulas
> will do it.
> Assuming your first # is in A1, enter this in B1 via
> ctrl/shift/enter:
> =MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),255)
> and enter this in C1 via ctrl/shift/enter:
> =1*MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),MA
> TCH(TRUE,ISERROR(1*MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1)),0)-1)
> fill these both down, and col C will contain the numeric
> portion.
> HTH
> Bob Umlas
>
> "James Silverton" <not.jim.silverton@comcast.not> wrote in
> message
> news:O3HN2220GHA.3464@TK2MSFTNGP03.phx.gbl...
>> Sorry, I recently mistakenly posted this to excel.charting.
>>
>> I recently had to extract the numerical data from text in
>> the cells of a fairly long column. Since my problem was
>> something I don't expect to have to do frequently, I simply
>> copied the column to Word and used its wild-cards in Replace
>> instead of Excel 2002's pitifully small list.
>>
>> I know there are available add-in user functions based on a
>> loop
>> and ISNUMBER but has anyone got a favorite method using
>> built-in
>> worksheet or formatting functions that might be better than
>> the
>> method I used?

Thanks Bob! So it is possible and I admire your ingenuity but
I'll bet I could copy into Word, replace characters by nothing
and return about as fast as I could type in the equation and
proof read it :-) Nonetheless, I am going to save your method in
case this type of editing becomes more frequently necessary.

Thanks also Biff but I don't like switching in and out of
programs. I guess it is probably because I was once very
accustomed to Unix and I could easily have done what I wanted
there. Here is a small set of the sort of thing, which resulted
from scanning and OCR of a newspaper article. The irregular
spacing is for real.

All Schools 1634



Wheaton 1313

Kennedy 1420

Einstein 1459

Watkins Mill 1468

Gaithersburg 1498

Rockville 1514
--
Jim Silverton
Potomac, Maryland

Biff
Fri Sep 08 15:32:41 CDT 2006

Based on your samples:

=--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),255)

A1 = All Schools 1634

The formula returns the numeric value: 1634

Copy down as needed.

Biff

"James Silverton" <not.jim.silverton@comcast.not> wrote in message
news:OK2ksK40GHA.4448@TK2MSFTNGP04.phx.gbl...
> "Bob Umlas" <Excel_Trickster@msn.com> wrote in message
> news:e1kk2Y30GHA.4976@TK2MSFTNGP02.phx.gbl...
>> Haven't seen your lst of numbers in text, but assuming it's something
>> like:
>> xxxxx765fffffff
>> jhgajshgdjhaghsdgas8888ygquwygduygquwygd
>> etc. and you want to extract the 765 and the 8888, then this set of
>> formulas
>> will do it.
>> Assuming your first # is in A1, enter this in B1 via ctrl/shift/enter:
>> =MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),255)
>> and enter this in C1 via ctrl/shift/enter:
>> =1*MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),MA
>> TCH(TRUE,ISERROR(1*MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1)),0)-1)
>> fill these both down, and col C will contain the numeric portion.
>> HTH
>> Bob Umlas
>>
>> "James Silverton" <not.jim.silverton@comcast.not> wrote in message
>> news:O3HN2220GHA.3464@TK2MSFTNGP03.phx.gbl...
>>> Sorry, I recently mistakenly posted this to excel.charting.
>>>
>>> I recently had to extract the numerical data from text in
>>> the cells of a fairly long column. Since my problem was
>>> something I don't expect to have to do frequently, I simply
>>> copied the column to Word and used its wild-cards in Replace
>>> instead of Excel 2002's pitifully small list.
>>>
>>> I know there are available add-in user functions based on a loop
>>> and ISNUMBER but has anyone got a favorite method using built-in
>>> worksheet or formatting functions that might be better than the
>>> method I used?
>
> Thanks Bob! So it is possible and I admire your ingenuity but I'll bet I
> could copy into Word, replace characters by nothing and return about as
> fast as I could type in the equation and proof read it :-) Nonetheless, I
> am going to save your method in case this type of editing becomes more
> frequently necessary.
>
> Thanks also Biff but I don't like switching in and out of programs. I
> guess it is probably because I was once very accustomed to Unix and I
> could easily have done what I wanted there. Here is a small set of the
> sort of thing, which resulted from scanning and OCR of a newspaper
> article. The irregular spacing is for real.
>
> All Schools 1634
>
>
>
> Wheaton 1313
>
> Kennedy 1420
>
> Einstein 1459
>
> Watkins Mill 1468
>
> Gaithersburg 1498
>
> Rockville 1514
> --
> Jim Silverton
> Potomac, Maryland
>

James
Fri Sep 08 16:30:52 CDT 2006

Hello, Biff!
You wrote on Fri, 8 Sep 2006 16:32:41 -0400:

B> =--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))
B> ,255)

B> A1 = All Schools 1634

B> The formula returns the numeric value: 1634

B> Copy down as needed.

B> Biff

Thanks again! That's rather impressive!

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.comcast.not

Biff
Fri Sep 08 20:51:17 CDT 2006

You're welcome!

Biff

"James Silverton" <not.jim.silverton@comcast.not> wrote in message
news:uSglO440GHA.2636@TK2MSFTNGP06.phx.gbl...
> Hello, Biff!
> You wrote on Fri, 8 Sep 2006 16:32:41 -0400:
>
> B> =--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))
> B> ,255)
>
> B> A1 = All Schools 1634
>
> B> The formula returns the numeric value: 1634
>
> B> Copy down as needed.
>
> B> Biff
>
> Thanks again! That's rather impressive!
>
> James Silverton
> Potomac, Maryland
>
> E-mail, with obvious alterations: not.jim.silverton.at.comcast.not

dbahooker
Sat Sep 09 17:08:30 CDT 2006

who gives a fuck about excel or word.

they're BOTH word processors.

use a fucking database dipshit

-Aaron
ADP Nationalist



Biff wrote:
> It would all depend on what the strings looked like. It might be very easy,
> but it depends.........Can you post several representative samples?
>
> > I simply copied the column to Word and used its wild-cards
> > in Replace instead of Excel 2002's pitifully small list.
>
> Yeah well, EXCEL isn't a text word processor! Let's see you do a matrix
> calculation in WORD!!!!!
>
> Biff
>
> "James Silverton" <not.jim.silverton@comcast.not> wrote in message
> news:O3HN2220GHA.3464@TK2MSFTNGP03.phx.gbl...
> > Sorry, I recently mistakenly posted this to excel.charting.
> >
> > I recently had to extract the numerical data from text in
> > the cells of a fairly long column. Since my problem was something I don't
> > expect to have to do frequently, I simply copied the column to Word and
> > used its wild-cards in Replace instead of Excel 2002's pitifully small
> > list.
> >
> > I know there are available add-in user functions based on a loop and
> > ISNUMBER but has anyone got a favorite method using built-in worksheet or
> > formatting functions that might be better than the method I used?
> >
> > James Silverton
> > Potomac, Maryland
> >
> > E-mail, with obvious alterations:
> > not.jim.silverton.at.comcast.not

dbahooker
Sat Sep 09 17:10:27 CDT 2006

if Excel had

a) data scrubbing tools
b) update queries
c) macros - like macros in Access- multiple choices not vba lol

then maybe it would be a decent platform.

as it is; Excel has NO VALUE for ANY PURPOSE.

spit on anyone that uses it.

-Aaron
ADP Nationalist



Biff wrote:
> It would all depend on what the strings looked like. It might be very easy,
> but it depends.........Can you post several representative samples?
>
> > I simply copied the column to Word and used its wild-cards
> > in Replace instead of Excel 2002's pitifully small list.
>
> Yeah well, EXCEL isn't a text word processor! Let's see you do a matrix
> calculation in WORD!!!!!
>
> Biff
>
> "James Silverton" <not.jim.silverton@comcast.not> wrote in message
> news:O3HN2220GHA.3464@TK2MSFTNGP03.phx.gbl...
> > Sorry, I recently mistakenly posted this to excel.charting.
> >
> > I recently had to extract the numerical data from text in
> > the cells of a fairly long column. Since my problem was something I don't
> > expect to have to do frequently, I simply copied the column to Word and
> > used its wild-cards in Replace instead of Excel 2002's pitifully small
> > list.
> >
> > I know there are available add-in user functions based on a loop and
> > ISNUMBER but has anyone got a favorite method using built-in worksheet or
> > formatting functions that might be better than the method I used?
> >
> > James Silverton
> > Potomac, Maryland
> >
> > E-mail, with obvious alterations:
> > not.jim.silverton.at.comcast.not

OM
Mon Sep 11 03:20:52 CDT 2006

James,
Based on your sample data - if it is all in one column (but with rows
irregularly between them) - then I would select the whole column and "Sort".
This gets rid of the blank lines. Then Data>Text to Columns> and choose
"Delimited", and "Space" as the delimiter. This will admittedly put (for
instance) "All Schools 1234" in 3 columns, and "Watkins 2332" in 2 columns,
but thats a lot simpler to sort out.

Regards,

Rob

"James Silverton" <not.jim.silverton@comcast.not> wrote in message
news:OK2ksK40GHA.4448@TK2MSFTNGP04.phx.gbl...
> "Bob Umlas" <Excel_Trickster@msn.com> wrote in message
> news:e1kk2Y30GHA.4976@TK2MSFTNGP02.phx.gbl...
>> Haven't seen your lst of numbers in text, but assuming it's something
>> like:
>> xxxxx765fffffff
>> jhgajshgdjhaghsdgas8888ygquwygduygquwygd
>> etc. and you want to extract the 765 and the 8888, then this set of
>> formulas
>> will do it.
>> Assuming your first # is in A1, enter this in B1 via ctrl/shift/enter:
>> =MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),255)
>> and enter this in C1 via ctrl/shift/enter:
>> =1*MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),MA
>> TCH(TRUE,ISERROR(1*MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1)),0)-1)
>> fill these both down, and col C will contain the numeric portion.
>> HTH
>> Bob Umlas
>>
>> "James Silverton" <not.jim.silverton@comcast.not> wrote in message
>> news:O3HN2220GHA.3464@TK2MSFTNGP03.phx.gbl...
>>> Sorry, I recently mistakenly posted this to excel.charting.
>>>
>>> I recently had to extract the numerical data from text in
>>> the cells of a fairly long column. Since my problem was
>>> something I don't expect to have to do frequently, I simply
>>> copied the column to Word and used its wild-cards in Replace
>>> instead of Excel 2002's pitifully small list.
>>>
>>> I know there are available add-in user functions based on a loop
>>> and ISNUMBER but has anyone got a favorite method using built-in
>>> worksheet or formatting functions that might be better than the
>>> method I used?
>
> Thanks Bob! So it is possible and I admire your ingenuity but I'll bet I
> could copy into Word, replace characters by nothing and return about as
> fast as I could type in the equation and proof read it :-) Nonetheless, I
> am going to save your method in case this type of editing becomes more
> frequently necessary.
>
> Thanks also Biff but I don't like switching in and out of programs. I
> guess it is probably because I was once very accustomed to Unix and I
> could easily have done what I wanted there. Here is a small set of the
> sort of thing, which resulted from scanning and OCR of a newspaper
> article. The irregular spacing is for real.
>
> All Schools 1634
>
>
>
> Wheaton 1313
>
> Kennedy 1420
>
> Einstein 1459
>
> Watkins Mill 1468
>
> Gaithersburg 1498
>
> Rockville 1514
> --
> Jim Silverton
> Potomac, Maryland
>