Berry Metzger
asked on
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'.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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.
=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.
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)
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)
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
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
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))
=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(
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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(A 1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1))-1))
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(A
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
regards, barry
I like Barry's the best...ingenious use of the Substitute function...plus padding the original value with spaces to eliminate any error.
I concur. Barry's is brilliant, simply brilliant. I would have never thought of that. Excellent work!
Kyle
Kyle
ASKER
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
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
=LEFT(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,1)+1)+1)-1)
Kyle