Excel VBA Trim function

I encounter this value...

GQ-000 - Regulatory Guidances

Is there some sort of function that would allow me to trim the string so I just get...
GQ-000

In other words get the 1st group of characters until the space is encountered.

I also may encounter
GQ-010_2 - Legalization
I just want the value
GQ-010_2
tesla764Asked:
Who is Participating?
 
byundtCommented:
Assuming that your space is ASCII 32:
Dim s As String, sInput as String
s = Left(sInput, InStr(1, sInput & " ", " ") -1)

Open in new window

0
 
unknown_routineCommented:
Dim MyString As String
MyString = "GQ-000 - Regulatory Guidances"


Dim FirstGroup As String

Dim arr

arr = Split(MyString, " ") 'split string by spaces

FirstGroup = arr(0)
0
 
unknown_routineCommented:
Another way:

Dim MyString As String
MyString = "GQ-000 - Regulatory Guidances"


Dim FirstGroup As String

FirstGroup = Left(MyString, InStr(MyString, " ") - 1)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Martin LissOlder than dirtCommented:
unknown's code can be modified like this

Dim FirstGroup As String


FirstGroup = Split(MyString, " ")(0)

Open in new window

0
 
byundtCommented:
And if you want to handle the possibility of a non-breaking space, you could use either of:
Dim s As String, sInput as String
s = Split(Replace(sInput,Chr(160)," ")," ")(0)
s = Left(sInput, InStr(1, Replace(sInput,Chr(160)," ") & " ", " ") -1)

Open in new window


I'm liking the Split method suggested by unknown_routine and MartinLiss better than my Left and InStr.
0
 
tesla764Author Commented:
Thanks everybody they were all great solutions.
0
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.

All Courses

From novice to tech pro — start learning today.