Solved

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

Posted on 2011-09-23
13
275 Views
Last Modified: 2012-05-12
Formula to return left three words separated by spaces, e.g., if 'one two three four xyz', formula returns 'one two three'.
 
0
Comment
Question by:Berry Metzger
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +3
13 Comments
 
LVL 12

Expert Comment

by:kgerb
ID: 36587836
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
 
LVL 12

Assisted Solution

by:telyni19
telyni19 earned 50 total points
ID: 36587875
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
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 36587894
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36587895
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
 
LVL 12

Expert Comment

by:telyni19
ID: 36587906
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
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 36587926
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36587970
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
 
LVL 50

Accepted Solution

by:
barry houdini earned 75 total points
ID: 36587972
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36587987
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 36587993
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36588006
I like Barry's the best...ingenious use of the Substitute function...plus padding the original value with spaces to eliminate any error.
0
 
LVL 12

Expert Comment

by:kgerb
ID: 36588882
I concur.  Barry's is brilliant, simply brilliant.  I would have never thought of that.  Excellent work!

Kyle
0
 

Author Closing Comment

by:Berry Metzger
ID: 36590975
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question