Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 89
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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