Home |
Search |
Today's Posts |
#1
|
|||
|
|||
strip minus signs from right to left
When I import to excel from our accounting system, negative numbers show the
minus sign at the right e.g. 100- instead of -100 or (100) Excel reads this as text and I have to go through the list manually inserting a minus sign at the left and deleting the minus sign from the right. Is there any easier way to do this? The numbers are not a fixed length, so the - could be the third or the thirteenth character in the cell. |
#2
|
|||
|
|||
Found it! Thanks PJB
=IF(RIGHT(A1,1)="-",-SUBSTITUTE(A1,"-",""),A1) "Heather" wrote: When I import to excel from our accounting system, negative numbers show the minus sign at the right e.g. 100- instead of -100 or (100) Excel reads this as text and I have to go through the list manually inserting a minus sign at the left and deleting the minus sign from the right. Is there any easier way to do this? The numbers are not a fixed length, so the - could be the third or the thirteenth character in the cell. |
#3
|
|||
|
|||
On Thu, 21 Apr 2005 09:39:26 -0700, "Heather"
wrote: When I import to excel from our accounting system, negative numbers show the minus sign at the right e.g. 100- instead of -100 or (100) Excel reads this as text and I have to go through the list manually inserting a minus sign at the left and deleting the minus sign from the right. Is there any easier way to do this? The numbers are not a fixed length, so the - could be the third or the thirteenth character in the cell. What version of Excel do you have? Later versions (2002+, possibly earlier) can do this automatically -- easiest if your data to be converted is in columns. Select the relevant column (one column at a time). From the main menu, select Data/Text to Columns Next Next Advanced: Ensure "Trailing minus for negative numbers" is SELECTED. OK Finish If you have an earlier version, or a complicated distribution of numbers with trailing negatives, post back and we can use a VBA routine. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
The left function does not work when displaying times, how is thi. | Excel Worksheet Functions | |||
I want to get brackets round my figures not minus signs | Excel Discussion (Misc queries) | |||
header in right or left margin? | Excel Discussion (Misc queries) | |||
minus formel/fortegn | Excel Discussion (Misc queries) | |||
Importing values w/trailing minus signs | Excel Discussion (Misc queries) |