CODEWITHSUNDEEP
excel
Roope
Roope

Reputation: 4507

Convert numbers with parentheses around to negative numbers

I have Excel data that has parentheses around negative values, but Excel does not see them as negative values, it seems to take them as text. E.g. trying to sum cells that have ($25.00) and $50.00 gives #VALUE! error.

The data is imported from a csv file.

It's not a formatting issue, changing negative value formatting does not change anything. So, how do I convert these values to actual negative numbers?

Thanks.

Here is some sample data in case someone wants it. So the values in parentheses are supposed to be negative numbers but seem to be text at the moment.

($25.00)
 $50.00
($35.00)
($15.00)

Upvotes: 1

Views: 4467

Answers (4)

Josh Young
Josh Young

Reputation: 1

Create two columns and create a heading as the first row is excluded. One for Withdrawals and one for Deposits for bank transactions for example. Use the formulas before for the Deposit column and for the Withdrawn column for all rows.

Be sure the formatting is set to Currency instead of Accounting to remove the parenthesis for negative currency entries.

Withdrawn Column: =IF(B2<1,B2,"")

Deposit Column: =IF(B2<1,"",B2)

Upvotes: 0

Mayank
Mayank

Reputation: 342

Complete syntax for converting numbers like ($6,437.55) to money data type:

CASE WHEN IsNumeric(Replace(Replace(Replace([INPUT-COLUMN-NAME], ',', ''),')',''),'(','-')) = 1 Then Cast(Replace(Replace(Replace([INPUT-COLUMN-NAME], ',', ''),')',''),'(','-') AS Money) ELSE 0 END As [OUTPUT-COLUMN-NAME]

This can be used directly in SQL statement like Select ColumnA, CASE WHEN as above, ColumnC From Table.. join etc.

You can also define a function like (which comes with a performance penalty though) and use function in select statement.

CREATE FUNCTION dbo.ConvertStringToMoneyType(@input_Number varchar(50))
RETURNS MONEY
AS
BEGIN

Declare @Output_Number money = 0

Set @Output_Number = CASE WHEN IsNumeric(Replace(Replace(Replace(@input_Number, ',', ''),')',''),'(','-')) = 1 Then 
        Cast(Replace(Replace(Replace(@input_Number, ',', ''),')',''),'(','-') AS Money) ELSE 0 END

RETURN @Output_Number


END
GO

Upvotes: 0

Dick Kusleika
Dick Kusleika

Reputation: 33165

Type 1 into an unused cell. Copy it. Select the numbers that are stored as text and select Home - Clipboard - Paste - Paste Special - Multiply. Delete the 1.

Upvotes: 1

pnuts
pnuts

Reputation: 59485

Try replace $ with nothing, ) with nothing and ( with -.

Upvotes: 1

Related Questions