H2 Convert number with negative sign on right

Mar 6th, 2002, 10:36 AM

How do you convert downloaded data where the negative sign appears on the right side of a number as opposed to the left? Excel is recgonizing it as text. I tried to format the cell to a number but excel is still reading it as text. i.e. 92.50- needs to be converted to -92.50.

Mar 6th, 2002, 10:45 AM

=VALUE("-" & LEFT(A1,LEN(A1)-1))

IML · Mar 6th, 2002, 10:49 AM

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

if you have positives and negatives in your list.

Mar 6th, 2002, 10:49 AM	#3
IML MrExcel MVP Join Date: Feb 2002 Location: Denver, CO Posts: 1,744	or =IF(ISNUMBER(A1),A1,LEFT(A1,LEN(A1)-1)*-1) if you have positives and negatives in your list. __________________ Give $3 to Mr Excel's Holiday Charity Event! Write Up http://www.mrexcel.com/board/viewtop...0916&forum=9&4 Dontate http://www.mrexcel.com/sunshop/index...bstart=0&id=18

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 6th, 2002, 10:36 AM	#1
Guest Posts: n/a	How do you convert downloaded data where the negative sign appears on the right side of a number as opposed to the left? Excel is recgonizing it as text. I tried to format the cell to a number but excel is still reading it as text. i.e. 92.50- needs to be converted to -92.50.

Mar 6th, 2002, 10:45 AM	#2
Guest Posts: n/a	=VALUE("-" & LEFT(A1,LEN(A1)-1))