How to count all 2 letter words in a cell or group of cells.

Hello. I'll get right to it for those of you who don't have time for small talk.

How do I create a formula to count all the two letter words in a single cell or group of cells?

ex.
CELL A1 contains a long sentence with the following to letter words: Im, up, it, or, me. (5 two letter words)
CELL A2 contains the words "at" and "no." (2 two letter words)

is there a way of only counting those 2 letter words WITHOUT:
1. splitting the sentence into a million cells, each containing one word and then using the LEN function to count cells containing two character words.
2. creating a database of all two letter words to search for within those cells.

The first option is too time consuming to be practical.
The second option would have to be a huge database containing ALL two letter combinations because the way i need to use this formula will not only apply to two letter "words" but also two letter "combinations." i.e. RN, TX, MU, etc. In addition, I believe this would also only count the cell containing multiple two letter words as ONE instead of tallying the total 2 letters words in that cell.

Anyway, is this even possible.

I thank you in advance for any help rendered.

Best Regards,
Agustin

Hmmm I think works fine (NO ITS NOT see comments below):

DELETE

EDIT: no, its not fine. In case of 2 letter word and comma straight after it treat it as 3 letter word.

Please post a sample sheet containing a few rows of REPRESENTATIVE data.

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Make sure confidential info is removed first!!!!

	A	B
1	ex.	1
2	CELL A1 contains a long sentence with the following to letter words: Im, up, it, or, me. (5 two letter words)	6
3	CELL A2 contains the words "at" and "no." (2 two letter words)	2
4	is there a way of only counting those 2 letter words WITHOUT:	2
5	1. splitting the sentence into a million cells, each containing one word and then using the LEN function to count cells containing two character words.	1
6	2. creating a database of all two letter words to search for within those cells.	2

	A	B
2	CELL A1 contains a long sentence with the following to letter words: Im, up, it, or, me. (5 two letter words)	=CountXWords(A2)

Sheet: Sheet1

For some unknown reason firewall treat this function like SQL injection and blocked post so unfortunately as picture:

qq.JPG


EDIT: with checking that A1 and A2 is not a word.

Please Login or Register  to view this content.

ex.
.....
The first option is too time consuming to be practical.
The second option would have to be a huge database containing ALL two letter combinations because the way i need to use this formula will not only apply to two letter "words" but also two letter "combinations." i.e. RN, TX, MU, etc. In addition, I believe this would also only count the cell containing multiple two letter words as ONE instead of tallying the total 2 letters words in that cell.

Perhaps not. For example in the attached is a lookup table with all combinations a-z&a-z. It is named alpha_2. The formula to concatenate those is

Formula:

Please Login or Register  to view this content.

Then to count those in B1 and filled down.

Formula:

Please Login or Register  to view this content.

Key is to predict and remove all alphanumerics which can appear near letters (!?;.,()[] etc.)
Then it can be 'bite from few sides'.

Originally Posted by Rick Rothstein

I got caught with that not too long ago... the problem is the consecutive Replace function calls. If you put a space after each Replace's opening parenthesis, the code can be posted.

Yup.
Wrapping REPLACE in color tags also works.