+ Reply to Thread
Results 1 to 14 of 14

Count in a cell where there is several words...

  1. #1
    Forum Contributor
    Join Date
    11-22-2006
    Posts
    100

    Count in a cell where there is several words...

    Hi,
    I have a problem to count some words in a column: in fact, in the cell they can write several words and my objective is to count in this column how many times there is the word "info" but as they can write several words in the same cell,(for example: speciality, info, degree) I can t really count how many times the word info is written in the column.

    Is there a solution?


    Thanks

  2. #2
    Forum Contributor vikas.bhandari's Avatar
    Join Date
    04-07-2006
    Location
    Delhi, India
    MS-Off Ver
    Office 2007 and 2010
    Posts
    303

    Thumbs up WildCards are the best solutions

    Hello,

    use this :

    Countif(A:A,"*info*")

    please note, it will only count the no of cells which contains "Info" but it will not tell how many times this word is appearng in a single cell??

    Does this help??

    Thanks,
    Vikas Bhandari

  3. #3
    Forum Contributor
    Join Date
    11-22-2006
    Posts
    100

    :)

    This helps me perfectly!!
    Cheers

  4. #4
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote Originally Posted by koda86
    This helps me perfectly!!
    Cheers
    Hi,

    Manually, you can Replace All, Info for Info to get a count of the words. (But I cannot find a way to get that count in VB or Formula)

    hth
    ---
    Si fractum non sit, noli id reficere.

  5. #5
    Forum Contributor vikas.bhandari's Avatar
    Join Date
    04-07-2006
    Location
    Delhi, India
    MS-Off Ver
    Office 2007 and 2010
    Posts
    303
    Quote Originally Posted by Bryan Hessey
    Hi,

    Manually, you can Replace All, Info for Info to get a count of the words. (But I cannot find a way to get that count in VB or Formula)

    hth
    ---
    Umm, yeah that can be an option, but we dont have to run a Macro, then we have no other option than countIf. Also, I think I didnt understand what you wantd to say....you want to say tht in VB, we cannot find how many times word "Info" is coming in a string???

    Regards,
    Vikas Bhandari

  6. #6
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote Originally Posted by vikas.bhandari
    Umm, yeah that can be an option, but we dont have to run a Macro, then we have no other option than countIf. Also, I think I didnt understand what you wantd to say....you want to say tht in VB, we cannot find how many times word "Info" is coming in a string???

    Regards,
    Vikas Bhandari
    in Manual mode you can 'Replace All' and receive a count of words that you replaced. In VB you can Replace All, but I see no method of picking up the count displayed to the manual display.
    It is possible to character search text for any information, but that could be noticeable in it's slowness, and would be more difficult to organise that the Replace All.

    It would depend upon the effort you wanted to put in to write code that is already performed on a manual basis.

    Does this help you?
    ---

  7. #7
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    I guess the OP is happy with

    Countif(A:A,"*info*")

    but if you want to count the number of times "info" appears in A1:A10, including multiple times in a cell the you could use this formula

    =SUMPRODUCT(--((LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"info","")))/LEN("info")))

  8. #8
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote Originally Posted by daddylonglegs
    I guess the OP is happy with

    Countif(A:A,"*info*")

    but if you want to count the number of times "info" appears in A1:A10, including multiple times in a cell the you could use this formula

    =SUMPRODUCT(--((LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"info","")))/LEN("info")))
    we can now, very nice twist daddylonglegs

    ---

  9. #9
    Forum Contributor vikas.bhandari's Avatar
    Join Date
    04-07-2006
    Location
    Delhi, India
    MS-Off Ver
    Office 2007 and 2010
    Posts
    303
    Man, you are crazzzzyyyyyy...how do you do all this!!!

    Well, just a feedback....this is an awesome forum where I get a lots of stuffs to learn and a chance to discuss with the most intelligent creatures in this world!!!

    Cheers to Excel Forums

    Thanks to every moderator and other intelligent personalities around!!!!
    Vikas B

  10. #10
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    A slight word of caution if info is contained in another word eg information it will be counted by all the formulas suggested. I do not have an easy answer to overcome this, but thought you should be aware.

    Regards

    Dav

  11. #11
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Perhaps you can't accommodate all punctuation but assuming words in A1:A10 are separated by just a comma, a space or a combination of the two then this formula should count all instances of "info" but not "inform" or "information"

    =SUMPRODUCT(--((LEN(" "&SUBSTITUTE(SUBSTITUTE(A1:A10,","," ")," "," ")&" ")-LEN(SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE(A1:A10,","," ")," "," ")&" "," info ","")))/LEN(" info ")))

  12. #12
    Registered User
    Join Date
    03-29-2006
    Posts
    21
    This seems to be just what I need.

    Can anyone inform me how I would do it withe the text im looking up is referenced in a cell??

    Thanks

    Gunny
    Gunny

  13. #13
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Which formula are you looking at?

    In general you can just replace "info" with a cell reference, e.g. if cell is D2

    =SUMPRODUCT(--((LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,D2,"")))/LEN(D2)))

    although in the last formula where "info" has a space either side you'd need a small tweak to

    =SUMPRODUCT(--((LEN(" "&SUBSTITUTE(SUBSTITUTE(A1:A10,","," ")," "," ")&" ")-LEN(SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE(A1:A10,","," ")," "," ")&" "," "&D2&" ","")))/LEN(D2)+2))

  14. #14
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1