Formula returns first three words separated by spaces in a text string of many words

Formula to return left three words separated by spaces, e.g., if 'one two three four xyz', formula returns 'one two three'.
 
Berry MetzgerLean process improvement consultantAsked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
This formula returns everything before space number 3 - if there are less than 3 spaces it returns the whole cell contents

=LEFT(A1,FIND("^^",SUBSTITUTE(A1&"   "," ","^^",3))-1)

regards, barry
0
 
kgerbChief EngineerCommented:
Try this one.  All the words need to be separated by spaces for this to work.

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

Kyle
0
 
telyni19Connect With a Mentor Commented:
This Excel formula finds the third space in a text string located in cell A1 and returns the left part of the string up to that space:

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

This variation does the same thing, but if an error results, indicating that there are fewer than three spaces in the string, it returns the entire string:
=IFERROR(LEFT(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",N2)+1)+1)-1),A1)
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
David L. HansenProgrammer AnalystCommented:
Sub SplitValue()
    Dim strSplit As Variant
     
    strSplit = Split(Range("A1").Value, " ")

Dim Val1 As Variant
Val1 = strSplit(0)

Dim Val2 As Variant
Val2 = strSplit(0)

etc...
End Sub
0
 
Glenn RayExcel VBA DeveloperCommented:
If the cell containing the string is in cell A1:

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

However, this only works if there are more than three words.  If there is a possibility of less than three words, you need an additional test.
0
 
telyni19Commented:
Ack, typo in my post. Correct version:

This variation does the same thing, but if an error results, indicating that there are fewer than three spaces in the string, it returns the entire string:
=IFERROR(LEFT(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)-1),A1)
0
 
David L. HansenProgrammer AnalystCommented:
Revised:

Sub SplitValue()
    Dim strSplit As Variant
     
    strSplit = Split(Range("A1").Value, " ")

Dim Val1 As Variant
Val1 = strSplit(0)

Dim Val2 As Variant
Val2 = strSplit(1)

etc...
End Sub
0
 
Glenn RayExcel VBA DeveloperCommented:
heh, like telyni19, error in my post:

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

If you are using Excel 2007, her use of IFERROR is recommended.  If using Excel 2003 you will have to use this variation instead:
=IF(ISERR(=LEFT(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1))-1)),A1,=LEFT(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1))-1))
0
 
Glenn RayExcel VBA DeveloperCommented:
Dang me...I wish I could edit a post!  

If using Excel 2003 use this instead to catch possible errors:

=IF(ISERR(LEFT(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1))-1)),A1,LEFT(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1))-1))
0
 
barry houdiniCommented:
I notice that when I copy the formula from here it doesn't always work correctly (unlike my original) - if you copy from here please manually replace the &"   " part - there should be 3 spaces between the quotes

regards, barry
0
 
Glenn RayExcel VBA DeveloperCommented:
I like Barry's the best...ingenious use of the Substitute function...plus padding the original value with spaces to eliminate any error.
0
 
kgerbChief EngineerCommented:
I concur.  Barry's is brilliant, simply brilliant.  I would have never thought of that.  Excellent work!

Kyle
0
 
Berry MetzgerLean process improvement consultantAuthor Commented:
All of you... you are all amazing and inventive!  I did like the simplicity of Barry's solution best, therefore, I have awarded him the most points.

telyni19 was first to respond.    
Thank you all for your quick responses.
And Barry, thanks again for your help, this being the second or third time!!
Berry
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.