Solved

Excel VBA Trim function

Posted on 2013-11-15
6
451 Views
Last Modified: 2013-11-15
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
0
Comment
Question by:tesla764
[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
6 Comments
 
LVL 15

Assisted Solution

by:unknown_routine
unknown_routine earned 100 total points
ID: 39651694
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
 
LVL 81

Accepted Solution

by:
byundt earned 350 total points
ID: 39651703
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
 
LVL 15

Assisted Solution

by:unknown_routine
unknown_routine earned 100 total points
ID: 39651706
Another way:

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


Dim FirstGroup As String

FirstGroup = Left(MyString, InStr(MyString, " ") - 1)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 47

Assisted Solution

by:Martin Liss
Martin Liss earned 50 total points
ID: 39651714
unknown's code can be modified like this

Dim FirstGroup As String


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

Open in new window

0
 
LVL 81

Assisted Solution

by:byundt
byundt earned 350 total points
ID: 39651722
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
 

Author Comment

by:tesla764
ID: 39651736
Thanks everybody they were all great solutions.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
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.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

740 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