First and foremost thank you for taking a look at this post. Any help will
be greatly appreciated. I am a novice Excel user trying to learn more about
it and am experimenting with statements and formulas and I have run into a
snag for which I can't get around.

In the following working formula =IF(ISERROR(MID('Raw Data'!A1,FIND(" ",'Raw
Data'!A1)+4,100))," ",MID('Raw Data'!A1,FIND(" ",'Raw Data'!A1)+4,100)) I get
a result of "THIS IS A TEST OF THE EMERGNECY BROADCAST SYSTEM 8PM.P3.IF THIS
WERE AN ACTUAL EMERGENCY ........" for example.

What I am trying to do is add to this formula so I get ""THIS IS A TEST OF
THE EMERGNECY BROADCAST SYSTEM" only . In the text that follows "SYSTEM" the
only value that does not change and will always stay the same is "P3" which I
figure I can use to base my find function only and reemove all characters to
the right of P3 and also 4 characters to the left of P3 and lastly removing
the P3 itself. I have tried combination of TRIM and LEN and hve had no luck.
This is a little over my head!

Thanks once again for looking at this post and providing any help you may be
able to offer.

i got the result this way --- replace the last '100' in your formula with this:

FIND("P3",MID(A1,FIND(" ",A1)+4,100),1)-6


--
hope to help,
cm


"Brenda" wrote:


First and foremost thank you for taking a look at this post. Any help will
be greatly appreciated. I am a novice Excel user trying to learn more about
it and am experimenting with statements and formulas and I have run into a
snag for which I can't get around.

In the following working formula =IF(ISERROR(MID('Raw Data'!A1,FIND(" ",'Raw
Data'!A1)+4,100))," ",MID('Raw Data'!A1,FIND(" ",'Raw Data'!A1)+4,100)) I get
a result of "THIS IS A TEST OF THE EMERGNECY BROADCAST SYSTEM 8PM.P3.IF THIS
WERE AN ACTUAL EMERGENCY ........" for example.

What I am trying to do is add to this formula so I get ""THIS IS A TEST OF
THE EMERGNECY BROADCAST SYSTEM" only . In the text that follows "SYSTEM" the
only value that does not change and will always stay the same is "P3" which I
figure I can use to base my find function only and reemove all characters to
the right of P3 and also 4 characters to the left of P3 and lastly removing
the P3 itself. I have tried combination of TRIM and LEN and hve had no luck.
This is a little over my head!

Thanks once again for looking at this post and providing any help you may be
able to offer.

Thanks cm! Looking at this now it makes sense and is clearer. I kinda
thought I was on the right track! This did the trick. Thank you again!

"cm" wrote:

i got the result this way --- replace the last '100' in your formula with this:

FIND("P3",MID(A1,FIND(" ",A1)+4,100),1)-6


--
hope to help,
cm


"Brenda" wrote:


First and foremost thank you for taking a look at this post. Any help will
be greatly appreciated. I am a novice Excel user trying to learn more about
it and am experimenting with statements and formulas and I have run into a
snag for which I can't get around.

In the following working formula =IF(ISERROR(MID('Raw Data'!A1,FIND(" ",'Raw
Data'!A1)+4,100))," ",MID('Raw Data'!A1,FIND(" ",'Raw Data'!A1)+4,100)) I get
a result of "THIS IS A TEST OF THE EMERGNECY BROADCAST SYSTEM 8PM.P3.IF THIS
WERE AN ACTUAL EMERGENCY ........" for example.

What I am trying to do is add to this formula so I get ""THIS IS A TEST OF
THE EMERGNECY BROADCAST SYSTEM" only . In the text that follows "SYSTEM" the
only value that does not change and will always stay the same is "P3" which I
figure I can use to base my find function only and reemove all characters to
the right of P3 and also 4 characters to the left of P3 and lastly removing
the P3 itself. I have tried combination of TRIM and LEN and hve had no luck.
This is a little over my head!

Thanks once again for looking at this post and providing any help you may be
able to offer.

=LEFT(A1,LEN(A1)-FIND("P3",A1)+4)

--
Regards,
Dave


"Brenda" wrote:


First and foremost thank you for taking a look at this post. Any help will
be greatly appreciated. I am a novice Excel user trying to learn more about
it and am experimenting with statements and formulas and I have run into a
snag for which I can't get around.

In the following working formula =IF(ISERROR(MID('Raw Data'!A1,FIND(" ",'Raw
Data'!A1)+4,100))," ",MID('Raw Data'!A1,FIND(" ",'Raw Data'!A1)+4,100)) I get
a result of "THIS IS A TEST OF THE EMERGNECY BROADCAST SYSTEM 8PM.P3.IF THIS
WERE AN ACTUAL EMERGENCY ........" for example.

What I am trying to do is add to this formula so I get ""THIS IS A TEST OF
THE EMERGNECY BROADCAST SYSTEM" only . In the text that follows "SYSTEM" the
only value that does not change and will always stay the same is "P3" which I
figure I can use to base my find function only and reemove all characters to
the right of P3 and also 4 characters to the left of P3 and lastly removing
the P3 itself. I have tried combination of TRIM and LEN and hve had no luck.
This is a little over my head!

Thanks once again for looking at this post and providing any help you may be
able to offer.

Thanks Dave! I 'll play around with this to make sense out of it and try to
make it work for me. This is a great learning experience!

"David Billigmeier" wrote:

=LEFT(A1,LEN(A1)-FIND("P3",A1)+4)

--
Regards,
Dave


"Brenda" wrote:


First and foremost thank you for taking a look at this post. Any help will
be greatly appreciated. I am a novice Excel user trying to learn more about
it and am experimenting with statements and formulas and I have run into a
snag for which I can't get around.

In the following working formula =IF(ISERROR(MID('Raw Data'!A1,FIND(" ",'Raw
Data'!A1)+4,100))," ",MID('Raw Data'!A1,FIND(" ",'Raw Data'!A1)+4,100)) I get
a result of "THIS IS A TEST OF THE EMERGNECY BROADCAST SYSTEM 8PM.P3.IF THIS
WERE AN ACTUAL EMERGENCY ........" for example.

What I am trying to do is add to this formula so I get ""THIS IS A TEST OF
THE EMERGNECY BROADCAST SYSTEM" only . In the text that follows "SYSTEM" the
only value that does not change and will always stay the same is "P3" which I
figure I can use to base my find function only and reemove all characters to
the right of P3 and also 4 characters to the left of P3 and lastly removing
the P3 itself. I have tried combination of TRIM and LEN and hve had no luck.
This is a little over my head!

Thanks once again for looking at this post and providing any help you may be
able to offer.