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:Please Login or Register to view this content.
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:
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks