What is the data source you are using?
Here you go.
If you modify the custom SQL under edit connection you can do this when connecting to excel.
Just add another field that calculates the word count like the example below substituting [Customer Name] with your field name.
IIF(LEN(TRIM([Customer Name]))=0,0,LEN(TRIM([Customer Name]))-LEN(REPLACE([Customer Name],' ',''))+1) AS [Count Words in Cust Name]
Please see the attached example from superstore sales which returns Customer Name and the count of words in the Customer Name.
Test Word Count.twbx 52.3 KB
Here is a dynamic solution using technique #4 from The Cross Join Collection:
SELECT d.[Customer Name] , l.Pos , mid$(d.[Customer Name], l.Pos, instr(l.Pos, d.[Customer Name] + ' ', ' ') - l.Pos) as [Word] FROM [data$] d, [lookup$] l WHERE l.Pos <= len(d.[Customer Name]) AND mid$(' ' + d.[Customer Name], l.Pos, 1) = ' '
Warning: This takes 15 seconds or so with approx 3000 rows containing 2-3 words (customer name) in average.
See more in attached workbook.