How to Convert Trailing Minus and Parentheses Negatives in Excel

Some exports write a negative number with the minus sign at the end (123.45-), or put it in parentheses ((123.45)). Both are easy for a person to read. Whether Excel treats them as numbers is a different question.

This guide shows what we observed when we opened one small test CSV in several ways, and three methods that turned the stubborn values into real numbers.

Tested in: Excel 2021 desktop (Version 2608) with an English interface, on Windows with Korean regional settings (decimal point ., thousands separator ,). Everything below describes what happened on that one setup. We did not test other regional settings (for example a decimal comma), currency symbols, other Excel versions, or Mac, and results may differ there.

Our Test File

The CSV has five rows and three amount columns. In each row, the three columns mean the same number:

Column	Example (first row)	Meaning
amt_trailing_minus	123.45-	-123.45
amt_parentheses	(123.45)	-123.45
amt_leading_minus	-123.45	-123.45

The five intended values are -123.45, -1234.56, -0.5, -100, and 7500. The last row, 7500.00, has no sign and is our plain-number check. In the file, the amounts in row 2 contain a thousands comma, so that row’s values are written in quotes.

Opening the CSV Directly

We opened the file directly in Excel, not through the Data tab. The three columns did not behave the same way:

Tap the image to view it at full size.

• The trailing-minus values (123.45-) stayed left-aligned, as text. Our added ISNUMBER check returned FALSE for them.
• The parentheses values and the leading-minus values were right-aligned numbers, and ISNUMBER returned TRUE. With C2 selected, the formula bar shows -123.45, although the file text was (123.45).
• The plain 7500.00 row came in as the number 7500.

In this test, the trailing minus was the only one of the three that Excel left as text. We only observed this on our setup, so do not assume it for every file or version.

Importing With Data > From Text/CSV

Next we imported the same file with Data > From Text/CSV and opened Transform Data. Power Query had already added a type-conversion step on its own. We saw it listed in the Applied Steps pane, which is not part of the screenshot below; the screenshot shows only the formula bar and the data.

Tap the image to view it at full size.

The formula bar shows the three amount columns set to type number. All three columns, including the trailing-minus one, show the intended values: -123.45, -1234.56, -0.5, -100, and 7500, with no error cells.

So in our test the same file ended up as numbers through this import path, while the direct open left one column as text. If you handle exports like this regularly, it is worth checking how the file was opened. We did not load this query to a worksheet.

When the Values Are Already Text: VALUE() Fails on a Trailing Minus

To test text that is already in cells, we loaded the same CSV through Power Query without the type step, so all three columns arrived as text. Then we tried =VALUE() on each cell. The VALUE and ISTEXT columns on the right are helper formulas we added.

Tap the image to view it at full size.

• =VALUE(B2) on the trailing-minus text returned #VALUE! in rows 2 to 5.
• =VALUE(C2) on the parentheses text returned -123.45, and the other rows returned the intended negatives.
• =VALUE(D2) on the leading-minus text returned the intended negatives.
• ISTEXT was TRUE for all 15 amount cells, and the 7500.00 row converted to 7500 in every column.

That VALUE() read parentheses as a negative number is what happened on our setup. We did not test whether it does on others.

Method 1: Text to Columns With “Trailing minus for negative numbers”

If the trailing-minus values are already in the sheet, Text to Columns can convert them in place. We selected B2:B6 and used Data > Text to Columns > Delimited > Next > Next. In step 3, we clicked Advanced. The box “Trailing minus for negative numbers” was already ticked when the dialog opened; we did not tick it ourselves.

We clicked OK and Finish, leaving the delimiter options in step 2 as they were. The text in B2:B6 was replaced by numbers:

Tap the image to view it at full size.

The column now shows right-aligned -123.45, -1,234.56, -0.5, -100, and 7500, and ISTEXT for that column is FALSE. The thousands comma in -1,234.56 suggests Excel applied a number format during the conversion; we did not check which one. Because this overwrites the original cells, keep a copy of the column first.

Method 2: A Helper Formula

If you want to keep the original text, a formula in a new column does the same job. For text like 123.45- in B2, we used:

=IF(RIGHT(B2,1)="-",-VALUE(LEFT(B2,LEN(B2)-1)),VALUE(B2))

It checks the last character. If it is -, it removes that character, converts the rest with VALUE, and flips the sign. Otherwise it converts the text as it is, so a plain 7500.00 still works.

For parentheses text like (123.45) in C2, we also tried:

=IF(LEFT(C2,1)="(",-VALUE(MID(C2,2,LEN(C2)-2)),VALUE(C2))

Both were filled down to row 6. On our setup VALUE alone already handled the parentheses (see above), so the second formula was optional there. We include it because other setups may not behave the same, and we did not test them.

Tap the image to view it at full size.

The result columns show -123.45, -1234.56, -0.5, -100, and 7500, the same five intended values as in the file. The formulas are not visible in the screenshot, so use the text above.

Method 3: Set the Column Type in Power Query

For a file you import again and again, set the type in Power Query instead. In the query that loaded the amounts as text, we selected the three amount columns and used Home > Data Type > Decimal Number.

Tap the image to view it at full size.

The formula bar shows the three columns converted with type number, and the preview shows the intended values with no error cells. The id column stayed as text because we did not select it. This screenshot is the Power Query preview, not the loaded sheet, and we did not check the query’s locale setting.

Which Method to Use

Situation	Method we would try first
Values are already in the sheet and you want to replace them	Text to Columns
You want to keep the original text beside the result	Helper formula
You import the same kind of file repeatedly	Power Query column type

All three worked on our test file on our setup. For other ways to convert text to numbers in bulk, see converting numbers stored as text in Excel.

Things to Know

• Scope. Everything above is from one five-row test file on Excel 2021 (Version 2608) with Korean regional settings. We did not test a decimal comma, currency symbols, other versions, or Mac.
• Overwriting. Text to Columns replaced the original cells in our test. Work on a copy.
• Our helper cells. The ISNUMBER, ISTEXT, VALUE, and fix columns in the screenshots are cells we added for testing; they are not part of the CSV.

One-line Summary

In our Excel 2021 test, a trailing minus (123.45-) was the only one of three negative formats left as text when the CSV was opened directly, VALUE() failed on it, and Text to Columns, a helper formula, or a Power Query number type each turned it into a real number.

Sources

Every value and screenshot on this page comes from our own reproduction in Excel 2021 on one PC. We did not rely on external documentation for any claim here.