Macro to separate PO Numbers in cell and enter into separate cells

Hi,

What I have is a list of PO Numbers in a column, however, some cells have one or more PO numbers and some cells have additional text. The PO numbers are all the same length i.e. 10 characters e.g. 4500038225 (N.B. some cells in the column may be blank i.e. have no PO numbers entered)

What I would like is a macro that which remove any text and separate all the PO numbers and put them in to the corresponding cells in a separate column (please see attached example file where I have highlighted rows 2, 6 and 10 as examples)

Any assistance on this matter would be greatly appreciated

Many thanks in advance

Regards

Rob

Jose,

many thanks for your response, it works only if there is no additional text in the cell !

Any ideas how to resolve

Regards


Rob

Hi Rob
Try this one
Enter formula in B2 and copy across and down

Formula:

Please Login or Register  to view this content.

Or a bit shorter version

Formula:

Please Login or Register  to view this content.

Please see attached file

Alkey,

many thanks for your response it is very much appreciated, however, could your code be refined a little as whilst it does what I wanted, when I tested it I just entered text into column 'A' instead of a PO number and/or PO number and text and it returned the text into column 'B'

Any further assistance would be greatly appreciated

Many thanks

Regards


Rob

Originally Posted by robertguy

Alkey,

many thanks for your response it is very much appreciated, however, could your code be refined a little as whilst it does what I wanted, when I tested it I just entered text into column 'A' instead of a PO number and/or PO number and text and it returned the text into column 'B'

Any further assistance would be greatly appreciated

Many thanks

Regards


Rob

I don't think I understand your request. I would suggest to upload a spreadsheet and show the desired results.

AlKey

please see the attached file, where I have entered only text into cells A3, A4 and A5 as opposed to a PO number or a PO number and text. As you can see the text in A3, A4 and A5 is populated into B3, B4 and B5. Is there away to modify your code only to populate the PO number

Many thanks

Regards

Rob

Try this

Formula:

Please Login or Register  to view this content.

AlKey

EXCELLENT ! Just what I wanted, many many thanks for your assistance

Thanks again

Regards


Rob