Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Find one letter in a string of letters (Excel xp)

    I want to write a formula that will look at a cell to see if it has a B in it.

    For instance, if B4 has ABCEF in it. What formula could i put in B3 that will add an asterisk to B3's value if there is a B in the cell below it and do nothing if there is no B. Thank you for the help.

  2. #2
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find one letter in a string of letters (Excel xp)

    In B3 enter:

    =IF(LEN(B4)-LEN(SUBSTITUTE(B4,"B","")),"*"&B4,"")

    or just

    =IF(LEN(B4)-LEN(SUBSTITUTE(B4,"B","")),"*","")

    Aladin
    Microsoft MVP - Excel

  3. #3
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find one letter in a string of letters (Excel xp)

    An alternative way

    =IF(ISERROR(FIND("B",B4)),"","*")

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Find one letter in a string of letters (Excel xp)

    i changed to

    =C213 & IF(ISERROR(FIND("A",AC214)),"","

  5. #5
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find one letter in a string of letters (Excel xp)

    =C213 & IF(ISERROR(FIND("A",AC214)),IF(ISERROR(FIND("B",AB 214)),"","

Posting Permissions

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