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.
