Extracting a number from a cell

Hi,

I have a spreadsheet which has raw data inside which is copy/pasted from multiple different systems and thus one of the columns 'structure' varies dependent on the original source of the data. This isn't too much of a problem as I only need the 'number' from each column.

A couple of examples of entries are (one line per cell)

ABC OVEN CO LTD 35699459 L G
MAGICLTD 3455355 G G D
FISH4LIFE LTD 3224223 D S
SNOW 2 GO LTD 544566 FG

Now I did originally plan on looping through the characters until I encountered the first number, then making a note, going through to the last number and making the last number as the next note then using MID or similar to pull that number out.

Now obviously some of the entries have 'numbers' within the names at the start, I'm only interested in >3 digits ...

Is my method the best way? anyone got any better ideas? maybe some code samples?

I have hundreds of rows to loop through so speed is an issue too, I need to extract the number and then place in the next column.

Many thanks
Mike

Here is one approach.

Sub x()

Dim oMatches As Object, oMatch As Object, i As Long, vOut, rng As Range

With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = "[0-9]{3,}"
    For Each rng In Range("A1", Range("A1").End(xlDown))
        Set oMatches = .Execute(rng)
        ReDim vOut(0 To oMatches.Count - 1)
        For i = 0 To oMatches.Count - 1
            vOut(i) = oMatches(i).Value
        Next i
        rng.Offset(, 1).Resize(, i) = vOut
    Next rng
End With

End Sub