Pick multiple numbers from a text

[ambv]

Hi,

I am trying to pick all the numbers from a text containing mix of numbers, characters, special characters. For Eg. in the below text:
Gsm Clip2 cust is not contactable on both num Sandeep TMCBHCHN_BA80053245 : 31-MAY-2012 08:20:26 AM att1 cust is not contactable on both num Sandeep GSM Clip 9379535419 Mr. PRABHAKAR SINGH cust is disputing for teh waiver in the bill no: 1240722193 for the usage amount as he ahs aldy gvn the complain for the wrong plan act with the sr no: 309057479 alto no : 08066242409 plz chG


Expected output is:
9379535419
309057479
08066242409
1240722193

The numbers with date, time should not be picked. All the numbers with more than 6 digits without any special characters between the digits should be selected. Please help me here.

[JBeaucaire]

Assuming the string is in cell A1 and you want the numbers extracted into A2 and down, this will do that. Note Excel likes to delete leading zeros, so there is a line of code designed to convert these numbers to text so the leading zeros are retained. Remove that if you want them entered as actual numbers, if that matters.

Please Login or Register  to view this content.

[ambv]

This is amazing. Thanks a lot.

[ambv]

I see a problem with this code when the string format is of type:

GSM Clip:8123405666 Del no.

When there is a colon before the number it is not picked. So what should be done to avoid any such special characters?

[ambv]

Also i need to do it for the whole column. The column with many rows with such strings. The output should be moved to a new column with each number listed down under in the same column for each output. Please help.

[JBeaucaire]

Try this, notice there is a support routine as well.

Please Login or Register  to view this content.

[ambv]

This one is clean solution. Thanks a lot.

[JBeaucaire]

I've marked this thread as SOLVED for you.
Next time, select Thread Tools from the links above and mark this thread as SOLVED. Thanks.