Change formatting for negative number

  • Thread starter Thread starter Jasmine
  • Start date Start date
J

Jasmine

I am importing a text file that has a row of currency. So
of the entries are negative, but are entered like 1.25-.
Excel seems to be recognizing this as a text file instead
of a negative number. Is there a macro I can write or a
find/replace that can be run to convert this to a true
negative number? I would appreciate any advice or help.
Thanks!
 
Jasmine

Do the following
1. Sort the rows so that you can separate the positive numbers from the negative numbers (the ones formatted as numbers, the positive ones, should sort separately as numbers from the negatives which will sort as text)
2. Highlight the negative numbers, and do a find/replace: find "-" and replace with "". This should convert all of them to positive numbers
3. Create a column next to these newly positive numbers that have to now be negative. Assume column A is the number you need to convert to a negative, and column B is your new column. Your formula for each cell is: B = A-(2*A). This will give you Column A but as a negative number

You can then re-sort all the numbers if you need to, but you should be good to go

----- Jasmine wrote: ----

I am importing a text file that has a row of currency. So
of the entries are negative, but are entered like 1.25-.
Excel seems to be recognizing this as a text file instead
of a negative number. Is there a macro I can write or a
find/replace that can be run to convert this to a true
negative number? I would appreciate any advice or help.
Thanks
 
Jasmine

If using Excel 2002 see Data>Text to Columns>Step 3>Advanced. Checkmark
"trailing minus for negative numbers"

Gord Dibben Excel MVP
 
Back
Top