Splitting off numeric data

[rollo]

I'm trying to separate the numeric data from cells in column A of a worksheet. Unfortunately the cells contain a non consistent mix of alphnumeric data. I've checked out the thread -
http://www.mrexcel.com/board/viewtop...ic=335&forum=2 - but the solutions there were not completely applicable to my problem. Can anyone help?
The data in column A contains variations of the following:
1846072 MT-AMOT
TFR 1235679 MAN TEL FROM
CM 11030 3 RETRY NO CAL
BM-BRIM COM 1281838
CM 13538 0 8621 EXPEN

Many thanks

[Barrie Davidson]

On 2002-05-14 07:14, rollo wrote:
I'm trying to separate the numeric data from cells in column A of a worksheet. Unfortunately the cells contain a non consistent mix of alphnumeric data. I've checked out the thread -
http://www.mrexcel.com/board/viewtop...ic=335&forum=2 - but the solutions there were not completely applicable to my problem. Can anyone help?
The data in column A contains variations of the following:
1846072 MT-AMOT
TFR 1235679 MAN TEL FROM
CM 11030 3 RETRY NO CAL
BM-BRIM COM 1281838
CM 13538 0 8621 EXPEN

Many thanks

Rollo, try this User Defined Function (UDF).

Function ExtractNumbers(Address As Range)
Dim Position1 As Long
Dim Position2 As Long
Dim Counter As Long

Application.Volatile
For Counter = 1 To Len(Address)
    If Position1 = 0 Then
        If IsNumeric(Mid(Address.Value, Counter, 1)) Then Position1 = Counter
    Else
        If IsNumeric(Mid(Address.Value, Counter, 1)) Then Position2 = Counter
    End If
Next Counter
ExtractNumbers = Mid(Address.Value, Position1, Position2 - Position1 + 1)
End Function

To use this, select Tools|Macro|VisualBasicEditor from the main menu. Select your worksheet in the top left window (the Project Explorer Window), and select Insert|Module from the main menu. Paste this function in the window that opens up on the right. Close the Visual Basic Editor Window. Then type in your formula referencing the cell with information. Something like:

=ExtractNumbers(A1)

Note - this function assumes your numbers are always grouped together (i.e., not something like "ABC 1234 ABC 1234").

Hope this helps you out.

Regards,


_________________

Barrie Davidson
My Excel Web Page

[ This Message was edited by: Barrie Davidson on 2002-05-14 07:32 ]

[rollo]

Many, many thanks, Barrie - that solution was absolutely brilliant. What a great site this is!

rollo