Counting text within a text string

[Ride The Lightning]

Hi

I have a concantated column of text strings. I want to be able to search within the text strings in the column and highlight (in colour if possible) particular letters that occur when I imput those letters in the cell the formula will relate to.

For example, I have in A3 "InthebeginningGodcreatedtheheavenandtheearth"
In cell A4 "Andtheearthwaswithoutformandvoidanddarknesswasuponthefaceofthedeep.AndtheSpiritofGodmoveduponthefaceofthewaters."

Cell A1 would be formula cell and cell B1 would have the imput criteria. If the imput criteria was "th" then it should highlight each time these two adjacent characters appear.

I have tried the Search function, but it only returns the reference of the 1st appearance of the characters.

Would appreciate help.

Thanks

RTL

 

[AlphaFrog]

You could count using the SUBSTITUTE function

=IF(B1="","",(LEN(A3)-LEN(SUBSTITUTE(A3,B1,"")))/LEN(B1))

I don't think you could highlight part of the text with just a formula. You would need a VBA solution.

 

[Ride The Lightning]

OK thanks, that formula is useful, but when I changed A3 to a range of cells, it returns #VALUE. How would I amend it to search a range?

 

[AlphaFrog]

Try something like this...

=IF(B1="","",SUMPRODUCT((LEN([COLOR="Red"]A3:A10[/COLOR])-LEN(SUBSTITUTE([COLOR="Red"]A3:A10[/COLOR],B1,"")))/LEN(B1)))

 

[T. Valko]

Try something like this...

=IF(B1="","",SUMPRODUCT(((LEN(A3:A10)-LEN(SUBSTITUTE(A3:A10,B1,"")))/LEN(B1))))

Since SUBSTITUTE is case sensitive it might be a good idea to ensure everything is the same case.

=SUMPRODUCT(LEN(A3:A10)-LEN(SUBSTITUTE(UPPER(A3:A10),UPPER(B1),"")))/LEN(B1)

 

[Ride The Lightning]

Thanks Alpha Frog and T Valko.

 

[T. Valko]

Thanks Alpha Frog and T Valko.

You're welcome!