Adding 2 numbers in the same cell to another defined by a word

[HonorJor]

Hi guys.
I'm really sorry if this question is a common one, and is already answered on the forums. I don't know how to word my question properly in order to make use of search features.

The question:

In Cell A I have "+1 Bananas / -1/2 apples" in cell B I have " -1/2 Bananas / +3 apples" I want to added and subtract Cell A & B together but have them displayed like this:

Cell C: to display the total product for bananas. And Cell D: to display the total product of Apples.
I currently have all of this set into a table. thus why I would prefer not to separate them.

I'm a novice at Excel and VB, but I learn quickly. even just telling me what I should search for, to learn to do this would be a great help. And if this is impossible, could you please suggest a way to fix it so something similar to this would be possible? A

Thank you for the time you spent even just reading this.

Jor.

 

[pgc01]

Hi Jor
Welcome to the board

Remark: this table has really bad design, with so many characters to choose from, why use the same character for 2 different functions: "+1 Bananas / -1/2 apples"

I understand that you have to work with it as is, so try in B2:

=GetQty($A2,B$1)

Copy down and across

I used this udf:

Function GetQty(sText As String, sName As String) As Double

With CreateObject("VBScript.RegExp")
    .Pattern = "[+\-]\d+(/\d+)? (?=" & sName & ")"
    .IgnoreCase = True
   If .Test(sText) Then GetQty = Evaluate(.Execute(sText)(0).Value)
End With
End Function

<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >C</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >D</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >E</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">Bananas</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">Apples</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">Pears</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">+1 Bananas / -1/2 apples</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">-0.5</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">0</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">-1/2 Bananas / +3 apples</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">-0.5</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">3</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">0</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">+4 Bananas / -2 apples</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">-2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">0</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>5</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">-3/5 Bananas / +4 pears</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">-0.6</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">0</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>6</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=6 style="background:#9CF; padding-left:1em" > [Book1]Sheet1</td></tr></table>

 

[HonorJor]

Hi Jor
Welcome to the board

Remark: this table has really bad design, with so many characters to choose from, why use the same character for 2 different functions: "+1 Bananas / -1/2 apples"

I understand that you have to work with it as is, so try in B2:

=GetQty($A2,B$1)

Copy down and across

I used this udf:

Function GetQty(sText As String, sName As String) As Double

With CreateObject("VBScript.RegExp")
    .Pattern = "[+\-]\d+(/\d+)? (?=" & sName & ")"
    .IgnoreCase = True
   If .Test(sText) Then GetQty = Evaluate(.Execute(sText)(0).Value)
End With
End Function

[TABLE="width: 2"]
<tbody>[TR]
[TH][/TH]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="align: center"]D[/TH]
[TH="width: 30, align: center"]E[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Bananas[/TD]
[TD="align: center"]Apples[/TD]
[TD="align: center"]Pears[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: left"]+1 Bananas / -1/2 apples[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]-0.5[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: left"]-1/2 Bananas / +3 apples[/TD]
[TD="align: center"]-0.5[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: left"]+4 Bananas / -2 apples[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]-2[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: left"]-3/5 Bananas / +4 pears[/TD]
[TD="align: center"]-0.6[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="colspan: 6"] [Book1]Sheet1[/TD]
[/TR]
</tbody>[/TABLE]

I am so sorry, I suck at explaining things. I don't actually use the double "/ 1/2" in a single cell, that was me stupidly using it trying to explain things.
I didn't know you could use pictures so...

If that picture works if not imgur: the simple image sharer

In column "Cost" you see the 2-3 numbers Followed by either (mp)(w)(s). I wish to add/subtract that column up to display it like

imgur: the simple image sharer

If you have some learning site that would explain a better format to me instead of wasting more time on my horribly done mistake. I would read that and learn myself.

Sorry again. thank you for your time.
Jor