Conditionally trimming using VBA

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 "
Ronald MalkAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
John TsioumprisConnect With a Mentor Software & Systems EngineerCommented:
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

0
 
Bill PrewConnect With a Mentor Commented:
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)
    Else
        GetHoliday = ""
    End If

End Function

Open in new window


»bp
0
 
John TsioumprisSoftware & Systems EngineerCommented:
I think this will help you
split("fdswfwekfwHollidayewfw","Holliday")(1)

Open in new window

0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Ronald MalkAuthor Commented:
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
0
 
Ronald MalkAuthor Commented:
Thanks jhon, It splits ok but it keep all the words behind it, I want just one word after "Holliday"
0
 
Ronald MalkAuthor Commented:
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
0
 
Ronald MalkAuthor Commented:
Thanks to All
0
 
John TsioumprisSoftware & Systems EngineerCommented:
Glad you worked it out
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.