Extract numbers from a cell keeping the quantities together

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 (to a row of cells, or column) 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.

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.

A Macro will be easiest.

Copy the value of the cell to vba.

Check every character and replace all non numericals with "+"

Replace every "++" with "+" until all replaced

Use the Split function

Please Login or Register  to view this content.

to read the text in to an array.

Write the Array back to excel.

Please Login or Register  to view this content.

Try this. I have given it both ways...

	G	H	I	J	K	L	M	N	O	P
11	2+12+55+1+0+16+555+1	2	2	12	55	1	0	16	555	1
12		12
13		55
14		1
15		0
16		16
17		555
18		1

H11=TRIM(MID(SUBSTITUTE($G$11,"+",REPT(" ",LEN(G$11))),LEN($G$11)*(ROW()-11)+1,LEN($G$11)))
copied down

OR
I11=TRIM(MID(SUBSTITUTE($G11,"+",REPT(" ",LEN($G11))),LEN($G11)*(COLUMN()-9)+1,LEN($G11)))
copied across

Note that in both cases, you may need to adjust the minus values after either ROW() or COLUMN(), depending on where you want the answers to show.

This has been reported as being cross posted elsewhere and as such does not comply with Rule 3 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

Please make sure to include the full link, so that other members will be permitted to continue helping you

Note that if you have fewer than 10 posts, you may need to type the link, or past it in pieces (or ask a mod to do that for you)

As you are a new member, I shall provide the link this time: https://www.mrexcel.com/forum/excel-...-together.html

Please note what Richard has said above and provide cross-post information yourself if you do this again in future. Thank you.

You don't need to delete your account on the other forum. You just need to read and obey the rules of the two forums you have joined: both forums require you to disclose cross-posting.

As a new member to ANY forum, the very FIRST thing you should do is read the rules. Ours are very easy to find - there is a link at the top of this page. Please take a moment to read them now. Thanks.

Thanks Tommy,

I didn't notice that.

Please change the last line to:

Please Login or Register  to view this content.

Originally Posted by Luisftv

Thank you so much FDibbins.

Your awesome formula works (almost) perfectly...

Cell G11 has: 2+12+55+1+0+16+555+1=

Therefore, that "=" sign is added to the last number... I need that = sign gone...

Other than that is awesome...

Try these...
H11=SUBSTITUTE(TRIM(MID(SUBSTITUTE($G$11,"+",REPT(" ",LEN(G$11))),LEN($G$11)*(ROW()-11)+1,LEN($G$11))),"=","")
or
I11=SUBSTITUTE(TRIM(MID(SUBSTITUTE($G11,"+",REPT(" ",LEN($G11))),LEN($G11)*(COLUMN()-9)+1,LEN($G11))),"=","")

Regarding the duplicate posts on other forums, we are not affiliated, but many of us are members on more than 1 forum - it's not a big deal at all The problem comes in when a member posts the same question in multiple forums, but does not share a link to those other forums.
When that happens, members on each forum could be trying to assist you, but you may already have an answer from 1 of the forums. That means that all the other members trying to help you, have wasted their time on you. Providing links helps eliminate that

Happy to help, thanks for the understanding and feedback

On a side note, there is no need to quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

For normal conversational replies, try using the QUICK REPLY box below.