formula needed for numbers

[ericlch16]

in a column A there is these values

F23456 H9454555 J232334
F#23450 H9832444 J893338
H89232123 F#99999 J#888888
78734 567788 6788967
K899932 L89765 J9999999

i want a formula to return the 5 digit number i.e
23456
23450
99999
78734
89765

is that possible? there is no consistency in the data in the cells. the number are not in order but there is always 5 digit number, 6 digit number, 7 digit numbers. i want to pick up the 5 digit number in another column?is that possible?

 

[Scott Huish]

=mid(left(a1,find(" ",a1)-1),min(find({"0","1","2","3","4","5","6","7","8","9"},a1&"0123456789")),find(" ",a1))+0

 

[ericlch16]

thanks

I am trying to understand the formula..but how to get the 6 digit number or 7 digit number? which parameters you change in the formula?

 

[shg]

Another way:

Function GetDigits(ByVal sInp As String, nDigits As Long) As String
    sInp = " " & sInp & " "
    
    With CreateObject("VBScript.Regexp")
        .Pattern = "\D(\d{" & nDigits & "})\D"
        .Global = False
        If .Test(sInp) Then GetDigits = .Execute(sInp)(0).SubMatches(0)
    End With
End Function

      ------------A------------- --B-- --C--- ---D---
  1                                5     6       7   
  2   F23456 H9454555 J232334    23456 232334 9454555
  3   F#23450 H9832444 J893338   23450 893338 9832444
  4   H89232123 F#99999 J#888888 99999 888888        
  5   78734 567788 6788967       78734 567788 6788967
  6   K899932 L89765 J9999999    89765 899932 9999999
  7   abcd 12345                 12345

The formula in B2 and copied across and down is

=GetDigits($A2, B$1)

 

[Fazza]

FYI, approach I used

Function Five(ByVal inp As String) As Variant
 
  Dim i As Long
  Dim ar As Variant
 
  Five = CVErr(xlErrNA)
  ar = Split(inp)
  For i = LBound(ar) To UBound(ar)
    If ar(i) Like "[0-9][0-9][0-9][0-9][0-9]" Then
      Five = CLng(ar(i))
      Exit For
    ElseIf ar(i) Like "*[!0-9][0-9][0-9][0-9][0-9][0-9]" Then
      Five = CLng(Right$(ar(i), 5))
      Exit For
    End If
  Next i
End Function

 

[ericlch16]

is there anyway i can get it without writing an internal function?

 

[barry houdini]

perilously long but this should work where A1 contains the data and B1 the length of the number to be extracted

=LOOKUP(10^B1,MID(SUBSTITUTE(A1," ","x"),ROW(INDIRECT("1:"&LEN(A1)-B1+1)),B1)/ISERR(MID("x"&A1&"x",ROW(INDIRECT("1:"&LEN(A1)-B1+1)),1)+0)/ISERR(MID("x"&A1&"x",ROW(INDIRECT(2+B1&":"&LEN(A1)+2)),1)+0))