+ Reply to Thread
Results 1 to 10 of 10

Counting occurences of text string (if string occurs twice in cell only count one)

  1. 10-17-2011, 03:51 PM #1
    maxc
    maxc is offline
    Registered User
    Join Date
    10-17-2011
    Location
    New York
    MS-Off Ver
    Excel 2011
    Posts
    8

    Counting occurences of text string (if string occurs twice in cell only count one)

    Hi people, random excel question and my first post here.

    I have a massive spreadsheet with lots of data that I'm trying to extract some hard numbers from.

    Let's say my spread sheet looks like this...

    A1: Mozilla/5.0 (iPhone; U; CPU iPhone OS 4_2_1 like Mac OS X; en-us) AppleWebKit/533.17.9 (KHTML, like Gecko) Version/5.0.2 Mobile/8C148 Safari/6533.18.5

    A2: Mozilla/5.0 (Linux; U; Android 2.2.1; en-us; pcdadr6350 Build/FRG83D) AppleWebKit/533.1 (KHTML, like Gecko) Version/4.0 Mobile Safari/533.1

    A3: Mozilla/5.0 (Linux; U; Android 2.2.1; en-us; myTouch4G Build/FRG83) AppleWebKit/533.1 (KHTML, like Gecko) Version/4.0 Mobile Safari/533.1

    I want to count the occurrences of iPhone in this data and I came across this formula

    =SUM(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,"iPhone","")))/LEN("iPhone")

    which would give me 2.

    However, I'm looking for a formula that would only count 1 occurrence / cell and therefore would output the number 1.

    Anyone know of a way to solve this problem?
    Last edited by maxc; 10-17-2011 at 04:24 PM.
    Reply With QuoteRegister To Reply
  2. 10-17-2011, 03:57 PM #2
    khamilton
    khamilton is offline
    Valued Forum Contributor khamilton's Avatar
    Join Date
    10-08-2009
    Location
    IL
    MS-Off Ver
    Excel 2007
    Posts
    345

    Re: Counting occurences of text string (if string occurs twice in cell only count one

    So you would like either 0 is none or 1 there is at least 1.
    Please acknowledge the response you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.
    Reply With QuoteRegister To Reply
  3. 10-17-2011, 03:58 PM #3
    tigeravatar
    tigeravatar is offline
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Counting occurences of text string (if string occurs twice in cell only count one

    =COUNTIF(A1:A3,"*iphone*")
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  4. 10-17-2011, 04:03 PM #4
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,440

    Re: Counting occurences of text string (if string occurs twice in cell only count one

    Try:

    =SUMPRODUCT(--(LEN(A1:A3)<>LEN(SUBSTITUTE(A1:A3,"iPhone",""))))


    Regards
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  5. 10-17-2011, 04:05 PM #5
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,440

    Re: Counting occurences of text string (if string occurs twice in cell only count one

    @tigeravatar: ok, that's a bit simpler
    Reply With QuoteRegister To Reply
  6. 10-17-2011, 04:08 PM #6
    tigeravatar
    tigeravatar is offline
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Counting occurences of text string (if string occurs twice in cell only count one

    hehe, thanks ^_^
    Reply With QuoteRegister To Reply
  7. 10-17-2011, 04:10 PM #7
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,440

    Re: Counting occurences of text string (if string occurs twice in cell only count one

    @tigeravatar: I was just led astray be the existing SUBSTITUTE formula ...
    Reply With QuoteRegister To Reply
  8. 10-17-2011, 04:20 PM #8
    maxc
    maxc is offline
    Registered User
    Join Date
    10-17-2011
    Location
    New York
    MS-Off Ver
    Excel 2011
    Posts
    8

    Re: Counting occurences of text string (if string occurs twice in cell only count one

    Thanks everyone for quick responses. Tigeravatar, thanks for that simple and effective formula! It solved my problem perfectly. I'm pretty much a newbie in terms of Excels more advanced functions so I'll most likely be back on this forum!
    Reply With QuoteRegister To Reply
  9. 10-17-2011, 04:28 PM #9
    maxc
    maxc is offline
    Registered User
    Join Date
    10-17-2011
    Location
    New York
    MS-Off Ver
    Excel 2011
    Posts
    8

    Re: Counting occurences of text string (if string occurs twice in cell only count one

    TMShucks, your formula worked great too! Thanks.
    Reply With QuoteRegister To Reply
  10. 10-17-2011, 04:33 PM #10
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,440

    Re: Counting occurences of text string (if string occurs twice in cell only count one

    You're welcome. Thanks for the rep.

    It's nice to have choices

    However, in this case, I bow to tigeravatar's simplistic approach.

    Regards
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1