Looking for a way to extract multiple numbers from text

[kenhunter]

Greetings everyone,
my knowledge of macros is just enough to be dangerous, I know there has to be an easy way to do this. I have a spreadsheet that has text which includes retail SKU numbers. There is no pattern to the text and varies greatly. It could have only one number or could have 10. Below is an example of one entry;

on hand for new stock sku# , 959801 , 939473, also 939506, 717845, 717713, 817472, non onhand 939539, 939902, 813858

What I need is a way to extract these SKU numbers out and drop in the cells to the right as individual numbers. So if the above information is in cell A1, then what I'm looking for is A2 = 959801 A3 = 939473 A4 = 939506 A5 = 717845 and so on.

I am using Excel 2010 on Win 7 platform.
Any help is appreciated.

Thanks and have a great day!!!!
Ken

[K m]

All of your example contain numbers only. Are there any "non-number" examples of the data?

[kenhunter]

If I understand your question right, the information I need to extract will be purely numeric.

[martindwilson]

in b1

Please Login or Register  to view this content.

in c1

Please Login or Register  to view this content.

drag across to right as many cols as you are likely to need this assumes the sku is 6 digits also no sku is repeated in the string

[kenhunter]

Thanks Martin,
Thanks for the formulas!! I appreciate it, I just now had a chance to try this but found a scenario I wasn't aware of. I'm finding some of the SKU data is loaded in a format with a "-" dash. So 959801 is also loaded in some places as 959-801. Any suggestions? Thanks

[martindwilson]

b1

Please Login or Register  to view this content.

c1

Please Login or Register  to view this content.

[kenhunter]

Thanks Martin,
That's PERFECT !!! You are awesome. It works like a charm, I really appreciate your help.
Have a great day and thank you

[Cutter]

If you are satisfied with the solution(s) provided, please remember to mark your thread as Solved.

New quick method:
Select Thread Tools (above first post on page) -> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
Go to the first post
Click edit
Click Go Advanced
Just below the word "Title" you will see a dropdown with the words "No prefix".
Change to "Solved"
Click Save

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.