Recognize Negative Number

**Bob@Sun** · 01-28-2010, 06:14 PM

Hello,

How can I make excel to recongnize this: 123456- as a negative number????

Thanks for the help

**Paul** · 01-28-2010, 06:17 PM

Hi Bob,

You can use a formula, such as:

=(RIGHT(A1,1)&LEFT(A1,LEN(A1)-1))+0

This creates a text string of, for example, -123456, and then adds 0 to it to convert it to a number within the cell.

**zbor** · 01-28-2010, 06:21 PM

How about this:

=IF(ISNA(FIND("-",A1)),A1,0-SUBSTITUTE(A1,"-",""))

**Paul** · 01-28-2010, 06:25 PM

ZBor, what is the "&D2" at the end of your formula for?

**zbor** · 01-28-2010, 06:28 PM

Nothing

Sorry, moved with arrows...

**Bob@Sun** · 01-28-2010, 06:33 PM

Well Paul's formula works fine, but mess up the number if there is no "-" after the number. Can you please fix that?

I can not understand zbor's function: =IF(ISNA(FIND("-",A1)),A1,0-SUBSTITUTE(A1,"-",""&D2))

Why is that D2 in it. what do you have in this cell?

**Paul** · 01-28-2010, 06:35 PM

Just remove the "&D2" bit. Was an accidental key-press. To use mine, you would need to change it to:

=IF(RIGHT(A1,1)="-",(RIGHT(A1,1)&LEFT(A1,LEN(A1)-1))+0,A1)