Return occurences of similar values from text string

Assuming your text string is in A1 and B1 has DOG or Dog or dog, then try this.....

Please Login or Register  to view this content.

This will return 2.

Does this work?

Sorry I completely missed your requirement, you are trying to return occurrences instead of counting the occurrences.

This will work but only for two instances.

Formula:

Please Login or Register  to view this content.

BTW can those instances be in separate cells?

please attach a sample file with expected result

I tried to do it all in one cell but couldn't. Assuming the text is in B1 and with a helper cell in A1 enter this and fill across as far as needed.

Formula:

Please Login or Register  to view this content.

There are empty cells between each returned item, but it finds all instances. File is attached.


Edit This small adjustment concatenates.....eventually. (Not in the file.)

Formula:

Please Login or Register  to view this content.

Hi.

If you want the returns placed into a single-cell, comma- or space-separated, then this will require VBA (otherwise unfeasibly long formulas).

Placing the returns each into individual cells, however, is no problem using worksheet formulas alone, if that is something you would consider.

Regards

Based on your workbook, put e.g. "Dog" in B1 and then this formula in C1:

=QUOTIENT(LEN(A2)-LEN(SUBSTITUTE(A2,B1,"")),LEN(B1))

Then this array formula** in B2:

=IF(COLUMNS($A:A)>$C$1,"",TRIM(LEFT(SUBSTITUTE(MID($A2,SMALL(IF(MID($A2,ROW(INDEX(A:A,1):INDEX(A:A,LEN($A2)-LEN($B$1)+1)),LEN($B$1))=$B$1,ROW(INDEX(A:A,1):INDEX(A:A,LEN($A2)-LEN($B$1)+1))),COLUMNS($A:A)),LEN($A2))," ",REPT(" ",LEN($A2))),LEN($A2))))

Copy this formula to the right until you start to get blanks for the results.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

You're welcome!

can use ROW(a$1:INDEX(A:A,LEN($A2)-LEN($B$1)+1)) instead of Row(INDEX(A:A,1):INDEX(A:A,LEN($A2)-LEN($B$1)+1))

Originally Posted by FlameRetired

Thank you for showing us yet one more opportunity to kick INDIRECT to the curb! LOL

Indeed!

INDEX is used nowhere nearly enough as it should be, in my opinion. And yet constructions using INDIRECT, OFFSET, and even ADDRESS (ouch!) abound, even when these can almost always be replaced with a simple, non-volatile (well, almost) INDEX set-up.

Cheers!

Originally Posted by nflsales

can use ROW(a$1:INDEX(A:A,LEN($A2)-LEN($B$1)+1)) instead of Row(INDEX(A:A,1):INDEX(A:A,LEN($A2)-LEN($B$1)+1))

You can, but the second is more robust.

For example, if row 1 is for whatever reason deleted, then your version will return a #REF! error, whereas mine will continue to function correctly.

Of course, both versions will result in that error if column A is deleted, and this is why it makes sense to make the choice of column reference (i.e. A:A) in this construction to be one which holds the data, and so is not likely to be deleted.

Regards