Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

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'.
 
0
Berry Metzger
Asked:
Berry Metzger
  • 4
  • 2
  • 2
  • +3
2 Solutions
 
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
 
telyni19Commented:
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
barry houdiniCommented:
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
 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now