Using Replace to Format Negative Numbers
 

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?


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200508/1

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.

take a look he


http://www.mcgimpsey.com/excel/postfixnegatives.html


In article ,
"Denise H. via OfficeKB.com" wrote:

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?

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.

JE McGimpsey wrote:
take a look he

http://www.mcgimpsey.com/excel/postfixnegatives.html

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?


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200508/1

Thank you. Appreciate your formula. I also got a posting with this website,
http://www.mcgimpsey.com/excel/postfixnegatives.html. There is an Excel
feature called Text to Columns under the Data menu which easily accomplishes
this.


Dave O wrote:
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.


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200508/1