+ Reply to Thread
Results 1 to 11 of 11

Finding and Counting for text within a string in a column

  1. #1
    Registered User
    Join Date
    07-20-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    2

    Lightbulb Finding and Counting for text within a string in a column

    Hi,

    I'm trying to teach myself excel and apply what I learn at work. Here's my problem:

    AF26AYN25
    AF25BYN21
    AU34CNYY22
    CE221AYYY10
    EU13BYN2
    GR83DYN1
    MI14BNYY15
    NO53BYN6
    PA66BYN25
    SO51CYN5
    AS44DNYN8

    These serial numbers represent buildings in various places around the world.

    I'm trying to count particular types of buildings, such as, all the buildings in Asia (AS in the first two digits).

    Creating the serial numbers was my "bright" idea for simplifying finding and counting from a table with lots of conditional logic (CountIf Column A="Asia", and Column B="B", and Column C="Yes", and Column D="No"...). I could not get a countif formula to work with and: AND(COUNTIF( , ), COUNTIF( , ). If comes back true, but doesn't count the cells I asked it to.

    So, I build this code system, which may be much better in the long run, or I may have wasted all afternoon now that I realize I don't know how to search within the string. I tried simply using CountIf with all wilcards except for the text in the digits I'm interested it: (AS????5????25), which doesn't work. Here's the code table:


    Region Code City Code Asset Type Code Criticality Code Plan Created Code Satellite Code Hub City Hub Plan Code Risk Profile Code Asset Code

    AF 2 6 A Y N 25 AF26AYN25
    AF 2 5 B Y N 21 AF25BYN21
    AU 3 4 C N Y 22 Y 22 AU34CNYY22
    CE 22 1 A Y Y 1 Y 10 CE221AYYY10
    EU 1 3 B Y N 2 EU13BYN2
    GR 8 3 D Y N 1 GR83DYN1
    MI 1 4 B N Y 2 Y 15 MI14BNYY15
    NO 5 3 B Y N 6 NO53BYN6
    PA 6 6 B Y N 25 PA66BYN25
    SO 5 1 C Y N 5 SO51CYN5
    AS 4 4 D N Y 6 N 8 AS44DNYN8

    Am I just making my life harder for myself? Should I just build lots of smaller tables and use the sum function for each specific type of building I'm looking for (dozens of tables). I honestly thought I'd be able to search for the text in the string with one function, and it would be clean and easy.

    Any help?
    Attached Files Attached Files
    Last edited by WanderingDog; 07-20-2013 at 06:20 PM.

  2. #2
    Registered User
    Join Date
    07-12-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Finding and Counting for text within a string in a column

    I'm no expert, but if all u wanna do is count, can't you just arrange the data in alphabetical order and count the "AS"'s?

  3. #3
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,063

    Re: Finding and Counting for text within a string in a column

    Post a workbook with a sample table and indicate what you counted and why you got the number you got.

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    Ben Van Johnson

  4. #4
    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
    38,787

    Re: Finding and Counting for text within a string in a column

    One way:

    =COUNTIF(A:A,"AF*")


    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    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


  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,308

    Re: Finding and Counting for text within a string in a column

    Hi and welcome to the forum :0

    Im a little unsure on what your data looks like (columns/rows etc). Could you upload a sample worlbook, showing what you have (no sensitive info), what your expected outcome would be, and how you arrived at that?
    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Registered User
    Join Date
    07-20-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Finding and Counting for text within a string in a column

    Ok, demo uploaded. The trouble is I'm looking for multiple combinations of text within the string, not just the ones with AS. I want to, ideally, be able to search for any combination.

  7. #7
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,063

    Re: Finding and Counting for text within a string in a column

    For Starters (though I'm not sure about all those spaces):
    Please Login or Register  to view this content.

  8. #8
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,698

    Re: Finding and Counting for text within a string in a column

    Quote Originally Posted by WanderingDog View Post
    Hi,
    Creating the serial numbers was my "bright" idea for simplifying finding and counting from a table with lots of conditional logic (CountIf Column A="Asia", and Column B="B", and Column C="Yes", and Column D="No"...). I could not get a countif formula to work with and: AND(COUNTIF( , ), COUNTIF( , ). If comes back true, but doesn't count the cells I asked it to.
    Is it a "bright" idea to do the combination while COUNTIFS or SUMPRODUCT can do this?
    With table like this:
    A B C D E F G
    AF 2 6 A Y N 25
    AF 2 5 B Y N 21
    AU 3 4 C N Y Y 22

    Count A column with "AF" and C column equals 6:
    =COUNTIFS(A2:A4,"AF",C2:C4,6)
    =SUMPRODUCT((A2:A4="AF")*(C2:C4=6))
    =1
    Does it work?

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,308

    Re: Finding and Counting for text within a string in a column

    Guys, if you look at te provided workbook, there are sample answers in there that, so far, would not work with any of the suggestions

  10. #10
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,698

    Re: Finding and Counting for text within a string in a column

    Quote Originally Posted by FDibbins View Post
    Guys, if you look at te provided workbook, there are sample answers in there that, so far, would not work with any of the suggestions

    The OP said in #1 that he couldnot use COUNTIF for multi columns from original table, so he have to create combination code to search

  11. #11
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,063

    Re: Finding and Counting for text within a string in a column

    Please Login or Register  to view this content.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Finding text string within Range and returning Row/Column number
    By IanE78 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-09-2012, 12:13 PM
  2. Replies: 6
    Last Post: 03-08-2012, 10:35 AM
  3. Replies: 9
    Last Post: 10-17-2011, 04:33 PM
  4. Finding a text string in a column
    By booo in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-30-2010, 04:04 AM
  5. Finding string Text
    By Dave31 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-27-2008, 07:50 AM

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