Removing the letter c

silentcoates

New Member
Joined
Oct 11, 2005
Messages
41
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.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

chicagodiceman

Active Member
Joined
Mar 13, 2004
Messages
371
Or if you have mixed results, some with and some without the "C" then:
=If(Right(A1,1)="C",SUBSTITUTE(A1,"C","")*-1,A1)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,859
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,041
Messages
5,545,679
Members
410,697
Latest member
srishtijain0708
Top