Clean up numbers/text in column with macro

Hi all,

I am trying to create a macro/VBA to clean up a column of batch numbers in an excel sheet (can be >1000 rows).
I need it to delete all plain text such as explanatory text and more and just return the batch numbers in a uniform matter such as "0000123456", "M1234", "1234-056789", "-" or "0000123456; 0000123456"

I have attached an example below.

As this is my first post here, please let me know if anything more is required :-)

Thank you very much in advance.

With kind regards,
Anders

example.jpg

Originally Posted by 6StringJazzer

Your batch numbers seem to be all different formats and include non-numeric characters. How do you tell what is explanatory text and what is a batch number?

Hi 6StringJazzer and thank you for your reply.

Basically batch numbers are either in the format A1234, 1234-1234567, or numbers with lengths of 10 or 12 digits. The first two should be as they are, as should the ones with LEN = 12. However all other numbers in the string with LEN from 5 to 10 should be returned with 0’s before so they all have LEN = 10 so 12345 => 0000012345.

Does that make sense?

I will take a look at this but I would like your test data in an Excel file, not an image.

The paper clip icon does not work for attachments. Instead, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

Thank you very much :-) It is much appreciated - and sorry for the late response (I thought I would get an email notification of new actions on the thread)

I'll take a look. You should be getting email notifications if your profile is set up for it. Check General Settings in your Profile.

subscribeemails.JPG

I think that your description covers most of the cases but remember, you are using your human judgement to convert these numbers, and we need an algorithm. So, for example,

M3876, EXPIRY DATE: 31-AUG-2016

Might come out looking like

M3876; 000000000031; 000000002016

When there are multiple batch numbers in the same cell, sometimes they are separated by ";" and sometimes ",". What are the rules for this? Are there other formats where there can be more than one per cell?

Thank you :-) There are in general no rules, but I have tried to dig out the most common types. They can be seperated by ";" or "," or "/". But I can probably modify the final output if needed :-)

Here is the solution, based on your description and examples. I assumed 1234567-1234 is a valid pattern.

I can not thank you enough :-) That is just working like a charm! And the 1234567-1234 pattern is indeed also a valid pattern :-) Thank you so much for all the help 6StringJazzer

Glad to help, it was an interesting problem.