Substring count in string with delimiters.

[lil_ern63]

Attached is an example sheet. I have used the formula =(LEN(UPPER($A4))-LEN(SUBSTITUTE(UPPER($A4),C$3,"")))/LEN(C$3) to count the number of occurances of a substring in a string of text, however this only works for a unique instance with a single letter does not occur in other substrings (i.e. B occurs by itself as well as in WB).

How can I alter the formula to look at an EXACT substring?

Also, I wanted to add an error check to see if a substring has been entered that doesn't exist from the list of possibilities. In the attached example, CUST2 does not exist in the list of possibilities from B3:G3.

The sample string text in A4 uses commas as a delimeter but can be changed to another, say a semicolon or colon if needed.

Any and all help is appreciated.

[dosydos]

Just off the top of my head you could subtract the "WB" count from the "W" and "B" formulas. I'm not sure if this will apply to your real life data or not.


=(LEN(UPPER($A4))-LEN(SUBSTITUTE(UPPER($A4),C$3,"")))/LEN(C$3)-((LEN(UPPER($A4))-LEN(SUBSTITUTE(UPPER($A4),$F$3,"")))/LEN($F$3))

[lil_ern63]

It would work in this example, but since the list of possible substrings is unknown, it would be less than ideal because as a substring is added, I would need to go back and double check each previous one to make sure there aren't any duplicates.

[GeoffW283]

OK, I think this works.

substrings.png

I have assumed a delimiter both at the beginning and end of the string under test in A4 as well as around the individual substrings in row-3. I changed the delimiter to a vertical bar as it seemed more natural given my changes.

I have tweaked your original formula just a little. The nested substitute()'s are necessary to accommodate the case where there are consecutive identical substrings in A4.
So in C9 copied across to G9:

Please Login or Register  to view this content.

The Error check formula in B9 is:

Please Login or Register  to view this content.

Basically it is comparing the number of delimiters in the string at A4 with the totsl number of matched substrings. If there are more delimiters in A4 than there are matches substrings then there must be an unrecognized substring in A4.

See the attached workbook.

Hopefully this works for you.

[lil_ern63]

This looks great, but I am having trouble duplicating the results. What exactly is the delimiter you used? I am having trouble locating. For that reason, I have tried to duplicate the results with using a comma or a semicolon, and no luck. Are these not "allowed" in the formula for some reason? All that being said, I suppose a delimiter isn't required, however that could muddy the waters quite a bit (i.e. if user inputs BB, is that two "B" or one "BB"?)

Secondly, could the formulas be tweeked to NOT require the delimiter at the beginning and end? I am ok with the necessity, however I am trying to build the sheet for company use, and limiting the "rules" is essential to smooth operation.

Thanks.

[GeoffW283]

I'm not sure from your profile what part of the world you are in and therefore what your keyboard layout is, but for me in the USA it's just above the enter key. I think I've also seen it at the upper left corner of the keyboard. It's the Unix "pipe" symbol if that helps you. That said, any character like ":" that won't appear within your substrings will work. I don't know why comma or semicolon doesn't work for you, although comma's could get confused with parameter separators.

The delimiter is required as I think you convinced yourself!

I struggled for a while to find a method that worked properly with all special cases like repeated consecutive identical substrings, dealing correctly with the first and last substrings and at the same time allow the error check to work. I'm not saying it's impossible to do this without delimiters at the beginning and end but I'm afraid I couldn't find such a solution.

I'll take a look and see if I can make it work with ";" as the delimiter.

[GeoffW283]

Attached is a workbook with ";" as a delimiter. To my eye it's less aesthetically pleasing, but it works!
I made the replacement of "|" with ";" in the following places:
C3:G3
A4
B9
C9:G9

Hope this helps.

[lil_ern63]

Geoff,

I am in the US, and sure enough there is the "|" you mentioned. I guess I just glanced over it.

Your solution is great! I am not sure what direction I will go with the delimiter quite yet. I was originally wanting a comma, as it looked the cleanest. I have thought to get around that, adding another dummy/helper column that would take the user input (using comma's) and then changing over to either the semicolon or the "pipe" as you called it. Also this dummy column would additionally add the delimiter to the beginning and end if the user has forgotten to. (My system would be replacing a current one (not in excel) that doesn't require a leading or trailing delimiter, and habits may be hard to break for employees used to the old style of input.

Again, thanks for all your input!

[GeoffW283]

No problem, glad to help and thanks for the interesting feedback.