# Copy everything before the 2nd space

#### daniels012

##### Well-known Member
I have 6 words in column A
I want to put the 2 left most words into column B.
What is a formula that will give me such data??
The words are varied in size.

Thank You,
Michael

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### Peter_SSs

##### MrExcel MVP, Moderator
daniels012

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

#### daniels012

##### Well-known Member
You are a blessing. Thank You so much. I have been working on this for about an hour.

So, if I wanted the 3 left words would I...

Try =LEFT(A1,FIND(" ",A1,FIND(" ",A1),A1,Find(" ",A1)+2)+1)-1)

?? I don't know??

Thank You!!
Michael

#### Peter_SSs

##### MrExcel MVP, Moderator
daniels012 said:
You are a blessing. Thank You so much. I have been working on this for about an hour.

So, if I wanted the 3 left words would I...

Try =LEFT(A1,FIND(" ",A1,FIND(" ",A1),A1,Find(" ",A1)+2)+1)-1)

?? I don't know??

Thank You!!
Michael
No, the syntax in your formula is incorrect. However, rather than extending the ideas in my previous post, if the number of words to be extracted might change or is more than two, then I suggest this alternative approach.

I have put the number of words to be extracted in C2 for ease of testing (but you could build it into the formula if you want. The formula in B1 is:
=LEFT(A1,FIND("@",SUBSTITUTE(A1&" "," ","@",C2))-1)
Mr Excel.xls
ABCD
1one two three four five sixone two threeNo of words required from A1
23
3
Left 2 Words

#### daniels012

##### Well-known Member
Thank You SOOOOOOOOOOOOO much!

Michael

Replies
4
Views
662
Replies
5
Views
451
Replies
14
Views
495
Replies
5
Views
263
Replies
4
Views
162

1,195,695
Messages
6,011,173
Members
441,591
Latest member
elmogm

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back