|
|
|
|||||||
| Excel Questions All Excel/VBA questions - formulas, macros, pivot tables, general help, etc. Please post to this forum in English only. |
 |
|
|
Thread Tools | Display Modes |
|
|
Mar 30th, 2004, 10:38 AM
|
#1 |
|
Join Date: Mar 2004
Location: London
Posts: 10
|
Negative numbers viewed as text
Hi
I have some figures exported out of another application, which puts the negative sign at the end of the number. Excel then views this number as text. Anyone any ideas how I can convert these numbers to read as a negative in the proper format? Thanks in advance Nic |
|
|
|
Mar 30th, 2004, 11:02 AM
|
#2 |
|
MrExcel MVP
Join Date: May 2003
Posts: 8,456
|
Re: Negative numbers viewed as text
Welcome!
You could try this formula: =SIGN(RIGHT(A1,1)&1)*SUBSTITUTE(A1,"-","")
__________________
"Fair Winds and Following Seas" |
|
|
|
|
Mar 30th, 2004, 11:03 AM
|
#3 |
|
MrExcel MVP
Join Date: Jul 2002
Posts: 32,030
|
Re: Negative numbers viewed as text
You can use this formula:
=(SUBSTITUTE(A1,"-","")+0)*IF(RIGHT(A1,1)="-",-1,1) where A1 contains the imported number. |
|
|
|
|
Mar 30th, 2004, 12:13 PM
|
#4 |
|
MrExcel MVP
Join Date: May 2002
Posts: 12,561
|
as I recall, there's also an option for this in the asap addin...
|
|
|
|
|
Mar 30th, 2004, 01:27 PM
|
#5 |
|
Join Date: Dec 2002
Posts: 677
|
Re: Negative numbers viewed as text
Try to use Text to Columns function :
Highlight your figures column>> Data>> Text to Columns…>> Next>> Next>> Finish which will change the end negative sign number to a negative number Regards |
|
|
|
|
| Bookmarks |
| Thread Tools | |
| Display Modes | |
|
|
Hybrid Mode
