I have a spread sheet that instead of showing negaive 100 as -100, it shows
negative 100 as 100-, is there any way to convert 100- to -100 by formular?
Thanks.

Data/ Text to Columns
--
David Biddulph

"How to convert from 100- to -100"
m wrote in message
...
I have a spread sheet that instead of showing negaive 100 as -100, it shows
negative 100 as 100-, is there any way to convert 100- to -100 by
formular?
Thanks.

Since you asked to convert "100-" to -100 via formula,
try this

With numeric text in A1
B1: =SIGN(--(RIGHT(L3,1)&1))*SUBSTITUTE(L3,"-","")

Does that help?

Regards,

Ron Coderre
Microsoft MVP (Excel)

"How to convert from 100- to -100"
m wrote in message
...
I have a spread sheet that instead of showing negaive 100 as -100, it
shows
negative 100 as 100-, is there any way to convert 100- to -100 by
formular?
Thanks.

Try

=LEFT(D1,3)*-1

And if the length of the text varies try

=LEFT(D1,FIND("-",D1)-1)*-1

HTH
Regards,
Howard

"How to convert from 100- to -100"
m wrote in message
...
I have a spread sheet that instead of showing negaive 100 as -100, it shows
negative 100 as 100-, is there any way to convert 100- to -100 by
formular?
Thanks.

Hi,

Little doubt that the text to columns command is the way to go, but if you
want a formula here is another one:

=IF(RIGHT(A1)="-",-SUBSTITUTE(A1,"-",),A1)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"How to convert from 100- to -100" wrote:

I have a spread sheet that instead of showing negaive 100 as -100, it shows
negative 100 as 100-, is there any way to convert 100- to -100 by formular?
Thanks.