I have been attempting to find a formula that counts the number of times the characters in a cell are found in any of the other strings of letters in a range. (What has stumped me is that I do not want just the exact matches of the characters in the same order, but rather, to count all instances when the characters from the reference cell are found even if they are separated by other characters. In my examples below in column A I have the strings of characters and column B is where I want to display the number of times they appear. My goal is to build a formula that will show me that abce appears twice... once as just abce, and then again as apart of abcde. Then bce appears 3 times in bce, abce, and abcde.
Example 1:
Strings / Count
abce / 2
abcde / 1
ae / 3
bce / 3
I have also formatted my data into strings of 0s and 1s as text to see if going about it this way might make more sense, to no avail. In this case 00101 appears twice, once in 00101, and again in 01111.
Example 2:
Strings / Count
01010 / 2
01111 / 1
00101 / 2
Example 1:
Strings / Count
abce / 2
abcde / 1
ae / 3
bce / 3
I have also formatted my data into strings of 0s and 1s as text to see if going about it this way might make more sense, to no avail. In this case 00101 appears twice, once in 00101, and again in 01111.
Example 2:
Strings / Count
01010 / 2
01111 / 1
00101 / 2