Cannot Converting Text to Number

[djeerd2]

My accounting software exports a report to an excel spreadsheet but the all negative numbers export as text format, not number format.

All the negative numbers are a text with negative sign at the end (eg 123.14-). The all positive numbers export as a number.

I need to add up all the negative numbers. I tried copying the cells then pasting them as values but it does not make then numbers.

Any help would be appreciated.

 

[sandy666]

example is not representative but you can try with PowerQuery (Get&Transform)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td][/td][td=bgcolor:#70AD47]Column1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]123.14-[/td][td][/td][td=bgcolor:#E2EFDA]

-123.14​

[/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Column1", type number}})
in
    Type[/SIZE]

 

[Fluff]

Try "Text to columns" > delimited > Next > clear all checkboxes > Finish

 

[Dave Patton]

A few alternatives that you can consider

Excel 2010
	A	B	C
1	123.14-	123.14	123.14
2	123.14-
3	123.14-
4	500.00
5	500.00
6			369.42
7
1e

Cell Formulas
Range	Formula
B1	=--LEFT(A1,LEN(A1)-1)
C1	=--SUBSTITUTE(A1,"-","")
C6	=SUMPRODUCT(--(RIGHT(A1:A5)="-"),(LEFT(A1:A5,LEN(A1:A5)-1)+0))

 

[djeerd2]

A few alternatives that you can consider

Excel 2010

	A	B	C

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]123.14-[/TD]
[TD="align: right"]123.14[/TD]
[TD="align: right"]123.14[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]123.14-[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]123.14-[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]500.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]500.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: right"]369.42[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

1e​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=--LEFT(A1,LEN(A1)-1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=--SUBSTITUTE(A1,"-","")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C6[/TH]
[TD="align: left"]=SUMPRODUCT(--(RIGHT(A1:A5)="-"),(LEFT(A1:A5,LEN(A1:A5)-1)+0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thank You

 

[djeerd2]

Thank you

 

[Dave Patton]

Thanks for the feedback.

 

[DanteAmor]

Another alternative. in column B you have the negative and positive numbers


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">123.14-</td><td style="text-align:right; ">-123.14</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">156.18</td><td style="text-align:right; ">156.18</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >B2</td><td >=IF(RIGHT(A2,1)="-",-SUBSTITUTE(A2,"-",""),A2)</td></tr></table></td></tr></table>

 

[kweaver]

If your values are in the A column, this formula filled down in the B column will fix them.

=IF(RIGHT(A1,1)="-",(-1)*SUBSTITUTE(A1,"-",""),A1)