| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro to apply parsing rules for strings and list the substrings
Luciano,
A lot of these simpler rules would be easy to implement For example, rule 2 Function Rule2(strVal As String) As Variant Rule2 = Application.Transpose(Split(Replace(Replace(strVal , "K", "K "), "R", "R "), " ")) End Function If your string is in cell A1, your could select A2:A10 and enter using Ctrl-Shift-Enter =Rule2(A1) Other rules would require stepping through and checking each instance, but before I do an example of that, if you could answer one question: Is there a letter that will NEVER appear in your strings? HTH, Bernie MS Excel MVP "Luciano Paulino da Silva" wrote in message ... Dear All, I'm looking for detect and list the substrings (bellow cell "A3") for a given string (on cell "A1") that must be generated for one or more general rule(s). I have almost 100 different rules that can be applied alone or together one to the other. Do you have any idea about how could I do that? The rules are listed bellow and I put an example. Rules: According to these rules, letters in a string undergoing parse are designated in the left or right direction from the parsed letter. There are some exceptions related to the presence of one or more letters sorrounding some specific point of parse. Parsing rules: Rule Parse where? Exceptions 1 Right side of K or R if P is Right to K or R 2 Right side of K or R 3 Right side of K or R if P is Right to K or R; after K in CKY, DKD, CKH, CKD, KKR; after R in RRH, RRR, CRK, DRD, RRF, KRR 4 Right side of K 5 Left side of K 6 Right side of M 7 Right side of R if P is Right to R 8 Left side of D 9 Left side of D, Right side of K 10 Left side of D or E 11 Right side of E if P is Right to E, or if E is Right to E 12 Right side of D or E if P is Right to D or E, or if E is Right to D or E 13 Right side of D, E and K if P is Right to D or E, or if E is Right to D or E 14 Right side of F, L, M, W, Y if P is Right to F, L, M, W, Y, if P is Left to Y 15 Right side of F, Y, W if P is Right to F, Y, W, if P is Left to Y 16 Right side of K, R, F, Y, W if P is Right to K, R, F, Y, W, if P is Left to Y 17 Right side of F, L 18 Right side of F, L, W, Y, A, E, Q 19 Right side of A, F, Y, W, L, I, V 20 Left side of A, F, I, L, M, V if D or E is Left to A, F, I, L, M, V Examples: String: AGFSAFSAHASGASHSGHHSRASAKSASFDDAKPASASAFDAGSRPASSD ADASAPSASDASDASSRADSKADSKK Using Rule 1: AGFSAFSAHASGASHSGHHSR ASAK SASFDDAKPASASAFDAGSRPASSDADASAPSASDASDASSR ADSK ADSK K Using Rule 8: AGFSAFSAHASGASHSGHHSRASAKSASF D DAKPASASAF DAGSRPASS DA DASAPSAS DAS DASSRA DSKA DSKK Using Rules 1 and 17 AGF SAF SAHASGASHSGHHSR ASAK SASF DDAKPASASAF DAGSRPASSDADASAPSASDASDASSR ADSK ADSK K |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro to apply parsing rules for strings and list the substrings
Bernie,
OK, We will have just 20 types of letters: A, C, D, E, F, G, H, I, K, L, M, N, P, Q, R, S, T, V, X For some situations, I will have some new future rules and it is possible that in several cases I will have to apply more than one rule simultaneously. Thanks in advance, Luciano On 16 abr, 15:10, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Luciano, A lot of these simpler rules would be easy to implement For example, rule 2 Function Rule2(strVal As String) As Variant * *Rule2 = Application.Transpose(Split(Replace(Replace(strVal , "K", "K "), "R", "R "), " ")) End Function If your string is in cell A1, your could select A2:A10 and enter using Ctrl-Shift-Enter =Rule2(A1) Other rules would require stepping through and checking each instance, but before I do an example of that, if you could answer one question: Is there a letter that will NEVER appear in your strings? HTH, Bernie MS Excel MVP "Luciano Paulino da Silva" wrote in ... Dear All, I'm looking for detect and list the substrings (bellow cell "A3") for a given string (on cell "A1") that must be generated for one or more general rule(s). I have almost 100 different rules that can be applied alone or together one to the other. Do you have any idea about how could I do that? The rules are listed bellow and I put an example. Rules: According to these rules, letters in a string undergoing parse are designated in the left or right direction from the parsed letter. There are some exceptions related to the presence of one or more letters sorrounding some specific point of parse. Parsing rules: Rule Parse where? * * * * * * * * * * * * * * * *Exceptions 1 Right side of K or R * * * * * * * * * * * *if P is Right to K or R 2 * * * Right side of K or R 3 * * * Right side of K or R * * * * * * * * * * * *if P is Right to K or R; after K in CKY, DKD, CKH, CKD, KKR; after R in RRH, RRR, CRK, DRD, RRF, KRR 4 Right side of K 5 Left side of K 6 Right side of M 7 Right side of R * * * * * * * * * * * *if P is Right to R 8 Left side of D 9 Left side of D, Right side of K 10 Left side of D or E 11 Right side of E * * * * * * * * * * * *if P is Right to E, or if E is Right to E 12 Right side of D or E * * * * * * * * * * * *if P is Right to D or E, or if E is Right to D or E 13 Right side of D, E and K * * * * * * * *if P is Right to D or E, or if E is Right to D or E 14 *Right side of F, L, M, W, Y * * * * * * * *if P is Right to F, L, M, W, Y, if P is Left to Y 15 *Right side of F, Y, W * * * * * * * * * * * *if P is Right to F, Y, W, if P is Left to Y 16 Right side of K, R, F, Y, W * * * * * * * *if P is Right to K, R, F, Y, W, if P is Left to Y 17 Right side of F, L 18 Right side of F, L, W, Y, A, E, Q 19 Right side of A, F, Y, W, L, I, V 20 Left side of A, F, I, L, M, V * * * * * * * *if D or E is Left to A, F, I, L, M, V Examples: String: AGFSAFSAHASGASHSGHHSRASAKSASFDDAKPASASAFDAGSRPASSD ADASAPSASDASDASSRADSKADSKK Using Rule 1: AGFSAFSAHASGASHSGHHSR ASAK SASFDDAKPASASAFDAGSRPASSDADASAPSASDASDASSR ADSK ADSK K Using Rule 8: AGFSAFSAHASGASHSGHHSRASAKSASF D DAKPASASAF DAGSRPASS DA DASAPSAS DAS DASSRA DSKA DSKK Using Rules 1 and 17 AGF SAF SAHASGASHSGHHSR ASAK SASF DDAKPASASAF DAGSRPASSDADASAPSASDASDASSR ADSK ADSK K |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro to apply parsing rules for strings and list the substrings
I`m very happy that you said that a lot of the rules are simple to
implemnt. I have successfuly tested that one you send me. Thank for your help with this. Luciano On 16 abr, 21:08, Luciano Paulino da Silva wrote: Bernie, OK, We will have just 20 types of letters: A, C, D, E, F, G, H, I, K, L, M, N, P, Q, R, S, T, V, X For some situations, I will have some new future rules and it is possible that in several cases I will have to apply more than one rule simultaneously. Thanks in advance, Luciano On 16 abr, 15:10, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Luciano, A lot of these simpler rules would be easy to implement For example, rule 2 Function Rule2(strVal As String) As Variant * *Rule2 = Application.Transpose(Split(Replace(Replace(strVal , "K", "K "), "R", "R "), " ")) End Function If your string is in cell A1, your could select A2:A10 and enter using Ctrl-Shift-Enter =Rule2(A1) Other rules would require stepping through and checking each instance, but before I do an example of that, if you could answer one question: Is there a letter that will NEVER appear in your strings? HTH, Bernie MS Excel MVP "Luciano Paulino da Silva" wrote in .... Dear All, I'm looking for detect and list the substrings (bellow cell "A3") for a given string (on cell "A1") that must be generated for one or more general rule(s). I have almost 100 different rules that can be applied alone or together one to the other. Do you have any idea about how could I do that? The rules are listed bellow and I put an example. Rules: According to these rules, letters in a string undergoing parse are designated in the left or right direction from the parsed letter. There are some exceptions related to the presence of one or more letters sorrounding some specific point of parse. Parsing rules: Rule Parse where? * * * * * * * * * * * * * * * *Exceptions 1 Right side of K or R * * * * * * * * * * * *if P is Right to K or R 2 * * * Right side of K or R 3 * * * Right side of K or R * * * * * * * * * * * *if P is Right to K or R; after K in CKY, DKD, CKH, CKD, KKR; after R in RRH, RRR, CRK, DRD, RRF, KRR 4 Right side of K 5 Left side of K 6 Right side of M 7 Right side of R * * * * * * * * * * * *if P is Right to R 8 Left side of D 9 Left side of D, Right side of K 10 Left side of D or E 11 Right side of E * * * * * * * * * * * *if P is Right to E, or if E is Right to E 12 Right side of D or E * * * * * * * * * * * *if P is Right to D or E, or if E is Right to D or E 13 Right side of D, E and K * * * * * * * *if P is Right to D or E, or if E is Right to D or E 14 *Right side of F, L, M, W, Y * * * * * * * *if P is Right to F, L, M, W, Y, if P is Left to Y 15 *Right side of F, Y, W * * * * * * * * * * * *if P is Right to F, Y, W, if P is Left to Y 16 Right side of K, R, F, Y, W * * * * * * * *if P is Right to K, R, F, Y, W, if P is Left to Y 17 Right side of F, L 18 Right side of F, L, W, Y, A, E, Q 19 Right side of A, F, Y, W, L, I, V 20 Left side of A, F, I, L, M, V * * * * * * * *if D or E is Left to A, F, I, L, M, V Examples: String: AGFSAFSAHASGASHSGHHSRASAKSASFDDAKPASASAFDAGSRPASSD ADASAPSASDASDASSRADSKADSKK Using Rule 1: AGFSAFSAHASGASHSGHHSR ASAK SASFDDAKPASASAFDAGSRPASSDADASAPSASDASDASSR ADSK ADSK K Using Rule 8: AGFSAFSAHASGASHSGHHSRASAKSASF D DAKPASASAF DAGSRPASS DA DASAPSAS DAS DASSRA DSKA DSKK Using Rules 1 and 17 AGF SAF SAHASGASHSGHHSR ASAK SASF DDAKPASASAF DAGSRPASSDADASAPSASDASDASSR ADSK ADSK K |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| lookup specific text, then apply rules | Excel Worksheet Functions | |||
| String parsing with variable lenght strings | Excel Worksheet Functions | |||
| How to find number of pairs of strings from list of strings? | Excel Worksheet Functions | |||
| Create template that ignors data list rules | New Users to Excel | |||
| Searching for Substrings Within Strings | Excel Discussion (Misc queries) | |||
Hybrid Mode
