Re: Exracting numerical data.

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@xxxxxxxxxxx> wrote in message
news:OK2ksK40GHA.4448@xxxxxxxxxxxxxxxxxxxxxxx
"Bob Umlas" <Excel_Trickster@xxxxxxx> wrote in message
news:e1kk2Y30GHA.4976@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxx> wrote in message
news:O3HN2220GHA.3464@xxxxxxxxxxxxxxxxxxxxxxx
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
work*** 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



.

Loading