[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 95
  • Last Modified:

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 "
0
Ronald Malk
Asked:
Ronald Malk
  • 4
  • 3
2 Solutions
 
Bill PrewCommented:
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
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now