searching a string (Office 2k)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

(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.
A10RA21FA30NA40AA50AA63HA70EA80MA99VB02BB05XB06BB11CB12CB15XB19NB23CB25BB27
CB32DB36CB38CB39XB44DB45SB47ZB49AB52DB61DB72DB73AB90VB92ZB93CB96CB98DC03G
C06ZC08EC14AC18DC28AC30BC31GC35LC36AC38FC40PC42MC47DC53CC58JC63ZC68AC71F
C72CC75TC76BC88BC94BD01XD05FD06ED14TD20BD30AD33CD35ZD37AD47DD49WD69BD70Z
D78AD81CD82CD89AD90GD92AE04EE22NE65BF02EF04KF09BF13EF14RF26JF28MF35RF38AF39
RF41CF47HF48AF58ZF61GF64BF66FF73CG01AG04CG29BG37DG48AG80NH02BH05BH27AH51C
H54AH62BH71FH75AH76BH77CH78K
<<>>
KPPA N03EP02DR09BU18AU33WU43HW02AX01BX04EX09AX13JX19KX43AX99DZ79YZ91C.

For clarity, I have surrounded a sub-string that satisfies my criteria thus <<>> 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

Reply | Quote

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 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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

mmmmmmmm”

Mike

Reply | Quote

mmmmmmmm”

Mike

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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 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.

Reply | Quote

(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.
A10RA21FA30NA40AA50AA63HA70EA80MA99VB02BB05XB06BB11CB12CB15XB19NB23CB25BB27
CB32DB36CB38CB39XB44DB45SB47ZB49AB52DB61DB72DB73AB90VB92ZB93CB96CB98DC03G
C06ZC08EC14AC18DC28AC30BC31GC35LC36AC38FC40PC42MC47DC53CC58JC63ZC68AC71F
C72CC75TC76BC88BC94BD01XD05FD06ED14TD20BD30AD33CD35ZD37AD47DD49WD69BD70Z
D78AD81CD82CD89AD90GD92AE04EE22NE65BF02EF04KF09BF13EF14RF26JF28MF35RF38AF39
RF41CF47HF48AF58ZF61GF64BF66FF73CG01AG04CG29BG37DG48AG80NH02BH05BH27AH51C
H54AH62BH71FH75AH76BH77CH78K
<<>>
KPPA N03EP02DR09BU18AU33WU43HW02AX01BX04EX09AX13JX19KX43AX99DZ79YZ91C.

For clarity, I have surrounded a sub-string that satisfies my criteria thus <<>> 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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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 <<>> 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: <<>> 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 <<>> 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 then continue looking for another. >>> and <<< I think there will be one, and only one per target string matching my pattern. >>> I am , 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 <<>>

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

Hope this helps.

Wassim

Reply | Quote

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 <<>> 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: <<>> 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 <<>> 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 then continue looking for another. >>> and <<< I think there will be one, and only one per target string matching my pattern. >>> I am , 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 <<>>

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

Hope this helps.

Wassim

Reply | Quote