MrExcel Publishing
Your One Stop for Excel Tips & Solutions

importing data with negative sign at end of no.


Posted by Kara on October 12, 2001 11:45 AM

I imported data into excel that had the negative sign at the end of the no. (ie. 100-) . I tried to set up a macro to put the - sign in the front of the no. but was having trouble with referencing the cell.
the steps I took were
1. Find "-"
2. F2
3. Backspace (to delete -)
4. Home
5. "-"
6. Enter

But when I would run the macro, it would find the next -, but it would not perform the function in the active cell, but input the no. that was in the cell I recorded the macro from.

Please help!!
Thanks


Posted by Caleb on October 12, 2001 11:46 AM

Sounds like you need to have the "relative reference" button on when you're recording your macro. It's a button that is on the record macro toolbar. When it is depressed, it will record the action without mapping it to a specific cell. Try doing that and it should work.

Posted by IML on October 12, 2001 11:50 AM

A part of the free add in at asap-utilities.com will do this for you. Also, you could use the following formula, paste special values over it and delete the original column.

=IF(ISNUMBER(A1),A1,LEFT(A1,LEN(A1)-1)*-1)

Good luck

Posted by Kara on October 12, 2001 12:10 PM

I tried that and it did not work. What coding do I use to alter a cell by adding text to the end of it or at the beginning? What I really need to know is how to put an = sign at the beginning of the no. to make it a formula

Posted by Kara on October 12, 2001 12:11 PM

THANK YOU!! If I would have known that yesterday, I would have saved about 4 hours of manual work.