Extract digits


Worksheet "Prod issues Report 04022019"

Tab "Full"

Column K - This column has 6-12 character numbers in them that i need to extract into column S. Some numbers start with IL or AB and also can end with t. 

Is there a formula or VBA Code (even better) to extract these numbers with this criteria into column S with the corresponding Row. 



Selected Answer


I have had a quick look at your problem. The trick is there does not appear to be any simple rule to Identify the code.(we won't call it a number)

In a number of cases you could take all the characters to the first blank space. You would needs some rules around th hyphen. and I have noticed that sometimes the hyphen has a space around it.

The next chanlenge is where the number is not first but appears somewhere in the text.

It is a job for artificail intaligence.

I would try using a macro and use the split command on the space then look at each element and choose the one which has at least 6 numbers. If you need the hyphen check the next element and concatenate if required. 

I have added a function to your excel which does some stuff



The only criteria I have is that I know it will be:

between 6 and 12 characters
Sroncey21 (rep: 62) Apr 3, '19 at 5:32 pm
Hi. the function I have added uses the addtional criteria.
The field will be terminated with a blank 
It may consists of letters and numbers.
It will have at least 6 numbers
If there is a hyphen split at the hyphen and keep the biggest bit.

You also have criteria around the start of the field where there is a #, which I have not coded. I also have not coded to ignore fields with more than 10 characters. 
I hope this is of some use. 
I think there are enough code samples for you to be able to complete your request.
As always, should you get stuck, post a new request to the forum.
k1w1sm (rep: 177) Apr 4, '19 at 2:55 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login