Hi
I need a formula to extract the first two words from a text string,
i have been using =LEFT(B5,FIND(" ",B5)-1) to previously extract the first word (word before the first space), but not sure how to extend this to get the first two words extracted.
Any ideas!!
thanks
Er...try this one...I feel like I made it too long but hey it works.
=TRIM(LEFT(B5,FIND(" ",B5))&MID(B5,FIND(" ",B5),FIND(" ",B5,FIND(" ",B5)+1)-FIND(" ",B5)))
Last edited by Speshul; 08-13-2014 at 11:39 AM.
You should hit F5, because chances are I've edited this post at least 5 times.
Example of Array Formulas
Click the * below on any post that helped you.Originally Posted by Jacc
Something like this will do the trick
=TRIM(LEFT(SUBSTITUTE(A2," ",REPT(" ",99)),200))
or even smaller
=LEFT(A2,FIND(" ",A2,FIND(" ",A2)+1))
Last edited by TheCman81; 08-13-2014 at 11:42 AM.
Excel Guru in the making
<----------If the question has been answered to your satisfication please click the Add Repuation star to the left
That's what I get for using like 4 helper cells to build the formula, haha.
1 Find first space
2 Find second space
3 What's the difference in the spaces
4 how long is second word
5 Combine!
6 Long formula for no reason
Last edited by Speshul; 08-13-2014 at 11:48 AM.
Hi
Thanks Speshal that worked a trick
I'll give the other two ago as well as they do look neater!
thanks All
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks