Count number of words in a string....

AndyD said:

hi
if can help with this would be appreciated

need to be able to count the number of words in a string

eg
if cell A1 = "Hello there"..returns 2
if cell A1 = "Hello there my friend"..returns 4

etc
thanks
A

Click to expand...

Using Morefunc Add-in,

=WORDCOUNT(A1)

also need to able to abbreviate words in a string

for example i need to abbreviate

London South East as "London SE"

London South West as "London SW"

London South as "London S" etc

Thanks
A

AndyD said:

also need to able to abbreviate words in a string

for example i need to abbreviate

London South East as "London SE"

London South West as "London SW"

London South as "London S" etc

Thanks
A

Click to expand...

Since Brian has already goaded you to invoke a function from the morefunc add-in, we might continue using it for the above query as well...

Formulas...

B2:

=WMID($A2,COLUMN()-COLUMN($B$2)+1,1)&" "&UPPER(LEFT(C2)&LEFT(D2))

C2, which must be copied across:

=WMID($A2,COLUMN()-COLUMN($B$2)+1,1)

Select B2:D2 and copy down.

Note that the formula in B2 assumes a string of 3 words at most, but, if needed, the formula generalized for longer input strings.

FYI...related topic...http://www.mrexcel.com/board2/viewtopic.php?t=67649