+ Reply to Thread
Results 1 to 4 of 4

Array Formula Question

  1. #1
    Registered User
    Join Date
    07-10-2007
    Posts
    3

    Array Formula Question

    I have a list of words.

    I need to count how many of those words are made using ONLY the letters D, O, and G (not case sensitive).

    For example, if the list of words was:

    dog, toe, go, dot, got, god, get, doe, Do

    the formula should return the value 4 (dog, go, god, Do).

    I know I could hash this out with regex in a VBA macro, but I was hoping there was an easier way to get this done with a formula (there's no regex in an excel formula, right?).

    I've tried using some pretty ugly array formulas, where I sort of "brute force" every possible combination, such as...
    Please Login or Register  to view this content.
    but it just gives me either a 1 or a 0.

    I'm working on Excel 2007 if it matters.

    Any help is appreciated, thanks!
    Last edited by wpt394; 03-10-2008 at 05:29 AM.

  2. #2
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    Yes it looks ugly!

    However all you need as an array formula is

    Please Login or Register  to view this content.
    If you replace all the D O and G from the string, the string will have a length of 0, in this case make it a 1 otherwise 0.

    Does that help

    regards

    Dav

  3. #3
    Registered User
    Join Date
    07-10-2007
    Posts
    3

    Wonderful!

    Very creative! I hadn't thought of throwing a test on a substitute.

    Substitute is case sensitive, however, so I did need to throw in test for capital letters.

    Thanks a ton. Problem solved.

  4. #4
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    it occured to me afterwards that if the range occurs blank values it will also equal 0 and be counted in the solution

    as a non-array the following may be better

    Please Login or Register  to view this content.
    Regards

    Dav

+ 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