-
1. Re: how do i count words in a column that contains sentences?
Robert Sutter Jun 3, 2014 7:04 AM (in response to ben kwok)What is the data source you are using?
-
2. Re: how do i count words in a column that contains sentences?
ben kwok Jun 3, 2014 8:36 AM (in response to Robert Sutter)Excel.
-
3. Re: how do i count words in a column that contains sentences?
Robert Sutter Jun 3, 2014 8:59 AM (in response to ben kwok)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.
Robb
-
Test Word Count.twbx 52.3 KB
-
-
4. Re: how do i count words in a column that contains sentences?
kettan Jun 3, 2014 9:37 AM (in response to ben kwok)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.
.