Questions

Excel find and replace formula

+
0 Votes
Locked

Excel find and replace formula

eilli
Hi everybody.
I am forex and options trader. Options contracts look like this: 'GOOG 121005C770" or "GOOG 121005P770". I need to have a formula that can lookup the cell and if contains "*******P***" to replace the whole cell with "Put" and if the cell contains "*******C***" to replace the whole cell with "Call". Now I am using find and replace function which is fine. I am using wildcard to find: ******P*** and replace it with Put and then run find and replace again to find: *******C*** and replace it with Call.
Is there a way to have a simple formula which can do both seaches and replacements.
thanks in advance.
E. M.
  • +
    0 Votes
    jaydee675

    =IF(MID(A3,12,1)="C","Call",IF(MID(A3,12,1)="P","Put"))

    ie. if the 12th character is C then Call else if P then Put

    A3 is the cell with the "GOOG 121005C770" entry.

    +
    0 Votes
    eilli

    Thanks a lot for the reply.

    Your formula is working fine. However, I got assistance from another site with different formula and both working well:

    =IF(ISNUMBER(SEARCH("c",RIGHT(A1,10))),"call",IF(ISNUMBER(SEARCH("p",RIGHT(A1,10))),"put",A1))

  • +
    0 Votes
    jaydee675

    =IF(MID(A3,12,1)="C","Call",IF(MID(A3,12,1)="P","Put"))

    ie. if the 12th character is C then Call else if P then Put

    A3 is the cell with the "GOOG 121005C770" entry.

    +
    0 Votes
    eilli

    Thanks a lot for the reply.

    Your formula is working fine. However, I got assistance from another site with different formula and both working well:

    =IF(ISNUMBER(SEARCH("c",RIGHT(A1,10))),"call",IF(ISNUMBER(SEARCH("p",RIGHT(A1,10))),"put",A1))