MrExcel Publishing
Your One Stop for Excel Tips & Solutions

LEN function


Posted by mcarter on August 28, 2001 2:17 PM

i want to show 1450- as -1450 and 950 as 950. The formula that i am trying doesn't work:

if(right(a1="-",left(a1,len(a1)-1)*-1,left(a1,len(a1)))


Posted by IML on August 28, 2001 2:23 PM

you are very close
=IF(RIGHT(A1,1)="-",LEFT(A1,LEN(A1)-1)*-1,left(a1,len(a1)))

or a little simpler
=IF(RIGHT(A1,1)="-",LEFT(A1,LEN(A1)-1)*-1,A1)

good luck


Posted by Aladin Akyurek on August 28, 2001 2:24 PM

Just use =TEXT(A1,"hhmm") instead. (NT)

Posted by Aladin Akyurek on August 28, 2001 2:32 PM

I need glasses I guess. :(

In case it isn't resolved:

=IF(RIGHT(A1)="-",("-"&LEFT(A1,LEN(A1)-1))+0,A1)