Return first & second word in text string (FIND / SUBSITUTE

[Rpan]

Does anyone know a formula I can use to return the first two words in a cell text string

i.e. in cell A1:
Batch Code Home

Desired result in B1:
Batch Code

I found a formula on MrExcel that will get me the first word - is the formula I desire similar to this ?:
IF(LEN(A1)<>LEN(SUBSITUTE(A1," ","")),LEFT(A1,FIND.......

Any help is appreciated !!
Renee
This message was edited by Rpan on 2002-08-08 20:11

 

[PaddyD]

try:

=LEFT(C1,FIND(" ",C1,FIND(" ",C1,1)+1)-1)

Paddy

EDIT: No if statements in the formula on the assumption that you've only got strings with at least 3 words. If not:

=IF((LEN(C1)-LEN(SUBSTITUTE(C1," ",""))<=1),C1,(LEFT(C1,FIND(" ",C1,FIND(" ",C1,1)+1)-1)))
This message was edited by PaddyD on 2002-08-08 20:25

 

[Rpan]

Awesome Paddy - I didn't realize it would be that easy - thanks

 

[Gaijin]

The easiest way I have found to do this is not through a formula. I click on the column then under data I click on text to columns. when you do this if you leave it on delimited and then choose a space as you seperator it will put the three worods into their own column. Use the following formula to combine the first two back together with a space between the words.

=D3&" "&E3

where d3 and e3 are your new columns with the new single word text in them.

Gaijin

 

[Gaijin]

Paddy I am following you formula till I get to the +1)-1) can you explain that? Is that the spacing?
This message was edited by Gaijin on 2002-08-08 20:27

 

[PaddyD]

On 2002-08-08 20:25, Gaijin wrote:
Paddy I am following you formula till I get to the +1)-1) can you explain that?

=LEFT(C1,FIND(" ",C1,FIND(" ",C1,1)+1)-1)

The 'inner-most' find searches for the position of the first blank space & uses this as the basis of the outer find to find the second space - the +1 is to get the outer find to start looking from the first cell after the first space.

The outer find returns the position of the second space. The -1 is to return a string up to the character before the second space - otherwise you'd end up returning the second space as well as the 2 words.

Paddy

 

[Gaijin]

Ok thank you