Conditionally trimming using VBA

Posted on 2017-05-19
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 54

Assisted Solution

by:Bill Prew
Bill Prew earned 250 total points
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 17

Expert Comment

by:John Tsioumpris
I think this will help you

Open in new window


Author Comment

by:Ronald Malk
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
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!


Author Comment

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

Accepted Solution

John Tsioumpris earned 250 total points
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
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
Thanks to All
LVL 17

Expert Comment

by:John Tsioumpris
Glad you worked it out

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Join & Write a Comment

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

739 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