I have a spreadsheet of over 40,000 lines for analysis. The first
column contains phrases from the British National Corpus. The other
columns contain various categorical descriptions of each phrase. This
is what I need to do (but don't know how!):

1. How to count the number of words in each phrase and write the
result to a new column.

2. How to count the number of letters in a single-word phrase and
write the result to a new column.

3. How to delete every line whose phrase contains more than one word.

4. How to delete every line whose phrase contains one word of less
than x letters.

5. How to delete lines whose phrases consist of one specific word
(e.g. 'it')

I would be very grateful for any guidance anyone can give. Many
thanks.

Kevin Glover

1. The trick here is to count the number of spaces and add one. This
formula will do so, say in C2:

=LEN(A2) - LEN(SUBSTITUTE(A2," ","")) + 1

assuming your string is in A2. You might like to put Num_words in C1
as a heading.

2. LEN returns the number of characters, so you could put this formula
in, say, D2:

=IF(C2=1,LEN(A2),"")

which only returns a value for single-word entries in column A. Put
Num_letters as the heading in D1.

3. A formula cannot delete a row. You can use a formula to mark a row
for deletion, and then delete all marked rows later in one operation
using a filter (or you could use a macro to do it for you). You could
put this formula in E2:

=IF(C2>1,"Delete","OK")

4. Put this formula in F2:

=IF(AND(D2<>"",D2<5),"Delete","ok")

I've assumed 5 letters for your "x".

5. Put this formula in G2:

=IF(AND(C2=1,A2="it"),"Delete","ok")

All these formulae can be copied down for as many phrases as you have.
Then you can apply autofilter (Data | Filter | Autofilter), and then
you can use the filter drop-down in column E and select Delete from
the list. Then highlight all the visible rows and click on Edit |
Delete and then select All in that filter. Do the same for columns F
and for G.

Hope this helps.

Pete

On Jan 19, 11:46*am, KevinUK <kevin.glo...@btinternet.com> wrote:
> I have a spreadsheet of over 40,000 lines for analysis. The first
> column contains phrases from the British National Corpus. The other
> columns contain various categorical descriptions of each phrase. This
> is what I need to do (but don't know how!):
>
> 1. How to count the number of words in each phrase and write the
> result to a new column.
>
> 2. How to count the number of letters in a single-word phrase and
> write the result to a new column.
>
> 3. How to delete every line whose phrase contains more than one word.
>
> 4. How to delete every line whose phrase contains one word of less
> than x letters.
>
> 5. How to delete lines whose phrases consist of one specific word
> (e.g. 'it')
>
> I would be very grateful for any guidance anyone can give. Many
> thanks.
>
> Kevin Glover

On Jan 19, 5:46*am, KevinUK <kevin.glo...@btinternet.com> wrote:
> I have a spreadsheet of over 40,000 lines for analysis. The first
> column contains phrases from the British National Corpus. The other
> columns contain various categorical descriptions of each phrase. This
> is what I need to do (but don't know how!):
>
> 1. How to count the number of words in each phrase and write the
> result to a new column.
>
> 2. How to count the number of letters in a single-word phrase and
> write the result to a new column.
>
> 3. How to delete every line whose phrase contains more than one word.
>
> 4. How to delete every line whose phrase contains one word of less
> than x letters.
>
> 5. How to delete lines whose phrases consist of one specific word
> (e.g. 'it')
>
> I would be very grateful for any guidance anyone can give. Many
> thanks.
>
> Kevin Glover

I would be willing to try to provide a one click macro solution. A
very clear explanation and

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

Pete, I have a sneaking suspicion that you just did Kevin's homework
assignment for him (I could be wrong about this though).

Rick Rothstein (MVP - Excel)




"Pete_UK" wrote in message
news:8d99e70e-ce38-47ff-9181-(E-Mail Removed)...

1. The trick here is to count the number of spaces and add one. This
formula will do so, say in C2:

=LEN(A2) - LEN(SUBSTITUTE(A2," ","")) + 1

assuming your string is in A2. You might like to put Num_words in C1
as a heading.

2. LEN returns the number of characters, so you could put this formula
in, say, D2:

=IF(C2=1,LEN(A2),"")

which only returns a value for single-word entries in column A. Put
Num_letters as the heading in D1.

3. A formula cannot delete a row. You can use a formula to mark a row
for deletion, and then delete all marked rows later in one operation
using a filter (or you could use a macro to do it for you). You could
put this formula in E2:

=IF(C2>1,"Delete","OK")

4. Put this formula in F2:

=IF(AND(D2<>"",D2<5),"Delete","ok")

I've assumed 5 letters for your "x".

5. Put this formula in G2:

=IF(AND(C2=1,A2="it"),"Delete","ok")

All these formulae can be copied down for as many phrases as you have.
Then you can apply autofilter (Data | Filter | Autofilter), and then
you can use the filter drop-down in column E and select Delete from
the list. Then highlight all the visible rows and click on Edit |
Delete and then select All in that filter. Do the same for columns F
and for G.

Hope this helps.

Pete

On Jan 19, 11:46 am, KevinUK <kevin.glo...@btinternet.com> wrote:
> I have a spreadsheet of over 40,000 lines for analysis. The first
> column contains phrases from the British National Corpus. The other
> columns contain various categorical descriptions of each phrase. This
> is what I need to do (but don't know how!):
>
> 1. How to count the number of words in each phrase and write the
> result to a new column.
>
> 2. How to count the number of letters in a single-word phrase and
> write the result to a new column.
>
> 3. How to delete every line whose phrase contains more than one word.
>
> 4. How to delete every line whose phrase contains one word of less
> than x letters.
>
> 5. How to delete lines whose phrases consist of one specific word
> (e.g. 'it')
>
> I would be very grateful for any guidance anyone can give. Many
> thanks.
>
> Kevin Glover

Yeah, maybe you are right, Rick. They do sometimes slip through ...

Pete

On Jan 19, 4:00*pm, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> Pete, I have a sneaking suspicion that you just did Kevin's homework
> assignment for him (I could be wrong about this though).
>
> Rick Rothstein (MVP - Excel)
>
> "Pete_UK" *wrote in message
>
> news:8d99e70e-ce38-47ff-9181-(E-Mail Removed)...
>
> 1. The trick here is to count the number of spaces and add one. This
> formula will do so, say in C2:
>
> =LEN(A2) - LEN(SUBSTITUTE(A2," ","")) + 1
>
> assuming your string is in A2. You might like to put Num_words in C1
> as a heading.
>
> 2. LEN returns the number of characters, so you could put this formula
> in, say, D2:
>
> =IF(C2=1,LEN(A2),"")
>
> which only returns a value for single-word entries in column A. Put
> Num_letters as the heading in D1.
>
> 3. A formula cannot delete a row. You can use a formula to mark a row
> for deletion, and then delete all marked rows later in one operation
> using a filter (or you could use a macro to do it for you). You could
> put this formula in E2:
>
> =IF(C2>1,"Delete","OK")
>
> 4. Put this formula in F2:
>
> =IF(AND(D2<>"",D2<5),"Delete","ok")
>
> I've assumed 5 letters for your "x".
>
> 5. Put this formula in G2:
>
> =IF(AND(C2=1,A2="it"),"Delete","ok")
>
> All these formulae can be copied down for as many phrases as you have.
> Then you can apply autofilter (Data | Filter | Autofilter), and then
> you can use the filter drop-down in column E and select Delete from
> the list. Then highlight all the visible rows and click on Edit |
> Delete and then select All in that filter. Do the same for columns F
> and for G.
>
> Hope this helps.
>
> Pete
>
> On Jan 19, 11:46 am, KevinUK <kevin.glo...@btinternet.com> wrote:
>
>
>
> > I have a spreadsheet of over 40,000 lines for analysis. The first
> > column contains phrases from the British National Corpus. The other
> > columns contain various categorical descriptions of each phrase. This
> > is what I need to do (but don't know how!):
>
> > 1. How to count the number of words in each phrase and write the
> > result to a new column.
>
> > 2. How to count the number of letters in a single-word phrase and
> > write the result to a new column.
>
> > 3. How to delete every line whose phrase contains more than one word.
>
> > 4. How to delete every line whose phrase contains one word of less
> > than x letters.
>
> > 5. How to delete lines whose phrases consist of one specific word
> > (e.g. 'it')
>
> > I would be very grateful for any guidance anyone can give. Many
> > thanks.
>
> > Kevin Glover- Hide quoted text -
>
> - Show quoted text -

I think he deserves an "A".
'---
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(XLCompanion counts and lists words)




"Rick Rothstein" <(E-Mail Removed)>
wrote in message
news:ih71s1$gpl$(E-Mail Removed)...
> Pete, I have a sneaking suspicion that you just did Kevin's homework assignment for him
> (I could be wrong about this though).
> Rick Rothstein (MVP - Excel)




> "Pete_UK" wrote in message
> news:8d99e70e-ce38-47ff-9181-(E-Mail Removed)...
>
> 1. The trick here is to count the number of spaces and add one. This
> formula will do so, say in C2:
>
> =LEN(A2) - LEN(SUBSTITUTE(A2," ","")) + 1
>
> assuming your string is in A2. You might like to put Num_words in C1
> as a heading.
>
> 2. LEN returns the number of characters, so you could put this formula
> in, say, D2:
>
> =IF(C2=1,LEN(A2),"")
>
> which only returns a value for single-word entries in column A. Put
> Num_letters as the heading in D1.
>
> 3. A formula cannot delete a row. You can use a formula to mark a row
> for deletion, and then delete all marked rows later in one operation
> using a filter (or you could use a macro to do it for you). You could
> put this formula in E2:
>
> =IF(C2>1,"Delete","OK")
>
> 4. Put this formula in F2:
>
> =IF(AND(D2<>"",D2<5),"Delete","ok")
>
> I've assumed 5 letters for your "x".
>
> 5. Put this formula in G2:
>
> =IF(AND(C2=1,A2="it"),"Delete","ok")
>
> All these formulae can be copied down for as many phrases as you have.
> Then you can apply autofilter (Data | Filter | Autofilter), and then
> you can use the filter drop-down in column E and select Delete from
> the list. Then highlight all the visible rows and click on Edit |
> Delete and then select All in that filter. Do the same for columns F
> and for G.
>
> Hope this helps. Pete

Pete,

You are a star. Thanks so much for the guidance. I was amused by the
suggestion that you might have done my homework. At age 60, it's been
a while since I did homework, so I'm afraid I won't be getting that A!
Thanks again.

Kevin

You are welcome, Kevin - thanks for feeding back.

When you said you had 40,000 records I thought that would be a bit
much for a homework assignment, so I'm glad I was able to help you in
your task.

Pete

On Jan 20, 9:40*am, KevinUK <kevin.glo...@btinternet.com> wrote:
> Pete,
>
> You are a star. Thanks so much for the guidance. I was amused by the
> suggestion that you might have done my homework. At age 60, it's been
> a while since I did homework, so I'm afraid I won't be getting that A!
> Thanks again.
>
> Kevin