Extract the first and second number in a string and total

[LRopes]

Hi,

I have one column with series of simple subtractions, i.e.:
$5,000-$6,000
$2,500-$3,500
$1,000-$500

The person I work for would like me to create a Total cell that gives the following result:
(total of the first number of each cell)-(total of the second number of each cell).

So, if all we had was the 3 rows above, the result returned in the Total cell would be $7,500-$10,000.

(As an added burden, some of the cells have only a single number, not the equation.)

Is there some formula that can do this?

THANKS!

 

[cruelkix]

Hi,

I have one column with series of simple subtractions, i.e.:
$5,000-$6,000
$2,500-$3,500
$1,000-$500

The person I work for would like me to create a Total cell that gives the following result:
(total of the first number of each cell)-(total of the second number of each cell).

So, if all we had was the 3 rows above, the result returned in the Total cell would be $7,500-$10,000.

(As an added burden, some of the cells have only a single number, not the equation.)

Is there some formula that can do this?

THANKS!

I would take the information and split it up so it is easier to work with.

Edit: Select the colum(s) with the information in it

Go to the "data" tab and use the "text to Columns" function.

Use the Delimited radio button.

Click next.

uncheck "Tab"

Check "other", in the other box place a "-".

Click finish.

You should be able to use standard excel equations from there.

Good luck.

 

[redfox3369]

Using the LEFT and MID formula options you can extract the 1st and 2nd number...just not sure how you want it applied, or how you have your data laid out.

 

[LxQ]

those would be:
=LEFT(A1,FIND("-",A1)-1)
=MID(A1,FIND("-",A1)+1,LEN(A1))

However, this will return a #VALUE if you only one one number without the dash.. in which case you might want to add an =IF(ISNUMBER(A1),A1, formula)

 

[LRopes]

Holy cow I just joined this and I REALLY appreciate the responses. I'm leaving work now but will try this tomorrow (and, re the first suggestion, that's what I wanted to do but I was told that wouldn't be acceptable - hmmm).

Thanks all, will report back tomorrow!

 

[markmzz]

If I understand correctly what you want, maybe this can help you:

Note: array formula - use Ctrl+Shift+Enter and not only Enter.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;">$5,000-$6,000</td><td style="text-align: center;;">$5,000-$6,000</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;">$2,500-$3,500</td><td style="text-align: center;;">$2,500-$3,500</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;">$1,000-$500</td><td style="text-align: center;;">$1,000-$500</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;">-$1,300</td><td style="text-align: center;;">-$1,300</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;">$1,200</td><td style="text-align: center;;">$1,200</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="font-weight: bold;text-align: center;background-color: #D8D8D8;;">Total</td><td style="font-weight: bold;text-align: center;background-color: #D8D8D8;;">$9,700-$11,300</td><td style="font-weight: bold;text-align: center;background-color: #D8D8D8;;">$9,700-$11,300</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;"></td><td style="text-align: center;;">Only TXT</td><td style="text-align: center;;">TXT and Numbers</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*****************</td><td style="text-align: center;;">*****************</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B6</th><td style="text-align:left">{=TEXT(<font color="Blue">SUM(<font color="Red">IF(<font color="Green">ISERR(<font color="Purple">FIND(<font color="Teal">"-",B$1:B$5,1</font>)</font>),1*B$1:B$5,IF(<font color="Purple">ISERR(<font color="Teal">FIND(<font color="#FF00FF">"-",B$1:B$5,2</font>)</font>),0,1*LEFT(<font color="Teal">B1:B5,FIND(<font color="#FF00FF">"-",B$1:B$5,2</font>)-1</font>)</font>)</font>)</font>),"$#,##0"</font>)&
TEXT(<font color="Blue">SUM(<font color="Red">IF(<font color="Green">ISERR(<font color="Purple">FIND(<font color="Teal">"-",B$1:B$5,1</font>)</font>),0,IF(<font color="Purple">ISERR(<font color="Teal">FIND(<font color="#FF00FF">"-",B$1:B$5,2</font>)</font>),1*B1:B5,1*MID(<font color="Teal">B1:B5,FIND(<font color="#FF00FF">"-",B$1:B$5,2</font>),100</font>)</font>)</font>)</font>),"$#,##0"</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C6</th><td style="text-align:left">{=TEXT(<font color="Blue">SUM(<font color="Red">IF(<font color="Green">ISERR(<font color="Purple">FIND(<font color="Teal">"-",C$1:C$5,1</font>)</font>),1*C$1:C$5,IF(<font color="Purple">ISERR(<font color="Teal">FIND(<font color="#FF00FF">"-",C$1:C$5,2</font>)</font>),0,1*LEFT(<font color="Teal">C1:C5,FIND(<font color="#FF00FF">"-",C$1:C$5,2</font>)-1</font>)</font>)</font>)</font>),"$#,##0"</font>)&
TEXT(<font color="Blue">SUM(<font color="Red">IF(<font color="Green">ISERR(<font color="Purple">FIND(<font color="Teal">"-",C$1:C$5,1</font>)</font>),0,IF(<font color="Purple">ISERR(<font color="Teal">FIND(<font color="#FF00FF">"-",C$1:C$5,2</font>)</font>),1*C1:C5,1*MID(<font color="Teal">C1:C5,FIND(<font color="#FF00FF">"-",C$1:C$5,2</font>),100</font>)</font>)</font>)</font>),"$#,##0"</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markimzz

 

[T. Valko]

Hi,

I have one column with series of simple subtractions, i.e.:
$5,000-$6,000
$2,500-$3,500
$1,000-$500

The person I work for would like me to create a Total cell that gives the following result:
(total of the first number of each cell)-(total of the second number of each cell).

So, if all we had was the 3 rows above, the result returned in the Total cell would be $7,500-$10,000.

(As an added burden, some of the cells have only a single number, not the equation.)

Is there some formula that can do this?

THANKS!

I think your left side total should be 8500 not 7500.

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 105px"><COL style="WIDTH: 29px"><COL style="WIDTH: 72px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">$5,000-$6,000</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid">_</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">-$1,500</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">$2,500-$3,500</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">$1,000-$500</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">_</TD></TR></TBODY></TABLE>


This formula entered in C2:

=SUMPRODUCT(LEFT(A2:A4,FIND("-",A2:A4)-1)-MID(A2:A4,FIND("-",A2:A4)+1,10))

 

[LRopes]

OK, here is where things stand now. And thanks everyone!
LxQ - those formulas do extract the numbers which is a GREAT start but when I try T. Valko's formula to give me the Total cell that returns a "0" as a result.
Markmzz when I try that formula I endup with #VALUE! (FYI, I entered the formula in the formula bar by entering ctrl-shift-enter and then pasting in your formula from the = sign through the final parenthesis.
For all, the result Markmzz shows in his table is what I'm aiming for ($9,700-$11,300).

THANKS

 

[LxQ]

just a quick note that markmzz's formula worked for me..

 

[LRopes]

Thanks, don't know what I'm doing wrong. Will keep trying