Solved

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

Posted on 2011-09-23
Medium Priority
284 Views
Formula to return left three words separated by spaces, e.g., if 'one two three four xyz', formula returns 'one two three'.

0
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

LVL 12

Expert Comment

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

telyni19 earned 200 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

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

LVL 27

Expert Comment

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

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

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

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

barry houdini earned 300 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

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

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

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

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

Kyle
0

Author Closing Comment

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

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Suggested Courses
Course of the Month7 days, 19 hours left to enroll

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

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