Count words in a cell except some specific words

[RonOliver]

Hi, everyone.

I'm trying to create a tool that splits the name and last name in a cell into three cells (one cell for the first name and the other two for both last names, as we have two last names in Spanish). The rest of the formula is already put together correctly, but I got stumped trying to do something.

I tried to write a formula that counts how many words there are in a cell (by counting spaces and then adding one). The formula looks like this:

=LEN(TRIM(C18))-LEN(SUBSTITUTE(C18," ",""))+1

The problem is that I want a formula that counts words in a cell EXCEPT certain words. For example, it's common for some last names to include prepositions (like "de Castro"). I do not want to count those as two words, but as one. Could you please help me with this? Thanks!

 

[offthelip]

try this:

=LEN(TRIM(C18))-LEN(SUBSTITUTE(C18," ",""))+1-(1*(FIND(" de ",C18)>0))

add another "find" for each conmbination you want to exclude

 

[RonOliver]

try this:

=LEN(TRIM(C18))-LEN(SUBSTITUTE(C18," ",""))+1-(1*(FIND(" de ",C18)>0))

add another "find" for each conmbination you want to exclude

Works great! Thanks!

 

[Legacy 143009]

Assuming you have a list of propositions from Z2 to Z20 like del, dos, les, los... Use your original formula and subtract this

yourOriginalFormula-SUMPRODUCT(ISNUMBER(SEARCH($Z$2:$Z$20,$A2))+0)

 

[RonOliver]

Assuming you have a list of propositions from Z2 to Z20 like del, dos, les, los... Use your original formula and subtract this

yourOriginalFormula-SUMPRODUCT(ISNUMBER(SEARCH($Z$2:$Z$20,$A2))+0)

Looks great! I'll try and see if I can get my own formula shorter by adding this.

 

[Legacy 143009]

Like

=LEN(TRIM(C18))-LEN(SUBSTITUTE(C18," ",""))+1-SUMPRODUCT(ISNUMBER(SEARCH($Z$2:$Z$20,C18))+0)