Conditionally trimming using VBA

Posted on 2017-05-19
Medium Priority
Last Modified: 2017-05-19
Hi every one, I Hope someone can help with conditionally trimming using VBA
I have a long text string will vary from time to time but only one word "Holliday" is always existed somewhere in the text string, what I need is to find the word or the number just after "Holliday"
Will be described like this; known word is "Holliday"&" "&"Targeted word"&"  "

Like this: me.txtbx.value= whatever word is or number after the word "Holliday"
This coming holiday 04WWE will be delayed
me.txtbx.value will display "04WWE"
After 3 minth it will be a holiday 09DDD must remember
me.txtbx.value will display "09DDD "
Question by:Ronald Malk
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
  • 4
  • 3
LVL 56

Assisted Solution

by:Bill Prew
Bill Prew earned 1000 total points
ID: 42142014
Here is an example of a regular expression approach, probably how I would approach it.  I created a small function which takes in the value to process, and returns the holiday identifier you want.  You will need to add a reference to the Microsoft VBscript Regular Expressions Library in your VBA project.

Option Compare Database
Option Explicit

Sub Test()

    MsgBox GetHoliday("This coming holiday 04WWE will be delayed")

End Sub

Function GetHoliday(strValue As String) As String

    Dim objRegex As New RegExp
    Dim colMatch As MatchCollection

    objRegex.MultiLine = False
    objRegex.Global = True
    objRegex.IgnoreCase = True

    objRegex.Pattern = "holiday\s+\b(\w+)\b"
    If objRegex.Test(strValue) Then
        Set colMatch = objRegex.Execute(strValue)
        GetHoliday = colMatch.Item(0).Submatches.Item(0)
        GetHoliday = ""
    End If

End Function

Open in new window

LVL 18

Expert Comment

by:John Tsioumpris
ID: 42142024
I think this will help you

Open in new window


Author Comment

by:Ronald Malk
ID: 42142037
Thank you for the reply, I was hopping that trimming can be used instead because I'm not familiar how to make use of this type of code, see I have got a button when clicked will scan the text string in control TxInfo  then will search for the word after the word "Holliday"  and will insert it in control TxRslt in the form
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.


Author Comment

by:Ronald Malk
ID: 42142039
Thanks jhon, It splits ok but it keep all the words behind it, I want just one word after "Holliday"
LVL 18

Accepted Solution

John Tsioumpris earned 1000 total points
ID: 42142058
So you can have many words after Holliday ? if this is the case use the delimiter that separate after the words returned by Holliday and split again....(i guess the delimiter is space...if not give a couple examples) like this
Split(Trim(split("fdswfwekfwHolliday OneWord TwoWord ....","Holliday")(1))," ")(0)

Open in new window


Author Comment

by:Ronald Malk
ID: 42142088
Thank John, actually the idea you suggested was very helpful, I only added to it the left function and it works perfect, have a look at it:
Private Sub Btnne_Click()
Dim stRst As String
stRst = Split(Txt_A, "Holiday ")(1)
Rslt = Left(stRst, InStr(1, stRst, " ") - 1)
End Sub

Author Closing Comment

by:Ronald Malk
ID: 42142093
Thanks to All
LVL 18

Expert Comment

by:John Tsioumpris
ID: 42142119
Glad you worked it out

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

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

764 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