On 28 Dec 2005 11:39:37 -0800, "paulinoluciano"
wrote:

In fact, the other topic is just a few more complex until to explain.
Let me try explain better. I have a sequence of characters like:

AASSASDKASASDASFAFSASASADKASASAFPKQREWEAQEOKSPADA OEKOQPPDAOPSKAEPQ

This sequence must be put in cell A2.
Thus, I have to perform some specific operations in this text:

Example 1:
Rules:
a) Fragment the sequence before K but not always (you could have lost
cut).
b) Sequence is not cut if K is found before FP

Results:

AASSASDKASASDASFAFSASASADKASASAFPKQREWEAQEOKSPADA OEKOQPPDAOPSKAEPQ

0 lost cut = Cutting the sequence all the time in which K is present
(The subsequences of this process should be put in B column:
AASSASDK
ASASDASFAFSASASADK
ASASAFPKQREWEAQEOK
SPADAOEK
OQPPDAOPSK
AEPQ

1 lost cut = Cutting the sequence after the first K present in the
sequence (The subsequences of this process should be put in C column::
AASSASDKASASDASFAFSASASADK
ASASAFPKQREWEAQEOKSPADAOEK
OQPPDAOPSKAEPQ
AASSASDKASASDASFAFSASASADKASASAFPKQREWEAQEOK
SPADAOEKOQPPDAOPSKAEPQ

2 lost cut = = Cutting the sequence after the second K (just for the
third and following) present in the sequence (The subsequences of this
process should be put in D column:
AASSASDKASASDASFAFSASASADKASASAFPKQREWEAQEOK
SPADAOEKOQPPDAOPSKAEPQ

Repair that in some cases I need lost cuts in which you cut after 1, 2,
3, 4,... specific characters.
I have to specify such rules in some place of the sheet containing the
precursor text.
The rules a

Cut after "XXX" (In this example I have put K but the some cell in the
sheet must contain what is the character in which the sequence will be
fragmented). In some cases it could be more than only one character
(e.g. K and R; nor necessarily together)
Cut before "XXX" (The cut may be after like previous example or before
the character)

Never before "XXX" (In some cases I have prohibitive situations; e.g.
It must not cut a sequence in K if K is preceeded by P or by RP)
Never after "XXX" (Same for after)

Number of times that the character could be missed prior cut "XXX" (In
some place of the sheet I must explicit how many characters could be
"lost" prior cut (see example).

You may want to look into "regular expressions" to do what you are trying to
describe. If you download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/ you will see that you can use them as worksheet
functions and also call them from a VBA module.

What you write is a bit confusing. For example, one rule you give is:
"Sequence is not cut if K is found before FP" but in your example you seem to
be acting as if the rule applies if K is found AFTER FP.

I am assuming the output starts in B1; if it starts in a different row, then
adjust the ROW() function to result in a 1 as the output:

seq is the character sequence (Insert/Name/Define and set seq = "your string")

For the "0 lost cuts"

B1: =REGEX.MID(seq,"(\w+?([^FP]K|$)){"&COLUMN()-1&"}",ROW())

ROW() resolves to a '1' which means take the 'first' sequence that matches the
pattern. As you copy/drag the formula down, ROW() will resolve to '2', '3',
etc. which means match the 2nd, 3rd, etc sequence that matches the pattern.

The basic pattern is defined by "(\w+?([^FP]K|$)){" which means look for a
sequence of letters that ends with a K that is not preceded by an FP, or that
is at the end of the string.

The {"&COLUMN()-1&"}" resolves, in Column B, to {1} which means look for one
occurrence of the preceding pattern.

If you copy/drag the formula down until you get blanks for the results, you
will see what you posted in your previous message.

If you copy/drag across to column D, you will see the results of "1 lost cut"
or "2 lost cuts".

I think once you understand the formula construction and the regular
expressions, it will be simple to use this for your other rules.

Without the COLUMN and ROW functions, the formulas would look like:

B1: =REGEX.MID(seq,"(\w+?([^FP]K|$)){1}",1)
B2: =REGEX.MID(seq,"(\w+?([^FP]K|$)){1}",2)

C1: =REGEX.MID(seq,"(\w+?([^FP]K|$)){2}",1)
C2: =REGEX.MID(seq,"(\w+?([^FP]K|$)){2}",2)

To use this in VBA, you would use the RUN method which is outlined in HELP for
morefunc.xll
--ron