Retrieve 7 digits number

[ElmerS]

Hi,

Assume the following string in a single cell:

ABC 1235674 DE 12 FGH 34567 1235674 IJKL 987654321 1235674 MN

I need to retrieve the 7 digits number which can occur more than once, may be anywhere within the string BUT will always be identical.
(Other numbers may vary and there may be more than one space between the items and there is no constant pattern)

If it is for any help I can retrieve and return the following strings with some add-in:

;1235674;12;34567;1235674;987654321;1235674;
1235674 12 34567 1235674 987654321 1235674

or with any other delimiter.

What will be the fastest and shortest way to return the 7 digits number ?
(If use of the add-in can be eliminated it will be just fine)

Thanks, Elm

[Marcol]

If there is only one set of digits that have 7 digits, perhaps 2, 3, 4 times, then this might do.

=MID(A2,SEARCH(" ??????? ",A2,1)+1,7)

See the attached for further formulae that might help.

[mrice]

Try this user defined function.

Function GetSeven(Cell As Range) As Long
Dim N As Integer, M As Integer
Dim Possible As Boolean

For N = 1 To Len(Cell) - 6
    Possible = True
    For M = N To N + 6
        If IsNumeric(Mid(Cell, M, 1)) = False Then
            Possible = False
            Exit For
        End If
    Next M
    If Possible = True Then
        If InStr(N + 1, Cell, Mid(Cell, N, 7)) > 0 Then
            GetSeven = Val(Mid(Cell, N, 7))
        End If
    End If
Next N
End Function

Open the VBA editor (Alt F11), add a module, paste in the above, back to the sheet (Alt F11) and enter =getseven(A1) etc into B1.

[ElmerS]

Thank you both.

Marcol:
Your formula returns: 3456 from the following first string and: JHGK from the second.

AAAA 12 34567 1234567 SDSDSD 987654321
ABDBDBC JHGK 456 1234567 DDD ABC 63 AAAAAAA

mrice:
Your Functions returns: 0 for both strings.

PS: Please note the variation of spaces !

**************************** EDITED **************************

I JUST NOTED THAT THE EXTRA SPACES ARE TRUNCATED BY THE FORUM INTERFACE

I WILL TRY TO SHOW THE STRING HEREUNDER IN A FEW MINUTES

Elm

[Marcol]

Okay try this

=MID(TRIM(A2),SEARCH(" ??????? ",TRIM(A2),1)+1,7)

PS: Please note the variation of spaces !

This can be difficult as the forum board trims extra spaces unless you put your data examples in tags

Try this workbook

[ElmerS]

Thanks Marcol,

Here is a sample of a string where the first bunch of 7 characters are: ddd-EEE and this is what the new formula returns instead of 1234567:

abcd ddd-EEE     1234567     DE 12  FGH    34567  1234567  IJKL 890     1234567  DSAASSSSMN

Elm

[mrice]

Slight variation to remove spaces before analysis.

Function GetSeven(Cell As Range) As Long
Dim N As Integer, M As Integer
Dim Possible As Boolean
MyString = Cell.Value
MyString = WorksheetFunction.Substitute(MyString, " ", "")
For N = 1 To Len(MyString) - 6
    Possible = True
    For M = N To N + 6
        If IsNumeric(Mid(MyString, M, 1)) = False Then
            Possible = False
            Exit For
        End If
    Next M
    If Possible = True Then
        If InStr(N + 1, MyString, Mid(MyString, N, 7)) > 0 Then
            GetSeven = Val(Mid(MyString, N, 7))
        End If
    End If
Next N
End Function

[Marcol]

Try this modification of one of DonkeyOtes' earlier solutions.

=MID(A2&"0000000",MATCH(TRUE,INDEX(ISNUMBER(MID(SUBSTITUTE(A2," ","^")&"0000000",ROW(A$1:INDEX(A:A,LEN(A2)+1)),7)+0),0),0),7)*1

As you will see from the example workbook, my original attempts fail if there are 7 character alpha codes.

[ElmerS]

Thank you Marcol & mrice and special thanks to DO.

Elm