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,
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.
so the B1 will be 4 and B2 will be 1
With your sample text in A1:A2
This formula, copied down, returns the count of the text pattern "A???-B???" in each referenced cell
A 1 sadfsdA001-B301,A303-B303,A004-B304,A005-B305,sdfs-2324,F987-B345 2 sadfsdA001-B301,B304sdfssadlfk-werl,-we
In that example the formulas return:![]()
B1: =COUNT(INDEX(SEARCH("A???-B???",MID(A1,ROW(INDIRECT("1:"&LEN(A1)-8)),9)),0))
4
1
Is that something you can work with?
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks