Retrieve 7 digits number

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

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.

Try this user defined function.

Please Login or Register  to view this content.

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.

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

Okay try this

Please Login or Register  to view this content.

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

Slight variation to remove spaces before analysis.

Please Login or Register  to view this content.

Try this modification of one of DonkeyOtes' earlier solutions.

Please Login or Register  to view this content.

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

Thank you Marcol & mrice and special thanks to DO.

Elm