Solved

Excel VBA Trim function

Posted on 2013-11-15
6
453 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
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
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

Technology Partners: 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

Suggested Solutions

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Outlook Free & Paid Tools
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

751 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