• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

6. Hacking Formulas and Functions > 73. Convert Numbers with the Negative Sign ...

Convert Numbers with the Negative Sign on the Right to Excel Numbers

Have you ever had to work with imported negative numbers that have the negative sign on the right? SAP is one such program that does this with negative numbers—e.g., 200- instead of -200. Changing these by hand so that Excel understands them can be a hassle, but it doesn’t need to be.

Say you have a long list of numbers you just imported and some of them are those so-called negative numbers. Your job is to convert these to valid negatives that Excel will recognize. For the purposes of this exercise, you will use the range A1:A100. In cell B1, enter this formula:

=SUBSTITUTE(IF(RIGHT(TRIM(A1))="-",RIGHT(TRIM(A1))&A1,A1),"-","",2)+0

PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint