Formula needed to extract numbers from alpha numerical entry please

Hi

I'm hoping someone can help with a problem I have. I have a sheet with thousands of rows of ID's which are made up of a series of letters, numbers, underscores and or spaces. Within the sheet there are numerous duplicates so I need to able extract the numerical element, which is the key data to be able to identify the duplicates.

The numbers I need to extract are mainly 6 and 7 digits long however don't always sit in the same place within the ID. I don't need the 3 digit number sequences, just the 6 & 7 number sequences

e.g

AB_123_456789_Jones
CD_131_7654321_Smith
AB_123_Jones_456789

I've found a formula that strips out all the numbers, however it also included the 3 digit numbers which I don't want. I've also used Text To Columns to break up the ID across a number of columns however that leaves me with 2 and sometimes 3 columns where a 6 digit ID appears

Is there a simple way to extract the number if it's a certain length? i.e. 6 or 7 digits long

I hope that all makes sense and I've provided sufficient information

Many thanks in advance

Greg

Hi Robert

Many thanks for your very quick response

The formula works brilliantly for the most part, there is one slight problem which is my fault as I've just noticed something I didn't mention in the original post so many apologies.

There are also some entries where it only includes alphabetical characters, the 3 digit numerical code and a colleague name

i.e. GL_131_G Clark

Is there a way to amend the formula to only return numbers of a certain length, or perhaps easier to exclude 3 digit numbers?

Many thanks

Greg

Hi

Yes the 3 digit code always appears in the same position

GL_131_E FULTON
GL_131_B Slowey
GL_131_P RYBAK

Thanks

Greg

Hi Daddylonglegs - That formula works excellently thank you!

Hi Ron - There are occasions where a 7 digit number could begin with a zero so unfortunately I wouldn't be able to use it

Just to add to the thread and plea for help if I may, on the entries where there is no 6 or 7 digit number, just the leading 2 letters, the 3 digit code plus a colleagues name, is there a simple way to extract the name element so I could also check for duplicates there?

i.e.

GL_131_G Clark
GL_131_J MCCREADY
TB_GAQ_T ALICE_Green

From what I can see looking down the list, where there is no 6/7 digit number, the name always starts in the same position, namely 8 characters from the left. Mostly with the initial first followed by a space or underscore, and then the surname. There are also a few instances where the surname is first followed by the initial although not sure if that would impact any formula

Many thanks again for all your help with this guys

Greg

Genius Daddylonglegs!

Works a treat and has highlighted over 900 duplicates in the sheet which is what I was hoping for

Thanks so much for all your help guys, keep up the great work

Thanks

Greg

Hello all

Unfortunately daddylonglegs' formula doesn't work when used at work as we have 2007 which I failed to mention on my original post, I hadn't considered it to be honest so apologies for the schoolboy error.

Is anyone aware of a formula I can use on 2007 that would return the 6 or 7 long number, or the colleague name and ignore the 3 digit number?

Many thanks

Hi Alkey

Thanks for your reply

You're going to have to forgive my vba knowledge however how do I apply the code to the sheet to return the results?

I've copied the code into the editor however I'm not altogether sure what else I need to do

Will the code also work if there isn't a 6 or 7 digit number? Where there isn't a 6/7 digit number I need to return the name instead?

i.e.

GL_131_G Clark
GL_131_J MCCREADY
TB_GAQ_T ALICE_Green

Many thanks

Greg

Hi

Not sure what I'm doing wrong however it's returning '#value!', should the code work on 2011 as well as 2007

This is what I'm doing...

Data starts in A1 on my sheet
Opening Vb editor, clicking insert module, pasting the code from your original post in and closing Vb editor
Copying and pasting the formula on your 2nd post into the cell where I want the number/name to populate which is when I get #value!

Any thoughts? Anything obvious I'm doing wrong?

Thanks

Hi
Thanks for that, it's also showing #value! when I open the sheet, could it be the version of Excel I'm using at home which is 2011 for the Mac?
Thanks

Macro's are enabled. A pop up box appears when you open a sheet containing macro's asking if you want to enable them which I did. I've also just tested with a basic macro and it worked fine so really don't know.

Can code differ between Excel versions do you know or is it universal and should work across all platforms?

Alkey - I'll test the code in work tomorrow and hopefully it should work ok

Thanks for all your help