• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

InStr statement that searches for the first occurence of a digit after a specified word

I have a series of macros that insert backslashes at different points in a string to set it up for text to columns. I need something to find the string position of the first digit after the word "CLOSED". How do I write that? Assuming that it can be done with a simple InStr statement.

Thanks,
John
Dim x As Range, Pos As Long, z As String, w As String
Set x = ActiveCell
Pos = InStr(x, "CLOSED") + 6
z = Left(x, Pos)
w = Mid(x, Pos, 300)
x = z & "\ " & w

Open in new window

0
gabrielPennyback
Asked:
gabrielPennyback
  • 4
  • 3
  • 2
1 Solution
 
jppintoCommented:
I'm not sure what you want to do here...I've tested your code with this string:

rwerCLOSEDddsfsdfsdf

and got this result:

rwerCLOSEDd\ ddsfsdfsdf

Isn't this what you wanted to do? If it is, then your code is working fine...

jppinto
0
 
Patrick MatthewsCommented:
John,

If the next digit after CLOSED can be any number of characters after the word, then I would try Regular Expressions.

1) Add the function RegExpReplace from my article http://www.experts-exchange.com/Programming/Languages/Visual_Basic/A_1336-Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html (source code below)

2) Use it in your code like this:

Dim x As Range, z As String
Set x = ActiveCell
z = ActiveCell.Value
z = RegExpReplace(z, "(CLOSED)(\D*)(\d)", "$1$2\$3")
x = z

Open in new window



The source code for the UDF:

Function RegExpReplace(LookIn As String, PatternStr As String, Optional ReplaceWith As String = "", _
    Optional ReplaceAll As Boolean = True, Optional MatchCase As Boolean = True, _
    Optional MultiLine As Boolean = False)
    
    ' Function written by Patrick G. Matthews.  You may use and distribute this code freely,
    ' as long as you properly credit and attribute authorship and the URL of where you
    ' found the code
    
    ' For more info, please see:
    ' http://www.experts-exchange.com/articles/Programming/Languages/Visual_Basic/Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html
    
    ' This function relies on the VBScript version of Regular Expressions, and thus some of
    ' the functionality available in Perl and/or .Net may not be available.  The full extent
    ' of what functionality will be available on any given computer is based on which version
    ' of the VBScript runtime is installed on that computer
    
    ' This function uses Regular Expressions to parse a string, and replace parts of the string
    ' matching the specified pattern with another string.  The optional argument ReplaceAll
    ' controls whether all instances of the matched string are replaced (True) or just the first
    ' instance (False)
    
    ' If you need to replace the Nth match, or a range of matches, then use RegExpReplaceRange
    ' instead
    
    ' By default, RegExp is case-sensitive in pattern-matching.  To keep this, omit MatchCase or
    ' set it to True
    
    ' If you use this function from Excel, you may substitute range references for all the arguments
    
    ' Normally as an object variable I would set the RegX variable to Nothing; however, in cases
    ' where a large number of calls to this function are made, making RegX a static variable that
    ' preserves its state in between calls significantly improves performance
    
    Static RegX As Object
    
    If RegX Is Nothing Then Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = PatternStr
        .Global = ReplaceAll
        .IgnoreCase = Not MatchCase
        .MultiLine = MultiLine
    End With
    
    RegExpReplace = RegX.Replace(LookIn, ReplaceWith)
    
End Function

Open in new window


Patrick
0
 
gabrielPennybackAuthor Commented:
jppinto, here's what I'm trying to do.
 
I WANT TO TURN THIS:
" ... Further chks carried  out satis. CLOSED  04 Apr 11  08:28  04 Apr 11  10:53  2Hrs  25Min"

INTO THIS:
" ... Further chks carried  out satis. \ CLOSED \ 04 Apr 11  08:28 \ 04 Apr 11  10:53  \ 2Hrs  25Min"

Patrick, your solution is great. How would I expand/adapt it to produce the last two backslashes surrounding the two dates? (Also, though it doesn't matterin this tect to columns case, how would I produce the backslash after CLOSED so that it had a space after it?)

Thanks,
John
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
gabrielPennybackAuthor Commented:
Never mind about the added space, I figured that one out. Just add a space before "$3"  :-)
0
 
jppintoCommented:
On your original post, the question was not clear! This is a completly different thing! You want to add multiple backslashes, not just one.
0
 
gabrielPennybackAuthor Commented:
Hi jppinto, I think it was my last post that was misleading. In my mind I was just giving you the larger context. All I actually expect is the answer to my original question. I only asked Patrick about getting the last two backslashes because he changed to game to RegEx and I've asked him followups in the past so that I can get a clearer understanding of how a new RegEx expression works.

Forgive me for alarming you. if you have an InStr solution for my question as originally posed, I'd be thankful for just that.

John
0
 
Patrick MatthewsCommented:
John,

I've no objection to the follow-up as long as the amount of time required is small; otherwise, it would be best to start a new question, I think.

Can you please state exactly what the rules are for where you want to place the backslashes?
0
 
gabrielPennybackAuthor Commented:
Thanks Patrick. If it's easy to do and you have the time and inclination, please let me know how to modify the code to pick up on the colons. Or perhaps better still, is there an online library of expressions that gives real life examples and meticulously explains all the syntaxes? For example, what does each character in this line mean: z = RegExpReplace(z, "(CLOSED)(\D*)(\d)", "$1$2\$3")  And what would be some other allowable values that would produce other results. Would this work:
RegExpReplace(z, "(CLOSED)(\s*)(\m)", "$a$b\$c"). I assume not, but what values other than 'D*' and 'd' would be allowable in this line?

I'm not looking for you to answer these questions specifically. What I'm hoping to find is an online resource that cuts right to the chase and actually answers questions like this without having to wade through a 300-page book. Not a pleasant prospect for the ADD's amongst us :-)

In any event, thank you very much.

- John
0
 
Patrick MatthewsCommented:
John,

Sorry, was away for a few days on business.  Something like this should work:

z = Replace(z, "CLOSED", "\ CLOSED")
z = RegExpReplace(z, "(CLOSED|\d\d [A-Z][a-z]{2} \d\d  \d\d:\d\d)", "$1 \")

Open in new window


That pattern, in English:

Match the string CLOSED, or (the pipe character acts as an "or" operator) a string that starts with two digits (\d = digit), is followed by one upper case and two lower case letters, then two more digits, then 2 sapces, then two digits, then a colon, then two more digits.

Because that pattern is in parentheses, it constitutes a submatch.

In my replacement string, I can use $# as a token to stand for my submatches: $1 is the first submatch.  Thus, my replacement string is the first submatch, followed by a space and a backslash.

This syntax is briefly described in my article, in the section "Quick Primer on Pattern Syntax".

If you find yourself using Regular Expressions a fair amount, I would suggest picking up the inexpensive VBScript in a Nutshell.  It has a few pages on Regular Expressions, and for the most part is very good, although for ome odd reason they ignore submatches and look-aheads.

Patrick
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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