Mid Formula - extract numerals..

[Glaswegian]

Hi

Not being very good with formulae, I'm a bit stuck and need some help.

I have cells with the following mixture of text and numerals.

ANYTEXTHERE.123456 TEXT

The amount of text before the numerals is variable and the number of numerals is variable. However, there is always a full stop before the numerals and always a space after the numerals. I therefore need to extract only the numerals.

My best shot at this came up with

=MID(A26,FIND(".",A26,1)+1,LEN(A26)-(FIND(" ",A26,5)))

but the problem (I think) is to do with the last FIND part.

Any help greatly appreciated.

[Charlize]

To use put in A2 when in A1 your startvalue 'anywhere.123346 text' : =extractno(A1)

Function extractno(cell As Range)
Dim no() As String
Dim vread As String
ReDim Preserve no(2)
vread = cell.Value
no = Split(vread, ".")
vread = no(1)
no = Split(vread, " ")
extractno = no(0)
End Function

[Glaswegian]

Works a treat Charlize - many thanks!

[mdmackillop]

Exactly the same method, but combine it into one line
Function extractno(cell As Range)
extractno = Split(Split(cell, ".")(1), " ")(0)
End Function

[Charlize]

As a bonus, try this one :

=MID(A26,SEARCH(".",A26,1)+1,SEARCH(" ",MID(A26,SEARCH(".",A1,1)+1,LEN(A1))))

Charlize