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.

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

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