Excel formula...need 55.55cr changed to -55.55

[jerryk]

Hello,

I have a report that dumps to excel, but the "credits" have a trailing "cr".

Since these are really "negative", I'd like to convert the numbers to negative, hence my example above. A further example would be:
I need 23cr to become -23 ..or even (23) would work.

Many thanks for any assistance you could provide.

 

[bluedragon2]

=-1*value(left(a1,len(a1)-2))



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

Probably needs to be
[tt]
=if(right(a1,2)="cr",-1*value(left(a1,len(a1)-2)),a1)
[/tt]
since, I would guess, that ALL and not credits.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[jerryk]

great info! I'll be saving those formulas as reference. Thanks!!

 

[bluedragon2]

good point Skip...



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...