pulling out 12 digit number from an alphanumeric string

[susan doyle]

Trying to pull out a number from a load of alphanumeric characters.

Example of the cell data: Boundary Walls Replaced 2007-08 Gates and Shed Doors Renewed 2008-09; Referenced from 100012345678 Re-roofing 2001.

Issue is that there are several numbers in the cell and I only need the 12 digit number, in the example above I need the 10012345678 number extracted. I have 2500 lines of data and each line has a different 12 digit number in different places within the cell. I have tried several array formulas I've found on this site however they find the first number in this case 2007 and also bring back text.

I'd appreciate any help on this

Thanks
Sue

[Tony Valko]

Do any of the number strings of interest start with leading zeros?

003456789012
023456789012
000456789012

[susan doyle]

Hi Tony

they either start with
1000******** or
2000********
there are no leading zeros

Hope you can help

[Tony Valko]

Try this array formula**:

=MAX(IF(ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),12)),--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),12)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Format as Number, 0 decimal places

Assumes there will not be other number strings longer than 12 digits.

[susan doyle]

Tony that works a treat... many many thanks

[Tony Valko]

You're welcome. Thanks for the feedback!