left and right function
 

I have data that looks like this

4.23-

100.23-
10121.23-

I need make this a number. so that it can be used in formulas
I have tried to multiple it by 1 to convert it to a number, but this
does not work.
I thinking i have to write a formula that will extract all data left of
the - sign.
What would that formula be.


Thanks.

left and right function
 

Hi there,

You can use something like this ...

=LEFT(A1,LEN(A1)-1)

If you want it to be truly negative ..

=LEFT(A1,LEN(A1)-1)*-1

If you want this to work with all numbers regardless if they have a - sign
at the end of it ..

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

HTH

--
Regards,
Zack Barresse, aka firefytr



wrote in message
oups.com...
I have data that looks like this

4.23-

100.23-
10121.23-

I need make this a number. so that it can be used in formulas
I have tried to multiple it by 1 to convert it to a number, but this
does not work.
I thinking i have to write a formula that will extract all data left of
the - sign.
What would that formula be.


Thanks.

left and right function
 

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

wrote in message
oups.com...
I have data that looks like this

4.23-

100.23-
10121.23-

I need make this a number. so that it can be used in formulas
I have tried to multiple it by 1 to convert it to a number, but this
does not work.
I thinking i have to write a formula that will extract all data left of
the - sign.
What would that formula be.


Thanks.