Exracting numerical data.

[James Silverton]

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]

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

 

[Bob Umlas]

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

 

[Gord Dibben]

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

 

[James Silverton]

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

message

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

 

[Biff]

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]

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]

You're welcome!

Biff

 

[dbahooker]

who gives a **** about excel or word.

they're BOTH word processors.

use a fucking database dipshit

-Aaron
ADP Nationalist

 

[dbahooker]

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

 

[OM]

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