+ Reply to Thread
Results 1 to 5 of 5

Count the QTY of specific character of "A???-B???"

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    12-13-2013
    Location
    Calgary,AB,Canada
    MS-Off Ver
    2016,O365
    Posts
    236

    Count the QTY of specific character of "A???-B???"

    Source: A1:A2
    A1=sadfsdA001-B301,A303-B303,A004-B304,A005-B305,sdfs-2324,F987-B345
    A2="sadfsdA001-B301,B304sdfssadlfk-werl,-we"

    Need a formula in B1 and B2 to count the QTY of specific character of "A???-B???" in A1 and A2. thanks,
    Last edited by CAABYYC; 01-12-2016 at 05:17 PM.

  2. #2
    Forum Contributor
    Join Date
    12-13-2013
    Location
    Calgary,AB,Canada
    MS-Off Ver
    2016,O365
    Posts
    236

    Re: Count the QTY of specific character of "A???-B???"

    so the B1 will be 4 and B2 will be 1

  3. #3
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Count the QTY of specific character of "A???-B???"

    With your sample text in A1:A2
    A
    1
    sadfsdA001-B301,A303-B303,A004-B304,A005-B305,sdfs-2324,F987-B345
    2
    sadfsdA001-B301,B304sdfssadlfk-werl,-we
    This formula, copied down, returns the count of the text pattern "A???-B???" in each referenced cell
    B1: =COUNT(INDEX(SEARCH("A???-B???",MID(A1,ROW(INDIRECT("1:"&LEN(A1)-8)),9)),0))
    In that example the formulas return:
    4
    1

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  4. #4
    Forum Contributor
    Join Date
    12-13-2013
    Location
    Calgary,AB,Canada
    MS-Off Ver
    2016,O365
    Posts
    236

    Re: Count the QTY of specific character of "A???-B???"

    thanks, Ron, it works. the only thing I don't quite understand is meaning of all individual function (when they ere put together)....

    Quote Originally Posted by Ron Coderre View Post
    With your sample text in A1:A2
    A
    1
    sadfsdA001-B301,A303-B303,A004-B304,A005-B305,sdfs-2324,F987-B345
    2
    sadfsdA001-B301,B304sdfssadlfk-werl,-we
    This formula, copied down, returns the count of the text pattern "A???-B???" in each referenced cell
    B1: =COUNT(INDEX(SEARCH("A???-B???",MID(A1,ROW(INDIRECT("1:"&LEN(A1)-8)),9)),0))
    In that example the formulas return:
    4
    1

    Is that something you can work with?

  5. #5
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Count the QTY of specific character of "A???-B???"

    In this formula:
    =COUNT(INDEX(SEARCH("A???-B???",MID(A1,ROW(INDIRECT("1:"&LEN(A1)-8)),9)),0))

    This section defines the patter to find: "A???-B???"
    The question marks (?) are wildcards that represent any single character

    This section defines the position of the first character the MID function will find:
    ROW(INDIRECT("1:"&LEN(A1)-8))
    It locates every character in sequence, ending with the 9th from the right side, because we're testing 9-character strings.

    This part creates a sequence of numbers from 1 through the length of the string less 8
    INDIRECT("1:"&LEN(A1)-8)

    We need to wrap the contents of the COUNT function in and INDEX function to avoid using CTRL+SHIFT+ENTER to create an array formula.
    (a neat little trick I discovered several years ago)

    Here's the resolution:
    =COUNT(INDEX(SEARCH("A???-B???",MID(A1,ROW(INDIRECT("1:"&LEN(A1)-8)),9)),0))
    =COUNT(INDEX(SEARCH("A???-B???",MID(A1,ROW(INDIRECT("1:"&39-8)),9)),0))
    =COUNT(INDEX(SEARCH("A???-B???",MID(A1,ROW(INDIRECT("1:31")),9)),0))
    =COUNT(INDEX(SEARCH("A???-B???",MID(A1,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31},9)),0))
    =COUNT(INDEX(SEARCH("A???-B???"
    ,{"sadfsdA00";"adfsdA001";"dfsdA001-";"fsdA001-B";"sdA001-B3";"dA001-B30";"A001-B301";"001-B301,";"01-B301,B";"1-B301,B3"
    ;"-B301,B30";"B301,B304";"301,B304s";"01,B304sd";"1,B304sdf";",B304sdfs";"B304sdfss";"304sdfssa";"04sdfssad";"4sdfssadl"
    ;"sdfssadlf";"dfssadlfk";"fssadlfk-";"ssadlfk-w";"sadlfk-we";"adlfk-wer";"dlfk-werl";"lfk-werl,";"fk-werl,-";"k-werl,-w"
    ;"-werl,-we"}),0))
    
    =COUNT(INDEX({#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!
    ;1;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!
    ;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!
    ;#VALUE!;#VALUE!;#VALUE!;#VALUE!},0))
    
    =1

+ 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. Replies: 1
    Last Post: 02-20-2015, 01:13 PM
  2. [SOLVED] :confused: Auto pick "specific data" and put it in "specific cells" with date
    By pipsmultan in forum Excel Formulas & Functions
    Replies: 29
    Last Post: 08-27-2014, 03:31 AM
  3. Replies: 1
    Last Post: 08-10-2014, 01:22 PM
  4. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  5. [SOLVED] How to Count number of "Error" and "OK" after the word "Instrument" found in table row
    By eltonlaw in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-17-2012, 06:26 AM
  6. Returning an "Error" if specific character is typed into a cell
    By cheddarthief in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-26-2011, 05:16 PM
  7. Replies: 7
    Last Post: 05-13-2006, 05:02 PM

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