On Nov 25, 7:15 pm, "Ron Coderre"
wrote:
If there is a mix of postive and negative values
this may be helpful for a one-time-fix:

1) Set the format to an appropriate number format.
2) Put a 1 in an empty cell and copy that cell
3) Select the range to be impacted
4) <edit<paste special....Check: Multiply....Click [OK]

Now, all of the postive values have become numbers.
The negative values are still text

5) Put a -1 in a cell and copy the cell

Note: you'll be in Copy Mode for the next few steps

6) Select the range to be impacted
7) Press the [F5] key...Click: Special
Check: Formulas
Check: Text.....UNcheck the other options
Click [OK]

Now, only the negative values are selected

8) <edit<replace
Find What: -........that's a minus sign
Replace with: (leave this blank)
Click [Replace All]

Press [ESC] twice....to clear the result message and
the edit/replace window.

NOte: you are STILL in Copy Mode
AND the text values are STILL selected

9) <edit<paste special....Check: Multiply....Click [OK]

Now, all of the negative values have become negative numbers.

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"via135" wrote in message

...



hi

while importing a txt file into xl there is one column which is in txt
format having figures with trailing minus (ex: 100-, 150-,100000-).
how can i change them into real numbers for arithmetical
operations??? added to the head ache is that the column is aligned
horizontally centred!!!

any hlp pl?!

-via135- Hide quoted text -

- Show quoted text -

###7) Press the [F5] key...Click: Special
Check: Formulas
Check: Text.....UNcheck the other options
Click [OK]
Now, only the negative values are selected###

sorry ron..!
i'm getting error "no cells were found"

-via135