Extract numbers from a cell keeping the quantities together

[luisftv]

Thanks you in advanced.

I have a cell (say G11) which contains the following: 2+12+55+1+0+16+555+1=

I need to extract all the numbers (automatically, with a formula or VB) into different cells keeping each quantity in it's own cell, for example:

2 will be in it's own cell
12 will be in it's own cell
55 will be in it's own cell
1 will be in it's own cell
and so on...

Obviously, I do not want the "+" or "=" or any other part of the string... just the quantities.

This has to be in place so that it changes on it's own without user input... the only cell that will have user input is the first one that contains everything.

In other words, is there a formula (or array formula) or VB code that can be added once and be done with it. Remember, the original cell will change it's contents every now and then... therefore, it has to be automatic... no pressing of keys or buttons to make it happen.

I've tried with LEFT, MID, RIGHT, VLOOKUP, etc... but LEFT only does one number starting from the left, RIGHT only get the whole number from the right, and MID only one number in the middle... but as you see, I have more numbers than just 3 quantities.

Thank you so much.

 

[Fluff]

Re: Extrat numbers from a cell keeping the quantities together

Thank you Fluff. And again, I do apologize.

Thank you

no longer a one-liner

No one liner! What can I say

 

[Rick Rothstein]

Re: Extrat numbers from a cell keeping the quantities together

No one liner! What can I say

By the way, that is a great smiley!

Just a reminder... don't forget to update your code for the OP to delete cells to the right of G11 before distributing the split apart values.