Selecting Partial Text in a field

[Guest]

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!

 

[Ron Rosenfeld]

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

 

[Guest]

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

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

 

[Ron Rosenfeld]

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

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