Hi!

I have a question and was hoping someone would be kind enough to help me.

I am trying to select some text within a field separated by tags like: [tag1]

I have tried this formula:
=MID(LEFT(D1,FIND("#", SUBSTITUTE(D1,"[","#",4))-1), FIND("[External",D1)+1,
255)

And it works great, except that some of the text is separated with a
carriage return after [tag1].

i.e.

Instead of [tag1]Blah blah blah

I have:
[tag1]
Blah blah blah

Ultimately, the best way to get this is to pull the text between [tag1] and
[tag2]. How would I go about doing this? If it's not possible, how can I
adapt my equation to accomodate the carriage returns?

Thank you so much for any help!

On Tue, 6 Feb 2007 07:59:01 -0800, Kanadani
wrote:

Hi!

I have a question and was hoping someone would be kind enough to help me.

I am trying to select some text within a field separated by tags like: [tag1]

I have tried this formula:
=MID(LEFT(D1,FIND("#", SUBSTITUTE(D1,"[","#",4))-1), FIND("[External",D1)+1,
255)

And it works great, except that some of the text is separated with a
carriage return after [tag1].

i.e.

Instead of [tag1]Blah blah blah

I have:
[tag1]
Blah blah blah

Ultimately, the best way to get this is to pull the text between [tag1] and
[tag2]. How would I go about doing this? If it's not possible, how can I
adapt my equation to accomodate the carriage returns?

Thank you so much for any help!

The presence of a carriage return shouldn't make a difference. You can just
use a SUBSTITUTE function to replace it with a <space.

You could use a formula of the type:

=TRIM(SUBSTITUTE(MID(A1,FIND("[tag1]",A1)+
LEN("[tag1]"),FIND("[tag2]",A1)-FIND("[tag1]",A1)-
LEN("[tag1]")),CHAR(10)," "))

This assumes tag1 and tag2 are different. If they are the same, it is still
doable but longer.

OR, you could use regular expressions as available in Longre's free
morefunc.xll add-in at http://xcell05.free.fr

But examples of the actual data and description of the limits would make this
easier to devise.
--ron

It worked perfectly! Thank you! You've made me a very, very happy woman. :)

"Ron Rosenfeld" wrote:

On Tue, 6 Feb 2007 07:59:01 -0800, Kanadani
wrote:

Hi!

I have a question and was hoping someone would be kind enough to help me.

I am trying to select some text within a field separated by tags like: [tag1]

I have tried this formula:
=MID(LEFT(D1,FIND("#", SUBSTITUTE(D1,"[","#",4))-1), FIND("[External",D1)+1,
255)

And it works great, except that some of the text is separated with a
carriage return after [tag1].

i.e.

Instead of [tag1]Blah blah blah

I have:
[tag1]
Blah blah blah

Ultimately, the best way to get this is to pull the text between [tag1] and
[tag2]. How would I go about doing this? If it's not possible, how can I
adapt my equation to accomodate the carriage returns?

Thank you so much for any help!

The presence of a carriage return shouldn't make a difference. You can just
use a SUBSTITUTE function to replace it with a <space.

You could use a formula of the type:

=TRIM(SUBSTITUTE(MID(A1,FIND("[tag1]",A1)+
LEN("[tag1]"),FIND("[tag2]",A1)-FIND("[tag1]",A1)-
LEN("[tag1]")),CHAR(10)," "))

This assumes tag1 and tag2 are different. If they are the same, it is still
doable but longer.

OR, you could use regular expressions as available in Longre's free
morefunc.xll add-in at http://xcell05.free.fr

But examples of the actual data and description of the limits would make this
easier to devise.
--ron

On Wed, 7 Feb 2007 11:26:00 -0800, Kanadani
wrote:

It worked perfectly! Thank you! You've made me a very, very happy woman. :)


Glad to help. Thanks for the feedback.
--ron