Using Replace to Format Negative Numbers

  • Thread starter Denise H. via OfficeKB.com
  • Start date
D

Denise H. via OfficeKB.com

We receive data from an outside source and have no control over its
appearance. On their worksheet negative numbers show up like this $4.33- or
8-. I can use a wildcard character in the search field to find these
occurences but have found that I can't change the format to the proper one, -
$4.33 or -8. In the search field I enter *- but if I enter -* in the replace
field, Excel replaces my number with -*. Suggestions?
 
D

Dave O

One of the gurus will likely respond with a brilliant built-in Excel
function that does this automatically. Until they do, this work-around
will work. Assuming all your data is in column A, add a column, enter
this formula, and copy down to address the entire column of data:
=IF(RIGHT(TRIM(A1),1)="-",MID(TRIM(A1),1,LEN(TRIM(A1))-1)*-1,A1)

This formula checks each entry for the trailing negative sign, and
converts it from a string to a negative number; if the entry is
non-negative and numeric the formula returns the number. You can then
copy this column, paste it as values over your original data, and
delete the inserted column.
 
D

Denise H. via OfficeKB.com

Thank you. My colleague has been struggling with this for years, tediously
fixing each instance. I was sure one of you smart people could help.
 
Top