Results 1 to 7 of 7

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. #1
    Board Regular j844929's Avatar
    Join Date
    Aug 2002
    Location
    Being a frog...a pond...
    Posts
    426

    Default 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.
    It's an accent, not a speech impediment...

  2. #2
    MrExcel MVP fairwinds's Avatar
    Join Date
    May 2003
    Posts
    8,634

    Default 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")
    "Fair Winds and Following Seas"

  3. #3
    Board Regular j844929's Avatar
    Join Date
    Aug 2002
    Location
    Being a frog...a pond...
    Posts
    426

    Default Re: Formula IF...begins with...possible?

    That's great. Works fine, thanks!
    It's an accent, not a speech impediment...

  4. #4
    Board Regular j844929's Avatar
    Join Date
    Aug 2002
    Location
    Being a frog...a pond...
    Posts
    426

    Default 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'........
    It's an accent, not a speech impediment...

  5. #5
    MrExcel MVP fairwinds's Avatar
    Join Date
    May 2003
    Posts
    8,634

    Default Re: Formula IF...begins with...possible?

    =IF(LEFT(A1)="C","T",IF(LEFT(A1)="T","C",LEFT(A1)))&RIGHT(A1)
    "Fair Winds and Following Seas"

  6. #6
    MrExcel MVP HalfAce's Avatar
    Join Date
    Apr 2003
    Location
    Alaska
    Posts
    9,236

    Default 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")))
    My greatest fear is that when I die my wife will sell my guns and my hot rod for what I told her they cost ...


  7. #7
    Board Regular j844929's Avatar
    Join Date
    Aug 2002
    Location
    Being a frog...a pond...
    Posts
    426

    Default Re: Formula IF...begins with...possible?

    Both solutions work a treat - thanks guys!
    It's an accent, not a speech impediment...

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com