What formula should I use to change a cell value with - and + signs

Hello all,

I would like to change my cell value, which contains a minus (-) or a plus (+) in it, to a cell with Inflow (for the + signs) and an outflow (for the minus signs). So the Inflow cell should check if the value in column A is plus or minus and insert it into the Inflow or Outflow without the symbols, so I can calculate with the currencies. I have attached my spreadsheet for clarity.

Thanks!

Replace minus and plus sign.xlsx

When I open your file, the values in column A are left aligned (and include commas as the decimal separator) indicating that they are text strings and not numbers. For me, whose system is set to use dot as decimal separator, I first need to replace the comma with a period, but you may not need to do that if your system settings are set to use comma as decimal separator.

Assuming you don't need to do anything special with the text in order for Excel to recognize the text as a number, something like this should work:

1) VALUE() function to convert number stored as text to number
2) ABS(VALUE(A2)) can return the absolute value of the text in A2.
3) NA() or whatever you want when the number isn't copied
4) an IF() function to choose between (2) and (3) =IF(VALUE(A2)>0,ABS(VALUE(A2)),NA()) in column B. Similar in column C, but with less than instead of greater than or with the value_if_true and value_if_false arguments switched.

Hi Luukos,

In B2 put t his formula and pull it down.

Formula:

Please Login or Register  to view this content.

in C2 put.

Formula:

Please Login or Register  to view this content.

and pull it down.

Users with decimal point is komma, like most dutch people, don't need substitute the decimal komma and don't need exclude the first character in the VALUE function:

Users with decimal point is komma, please try in B2 and copy down:

Formula:

Please Login or Register  to view this content.

and in C2

Formula:

Please Login or Register  to view this content.

I was worried that all his cells might not be text and should have tested for that before doing my/our formula(s).

Perhaps a If(IsNumber()) or If(IsText()) should be before the formulas we have above. I was also worried what happens if neither a "+" or "-" was the first character of (hopefully) string. Should we toss in a message telling about the concern?

I put in the Substitute to make the formula work in my language. I did know that commas and periods are language dependent, but I'm always ready to learn new things about Excel. It seems there is lots to still learn.

Thanks.