Solved

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

Posted on 2011-09-23
13
271 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
  • 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

839 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