# Formula IF...begins with...possible?

This is a discussion on Formula IF...begins with...possible? within the Excel Questions forums, part of the Question Forums category; Hi, Does anyone know if there is a way of writing a formula that will show certain values depending upon ...

1. ## Formula IF...begins with...possible?

Hi,

Does anyone know if there is a way of writing a formula that will show certain values depending upon the first piece of text in another cell...?

What I'd like to do, is the following:

IF cell A1 begins with C, replace 'C' with 'T' can it be done? (There are only ever two characters in the cell. I can do it so that no matter what the cell begins with, it is replaced with a 'T', but I need to make it conditional...

Any suggestions would be appreciated.

2. ## Re: Formula IF...begins with...possible?

Hi, I guess:

=IF(LEFT(A1)="C","T"&RIGHT(A1),A1)

but if C cannot appear as second letter you could use

=SUBSTITUTE(A1,"C","T")

3. ## Re: Formula IF...begins with...possible?

That's great. Works fine, thanks!

4. ## Re: Formula IF...begins with...possible?

Actually, I've had another thought. There are a few instances where the referenced cell begins with 'T' and not 'C'. Any ideas on using an IF(AND( statement to do the calculation the other way round as well, but combine the two into one cell...?

ie. If the cell begins with 'T', replace with 'C' but if it begins with 'C', replace with 'T'........

5. ## Re: Formula IF...begins with...possible?

=IF(LEFT(A1)="C","T",IF(LEFT(A1)="T","C",LEFT(A1)))&RIGHT(A1)

6. ## Re: Formula IF...begins with...possible?

Another variation on fairwind's first substitute...
=IF(LEFT(A1)="C",SUBSTITUTE(A1,"C","T"),IF(LEFT(A1)="T",SUBSTITUTE(A1,"T","C")))

7. ## Re: Formula IF...begins with...possible?

Both solutions work a treat - thanks guys!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•