Removing the letter c

[silentcoates]

Hello

If I have a list of monetay amounts in a list in column A and some of them have a capital C after them. Is there a formula that will change these ones so they show as a negative amount without the C and in red. Example below

A B
10.20C -10.20

Thanks for any help you can offer.

 

[Richard Schollar]

Hi

Try:

=SUBSTITUTE(A1,"C","")*-1

 

[chicagodiceman]

Or if you have mixed results, some with and some without the "C" then:
=If(Right(A1,1)="C",SUBSTITUTE(A1,"C","")*-1,A1)

 

[silentcoates]

awesome, thanks for that

 

[Peter_SSs]

silentcoates

For the pasrt about showing the negative numbers in red:
1. Select column B
2. Format|Cells...|Number tab|Category: Number|in the 'Negative numbers:' box at the bottom right you should have at least 1 choice that has negative numbers in red.