How can I get Excel (2003) to return just the first 4 words (of another cell)?

S

ship

Hi

How can I get Excel (2003) to return just the first 4 words of another
cell?
With thanks


Ship
Shiperton Henethe
 
R

Ron Rosenfeld

Hi

How can I get Excel (2003) to return just the first 4 words of another
cell?
With thanks


Ship
Shiperton Henethe
Click to expand...

What do you want to do if there are less than four words in the cell?

This should get you started. Substitute what you want for less than four words
for the string in the formula below.

=IF((LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ","")))<4,
"LESS THAN FOUR WORDS",
LEFT(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),4))-1))


--ron
 
D

Duke Carey

Assuming you have no carets (^) in your cell, and assuming the cell with the
string you want to act on is A1, try

=LEFT(A1,FIND("^",SUBSTITUTE(A1," ","^",4))-1)
 
M

Mike H

try this with your string in A1

=LEFT(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND("
",A1,1)+1)+1)+1)))

Mike
 
Top