Results 1 to 4 of 4

Thread: Excel Formula
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Excel Formula

    Hi, refer to attachment, I'd like to find out how many time the email at column A appears at col. B (within the same row only. Eg. A1 with B1, A2 with B2). Currently I have this formula as appeared at col. C1, but it's not that accurate, as long as the emails at col. B are longer that email at col. A, it will count it. Wonder if you knows the accurate formula to count it? Much appreciated! Many thanks for your help!
    Email Range Formula
    =(LEN(C2)-LEN(SUBSTITUTE(UPPER(C2),UPPER(A2),"")))/LEN(A2)
    Accurate Result
    A@abc.com A@abc.com.sg/connection lost, A@abc.com.sg/connection lost, AA@abc.com.sg/unknown, A@abc.com/error 4 wrong, should be 1
    bb@mail.com.sg bb@gmail.com.sg/connection lost, aa@mail.com.sg/connection lost, bb@mail.com/error 0 correct
    ab@outlook.com aab@outlook.com/connection lost, aab@outlook.com.my/connection lost, ab@outlook.my/connection lost 2 wrong, should be 0

  2. #2
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,271
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Excel Formula

    Welcome to Mr Excel forum

    Maybe...
    =(LEN(","&SUBSTITUTE(C2," ",""))-LEN(SUBSTITUTE(UPPER(","&SUBSTITUTE(C2," ","")),UPPER(","&A2&"/"),"")))/LEN(","&A2&"/")

    Hope this helps

    M.

  3. #3
    New Member
    Join Date
    Apr 2014
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Formula

    I am going to offer a different approach using the Search function. The search function matches whether the string to be searched for is upper or lower case, (The Find function requires matching case.)

    Bear in mind, this is not exactly what you asked for as it returns a 0 if the email address is not found or a 1 if it is found. Although if there is a small number of error types, you could add additional formulas which include the error type in the search string, e.g., "/connection lost", etc.

    Also, I had to add the space, (" "&) so that aab@outlook.com would not be counted even though it contains ab@outlook.com

    =IF(ISERROR(SEARCH(" "&$A4&"/"," "&$B4)),0,1)

    I hope this helps.

    Paul

  4. #4
    New Member
    Join Date
    Apr 2014
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Formula

    I took one more shot at this and now have the formula counting the occurrences of the email address within the string, so if the email is listed more than once, e.g., with more than one error, it should count it as many times.

    At least it worked for me when I dummied a record with an email address listed twice.

    =(LEN(" "&$C4&"/")-LEN(SUBSTITUTE(UPPER(" "&$C4&"/"),UPPER(" "&$A4&"/"),"")))/(LEN($A4)+2)

Some videos you may like

User Tag List

Tags for this Thread

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
  •