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)

Column1		Column1
123.14-		-123.14​

[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
1	123.14-	123.14	123.14
2	123.14-
3	123.14-
4	500.00
5	500.00
6			369.42
7

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

1e​

Worksheet Formulas Cell Formula B1 =--LEFT(A1,LEN(A1)-1) C1 =--SUBSTITUTE(A1,"-","") C6 =SUMPRODUCT(--(RIGHT(A1:A5)="-"),(LEFT(A1:A5,LEN(A1:A5)-1)+0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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)