Find the first space in a string and only return the characters before that space using vba

Posted on 2012-03-15
Medium Priority
Last Modified: 2012-03-16
Is there an easy way to find in a string and only return the characters before the first space in the string.

For example: married  divorce single blabla

should return married in this example

many thanks!!
Question by:BauwensER
LVL 61

Accepted Solution

mbizup earned 2000 total points
ID: 37725041
Try this:

Dim strYourString as string
strYourString = "married divorced etc"

msgbox Split(strYourString," ") (0)
LVL 77

Expert Comment

ID: 37725116
If you want this ina query then...

Left(yourfield , instr(yourfield & " ", " ")-1)

Expert Comment

ID: 37725133
The following will break up the source string [the first argument to Split()] using the second argument as the delimiter.  
The delimter may be more than one character in length.  
If the source string is empy then the size of the array will be -1.
If the delimter is not found then the whole string is returned in element 0 of the array.
Dim mystring() As String

Sub testme()

   mystring = Split("this is a sample string", " ")
   if ubound(mystring) >= 0 then
      Debug.Print mystring(0)

End Sub

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

627 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