Existing working formula processes multiple words in cell, I need single word in cell.

Hi,

Can someone pls help.

I have the following formula:

=IF(SUMPRODUCT((LEN(" "&SUBSTITUTE(SUBSTITUTE(J5,","," "),"."," ")&" ")-LEN(SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE(J5,","," "),"."," ")&" "," "&SQR_Conversions!$B$2:$B$2418&" ","")))/LEN(" "&SQR_Conversions!$B$2:$B$2418&" "))=0,"",SUMPRODUCT((LEN(" "&SUBSTITUTE(SUBSTITUTE(J5,","," "),"."," ")&" ")-LEN(SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE(J5,","," "),"."," ")&" "," "&SQR_Conversions!$B$2:$B$2418&" ","")))/LEN(" "&SQR_Conversions!$B$2:$B$2418&" ")))

This works well and does it's job when parsing multiple words in a cell: i.e.

Here are a few words

I need the above formula modified to work on a single single word: i.e.

singleword

I suspect its a change to the syntax, tried but no joy: &SUBSTITUTE(SUBSTITUTE(J5,","," "),"."," ")&" ")

Really appreciate any pointers.

Rgds

Marcus

Hi Richard,

Thanks for your speedy post. That worked but not with the rest of the formula, it didn't return desired output. See image attached.

excel_forum.PNG

Code in C1:

=SUMPRODUCT((LEN(" "&SUBSTITUTE(SUBSTITUTE($A$2:$A$7,","," "),"."," ")&" ")-LEN(SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE($A$2:$A$7,","," "),"."," ")&" "," "&B2&" ","")))/LEN(" "&B2&" "))