Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    May 2001
    Location
    Kenilworth, Warwickshire, England
    Posts
    269
    Thanks
    3
    Thanked 0 Times in 0 Posts

    searching a string (Office 2k)

    I am going to spend a very long time, largely in trial and error mode, trying to do something using excel functions which I am sure is better tackled with a bit of vba code. I am unfamiliar with vba, and would welcome some help to at least get me started.

    I need to look for and populate a cell with a sub-string of the form "Jxxx" where J is J and x is an alpha. the to-be-searched string is a long string made up of a succession of groups of 4, mostly of the form "xnnx" where x is alpha and n is numeric. Both the leading and trailing alphas could be the letter J, and such a group could immediately precede or follow my target group. Thus the test needs to spot each "J" in the target string, test it and then continue looking for another. I think there will be one, and only one per target string matching my pattern. I may well want to return the next 4 characters following my search pattern as well as the actual four. That is test for four, and return 8.

    Thanks in anticipation,

    Mike C

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: searching a string (Office 2k)

    I am not sure I understand.
    Could you give some varied examples of what you want to search and what you want the results to be (ie what should the formula yield)? It sounds like either several intermediate formulas or perhaps a user defined function is what you need.

    If you have to "examine" each "J" in a string, I doubt whether the formula approach will be practical

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts

    Re: searching a string (Office 2k)

    Not sure of what you want. However, using the string formulas in Excel I was able to do what I believe you want.

    If not explain what is different.

    See attached.

    Regards,

    TD

  4. #4
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: searching a string (Office 2k)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Mike

    I also don't really get the idea that you are trying to get to.

    I have several questions:

    1) Is the string delimited with some character, say a space, or is it continuous? When you say <<< succession of groups of 4, mostly of the form "xnnx" >>> I get the image of 4 characters, and then a space and then 4 more characters and then a space and it goes on and on. Is that accurate?

    2) When you say Jxxx does that mean Uppercase J, or any J? This could be picked up by =CHAR(74) which is upper case J. Lower case J is CHAR(106)

    3) You say: <<< Both the leading and trailing alphas could be the letter J >>> that means you need to look for a J followed by an alphabetic character and not by a numeric, since Jxxx is alpha, but Jn is numeric, this should be easy to figure out.

    4) OK when you say <<< such a group could immediately precede or follow my target group. >>> that means you are looking for a J followed by an alpha and not simply a J. Look for the J and test its position+1 to see if its an alpha or numeric, if Alpha extract the characters Position of J to (position of J + 3).

    5) You say <<< Thus the test needs to spot each "J" in the target string, test it and <font color=red>then continue looking for another.</font color=red> >>> and <<< I think there will be one, <font color=red>and only one </font color=red>per target string matching my pattern. >>> I am <img src=/S/confused.gif border=0 alt=confused width=15 height=20>, could there be multiple target strings in the search string, or is it unique? Not a problem, since you can look and re-search the search string from the position of the last J you found, but that is extra work.

    6) Mike!!! Did you see that Aflac advertisement with the duck and Yogi Berra? The duck's expression matches mine when I read <<< may well want to return the next 4 characters following my search pattern as well as the actual four. That is test for four, and return 8. >>> <img src=/S/confused.gif border=0 alt=confused width=15 height=20> <img src=/S/drop.gif border=0 alt=drop width=23 height=23>

    So you are saying that you want to extend the capture from 4 to 8 characters, but based on what?

    Hope this helps.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  5. #5
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: searching a string (Office 2k)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> TD

    Mike does not know what the string will be other than it starts with a J and has alphabetic behind it.

    If we knew what the string was, then we can plug it into cell B3 and your formula will work.

    I am afraid this needs VBA, because the string needs to be searched for a J and then look for what is coming next.

    Great try though.

    Happy Holidays

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  6. #6
    3 Star Lounger
    Join Date
    May 2001
    Location
    Kenilworth, Warwickshire, England
    Posts
    269
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: searching a string (Office 2k)

    (Edited by HansV to break extremely long string without spaces that caused horizontal scrolling. For those who read this thread: please treat it as one long string without any breaks.)

    Thanks guys.

    Here is a typical string I need to search. as you will see there are no delimiters.
    A10RA21FA30NA40AA50AA63HA70EA80MA99VB02BB05XB06BB1 1CB12CB15XB19NB23CB25BB27
    CB32DB36CB38CB39XB44DB45SB47ZB49AB52DB61DB72DB73AB 90VB92ZB93CB96CB98DC03G
    C06ZC08EC14AC18DC28AC30BC31GC35LC36AC38FC40PC42MC4 7DC53CC58JC63ZC68AC71F
    C72CC75TC76BC88BC94BD01XD05FD06ED14TD20BD30AD33CD3 5ZD37AD47DD49WD69BD70Z
    D78AD81CD82CD89AD90GD92AE04EE22NE65BF02EF04KF09BF1 3EF14RF26JF28MF35RF38AF39
    RF41CF47HF48AF58ZF61GF64BF66FF73CG01AG04CG29BG37DG 48AG80NH02BH05BH27AH51C
    H54AH62BH71FH75AH76BH77CH78K
    <<<JJGD>>>
    KPPA N03EP02DR09BU18AU33WU43HW02AX01BX04EX09AX13JX19KX4 3AX99DZ79YZ91C.

    For clarity, I have surrounded a sub-string that satisfies my criteria thus <<<JJGD>>> in reality it is just part of the continious string.

    1) i do not know what the 4 characters are other than they are all alpha and start with J
    2) the preceeding four characters could be H78J. In fact, there could be any number of such groups with a trailing J before and after my target group
    5) I would like to retrieve JJGD and KPPA in this example from the string

    Hope this clarifies

    Mike C

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: searching a string (Office 2k)

    The Excel gurus will probably come up with a formula solution (perhaps an array formula), but here is a VBA function that hopefully does what you want. The code should be copied to a standard module in the Visual Basic Editor.

    Public Function FindPattern(TextValue, Optional HowMany As Long = 4) As String
    Dim i As Long
    Dim j As Long
    Dim n As Long
    Dim c As Long
    Dim f As Boolean
    FindPattern = ""
    n = Len(TextValue)
    For i = 1 To n - 3 Step 4
    If UCase(Mid(TextValue, i, 1)) = "J" Then
    f = True
    For j = 1 To 3
    c = Asc(UCase(Mid(TextValue, i + j, 1)))
    If c < 64 Or c > 91 Then
    f = False
    Exit For
    End If
    Next j
    If f Then
    FindPattern = Mid(TextValue, i, HowMany)
    Exit For
    End If
    End If
    Next i
    End Function

    Example of use: if your long text is in cell A1, put the formula =FindPattern(A1) in another cell to return JJGD, or =FindPattern(A1,8) if you want to return JJGDKPPA.

    Added: I have assumed that:
    - The entire string is to be chopped up into pieces of length 4, so I only look for the pattern starting at position 1, 5, 9, 13, etc.
    - You are looking for the first occurrence of the pattern.
    - You are looking for characters in the range A ... Z. If you also want to take other characters into account, the test in the inner loop will have to be adapted.

  8. #8
    3 Star Lounger
    Join Date
    May 2001
    Location
    Kenilworth, Warwickshire, England
    Posts
    269
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: searching a string (Office 2k)

    Hans, that was absolutely spot on.

    Tell you what though, I cut and pasted the code not from the lounge, but from the text version that was in the email that notified me of your reply.

    the email changed your " < " into " < " and the code would not compile! spotted it and away I went

    Thank you very much

    Mike C

  9. #9
    3 Star Lounger
    Join Date
    May 2001
    Location
    Kenilworth, Warwickshire, England
    Posts
    269
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: searching a string (Office 2k)

    Guys,

    this is impossible! the act of posting my previous reply was to change the characters that I actually keyed-in (in the lounge!) back into greater or less than signs. I think you get my drift - the characters get changed by the process of poting or emailing!

    Thanks again Hans

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: searching a string (Office 2k)

    Sometimes, the "greater than" or "less than" symbols are replaced by HTML code. Apparently it works both ways, see screenshot of your reply.

  11. #11
    3 Star Lounger
    Join Date
    May 2001
    Location
    Kenilworth, Warwickshire, England
    Posts
    269
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: searching a string (Office 2k)

    mmmmmmmm"

    Mike

Posting Permissions

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