ForLoop to Find string value as part of substring in another cell

[skydivetom]

Experts:

I need some assistance with creating maybe a ForLoop (or DoWhile) in order to find a substring in a cell (e.g., A1). The value to be found is located in cell A10.

Cell A1:
- Starting at the left, cell A1 contains the value "XYZ" four times (XYZ + XYZ + XYZ + XYZ = XYZXYZXYZXYZ... without spaces).
- At the same time, cell A1 also contains the character orders of, e.g., "YZX" 3 times... YZXYZXYZX ... again, without spaces).
- Further, I also can find the string order = "XYZY" e.g., twice in cell A1.
- ultimately, depending on the string, there are numerous possibilities for finding a string.

Let's say now, I enter value "XYZ" into cell A10, I would like to return "4" in B10, given that this combination of letters (XYZ) exists 4 times in A1.
Alternatively, if I enter "XYZY" into A10, I'd like the count to show a 2.

My question:
How can I create either a ForLoop or DoLoop to achieve the accurate count on a sub-string (A1) based on a lookup value in A10?

Any thoughts/recommendation on how to create the ForLoop? Pls see attached XLS example.

[davesexcel]

Try this

Please Login or Register  to view this content.

"XYZY" is not an option.

[skydivetom]

PERFECT!!! Thank you... that'll work.