Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Matching text at start of string

    If I want to match text at the start of a string, how do I do that? I know it is easy in PERL, but can't figure it out in VBA.

    E.g. " " at start of " any text can go here" in PERL would be (/^ /).

    Note, I do not want to replace this text at this point - I simply want to know if there are leading spaces (I cannot guarantee that there will not be the same number of spaces elsewhere in the line at some point in the file, so I want to be sure I only identify lines with leading spaces).

    Thanks for any help on this.

    Kiwi44

  2. #2
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Matching text at start of string

    Kiwi,

    Something like:

    If Left(strString, 1) = " " Then ..

    or

    if mid(strString, 0, 1) = " " then

    might work
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Matching text at start of string

    There are many ways to do this. If I understand what you are trying to do, I would probably do something like this:

    <pre> If (Left(Trim(strMyString),n) = "string to match") then
    </pre>


    Where n is the lenght of "string to match".
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Matching text at start of string

    Thanks, Geoff (and Legare)

    In playing around with the problem after I posted the request, I also found that Instr(1, strString," ") works.

    A new question (which I do not have further time to play around on tonight!) is how do I replace "_(_DIG. LIVER DIS._)" with ""? I have tried a variety of combinations, but nothing seems to work. I find the text OK with If myString Like "*_(_*", but can't what to use in Replace(myString,?,"").

    As the periods could appear in any position in the string, and with different letters in between, what I am looking for perhaps is something that will match one or more of either characters, spaces, or periods, in any order.

    Is this possible in VB?

    Kiwi44

  5. #5
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Matching text at start of string

    Kiwi,

    Are we talking about text in a document, in a spreadsheet, or in text from somewhere else?

    The reason I ask is that, if it's in a Word document, you can use wildcard searches for matching patterns; in VBA just on straight text, you may be able to achieve what you want, but it will be more difficult.

    What you want may be something like (though I'm not quite certain what you're trying to achieve) is (and this is pretty rough):

    <pre>Dim lPosFrom As Long, lPosTo As Long, strString As String
    lPosFrom = InStr(strString, "_(_")
    lPosTo = InStr(strString, "_)")
    If lPosFrom > 0 And lPosTo > lPosFrom Then
    If lPosTo = Len(strString) Then
    strString = Mid$(strString, 1, lPosFrom - 1)
    Else
    strString = Mid$(strString, 1, lPosFrom - 1) & Mid$(strString, lPosTo + 2)
    End If
    End If
    </pre>

    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Matching text at start of string

    Geoff has given you a way to replace the text in the string. However, I want to make one comment about using Instring. Your initial question said that you wanted to find the text at the beginning of the string, and that is why I answered using the Trim and Left functions. I did not use Instring because Instring will find the text anywhere in the string. If that is what you want to do, then using it is fine. However, if you only want to find the text at the start of the string, it will not work as you expect.
    Legare Coleman

  7. #7
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Matching text at start of string

    Hi Kiwi and Legare

    Leasgre, you are right about Kiwi's solution. I didn't twig, and I should have.

    Kiwi, your solution gives the correct solution to the string " something". But it also gives the same anser for "something something else".

    The "Instr(start, string, find)" function says, 'Give me the first occurrence of "find" in "string" starting from "start"'

    The function "left(strString, length)" function gives me the leftmost charcacters in string "string" for length "length"

    the function "mid(strString, start, length)" gives me the string in the middle of string "strString" starting from "start" for length "length".

    Legare's example also shows something else quite important. The "trim" function gets rid of leading and trailing spaces from a string. That's *really* important when you're storing data into a database. I work in police systems- if you do a search for "Whitfield", it's possible NOT to get a match on "Whitfield " (or even on "whitfield" without the capital W if you don't design properly).
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •