Extracting first 2 words

[Guest]

I have the following line items:

Dobey Scissors XA
Duey Staples DDG
Dobey Paper FFF

I need to turn those values to:

Dobey Scissors
Duey Staples
Dobey Paper

How do I extract the first 2 words for each line item if the character
lengths are different?

Thanks!!

 

[Tom Ellison]

Dear Yvette:

The InStr function is your friend. For the second space:

InStr(InStr("Hello there George", " ") + 1, "Hello there George", " ")

This returns 12. Replace both strings "Hello there George" with the column
name.

Tom Ellison

 

[Guest]

If the field always consist of three words, you can try

Left([FieldName],InStrRev([FieldName]," ")-1)
=================================
Or, create a function in a module

Function MySplit(MyStr As String, MyLocation As Integer)
MySplit = Split(MyStr, " ")(MyLocation)
End Function

And then use it as
MySplit([FieldName], 0) & " " & MySplit([FieldName], 1)

 

[Guest]

Fabulous, thank you!

Dear Yvette:

The InStr function is your friend. For the second space:

InStr(InStr("Hello there George", " ") + 1, "Hello there George", " ")

This returns 12. Replace both strings "Hello there George" with the column
name.

Tom Ellison

 

[Tom Ellison]

Dear Yvette:

Don't get totally extatic yet.

Now, if the column has any values in it that don't have the requisite 2
spaces, you're going to get an error. Also, consider what would happen if
there were two consecutive spaces. I assume a user has been typing in these
names. There may not be as much consisitency as you expect.

Also, it may work well now, but fail in the future when odd looking values
are entered. I recommend you consider my words, and the exeptions I
mention. Shall we do a bit more?

Tom Ellison