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

Hi & welcome to MrExcel
How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Splt As Variant
   Dim i As Long
   
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) <> "G11" Then Exit Sub
   Splt = Split(Target, "+")
   For i = 0 To UBound(Splt)
      Target.Offset(, i + 1).Value = Val(Splt(i))
   Next i
End Sub

This needs to go in the sheet module and assumes that your numbers will always be separated by +

 

[luisftv]

Re: Extrat numbers from a cell keeping the quantities together

Thank so much for replying Fluff.

How do I call this call into the target cells? Obviously I do not want it on G11... G11 is where the data that I need to split is located.

Forgive my ignorance if I said something stupid.

Is this code inserted as a MODULE on normal in the Worksheet?

Thanks again.

 

[Fluff]

Re: Extrat numbers from a cell keeping the quantities together

Change the value in red to whatever you want the target cell to be

If Target.Address(0, 0) <> [COLOR=#ff0000]"G11"[/COLOR] Then Exit Sub

Right click the sheet tab you want this to operate on, select view code & paste the code into the window that opens up.

 

[Fluff]

Re: Extrat numbers from a cell keeping the quantities together

Cross posted https://www.excelforum.com/excel-ge...ping-the-quantities-together.html#post5011958

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

especially considering you had already had 2 replies before posting here

 

[luisftv]

Re: Extrat numbers from a cell keeping the quantities together

I APOLOGIZE FOR THAT. I DIDN'T KNOW... I'm at work and didn't have the time to read the long list of rules.

Please, tell me, how can I delete this double posing at this site and how can I delete my account. I've tried even before you mentioned the double posting but I can't find the way to do it.

Again, Sorry.

Cross posted https://www.excelforum.com/excel-ge...ping-the-quantities-together.html#post5011958

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

especially considering you had already had 2 replies before posting here

 

[luisftv]

Re: Extrat numbers from a cell keeping the quantities together

Again,

I tried posting the link... and also this forum just keeps deleting it... it just desperadoes... how can I post the link if the system deletes it?

I am trying to insert it here:

https://www.excelforum.com/excel-ge...ping-the-quantities-together.html#post5011999

I hope this time it shows... I'll know after I post, I guess

 

[Fluff]

Re: Extrat numbers from a cell keeping the quantities together

There is no need to delete this thread, nor your account.
All we (and other sites) ask is that you supply links when you cross post.

 

[Rick Rothstein]

Re: Extrat numbers from a cell keeping the quantities together

Hi & welcome to MrExcel
How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Splt As Variant
   Dim i As Long
   
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) <> "G11" Then Exit Sub
   Splt = Split(Target, "+")
   For i = 0 To UBound(Splt)
      Target.Offset(, i + 1).Value = Val(Splt(i))
   Next i
End Sub

This needs to go in the sheet module and assumes that your numbers will always be separated by +

This one-liner will do what your posted code does...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) = "G11" Then [H11].Resize(, 1 + Len([G11]) - Len(Replace([G11], "+", ""))) = Split([SUBSTITUTE(G11,"=","")], "+")
End Sub[/td]
[/tr]
[/table]

However, given our codes are Change event procedures, neither of them is sufficient as they now stand... entering in an expression with less plus signs than exist in G11 currently will leave remnants of that previous expression in the cells at the end to the right. We need to clear the cells to the right of G11 before hand. Here is my code (no longer a one-liner) modified to do this...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) = "G11" Then
    Range([H11], [H11].End(xlToRight)).ClearContents
    [H11].Resize(, 1 + Len([G11]) - Len(Replace([G11], "+", ""))) = Split([SUBSTITUTE(G11,"=","")], "+")
  End If
End Sub[/td]
[/tr]
[/table]

 

[luisftv]

Re: Extrat numbers from a cell keeping the quantities together

There is no need to delete this thread, nor your account.
All we (and other sites) ask is that you supply links when you cross post.

Thank you Fluff. And again, I do apologize.