So close to completing Search and Substitute function

[white84]

Hi! I need all text after an identifier "CPAL", which can vary in length and may have a : or ; after "CPAL" plus a space.

Example Text: 1
0 Retrofit A/C, 2/4/1/0.5 FOC IP, CPAL: HSE + W + B Parts + Structures

What I am trying is giving me the text before that identifier

=Mid(A2,1,SEARCH("CPAL",A2,1))
Returns
10 Retrofit A/C, 2/4/1/0.5 FOC IP, C

Ideally the formula would return
HSE + W + B Parts + Structures

Thanks in advance!!

 

[mikerickson]

Try moving the SEARCH to the second argument of MID (and setting the last argument to 255 to get all of the rest of the string). You'll probably have to add a constant to the Search.

 

[white84]

Edit:
The example text above has a formatting issue when I posted here. Lets try again.

10 Retrofit A/C, 2/4/1/0.5 FOC IP, CPAL: HSE + W + B Parts + Structures

 

[white84]

Try moving the SEARCH to the second argument of MID (and setting the last argument to 255 to get all of the rest of the string). You'll probably have to add a constant to the Search.

Perfect!! Thank you!!

 

[white84]

Try moving the SEARCH to the second argument of MID (and setting the last argument to 255 to get all of the rest of the string). You'll probably have to add a constant to the Search.

I thought I would be able to add an OR prefix to this corrected formula for one additional text criteria that may occur. (i.e. 8 new A/C, 1/4/1/1 50% disc IP, SUP: HSE only) where I would want to return SUP: HSE only

This gives me a value error.
=OR(MID($A$2,SEARCH("CPAL",$A$2,1),255)*MID($A$2,SEARCH("SUP",$A$2,1),255))

Again, thanks in advance!

 

[white84]

Gah! I accidentally copied the Excel suggested correction, which still doesn't work. This is what I meant to post.

=OR(MID(Sheet37!$A$2,SEARCH("CPAL",Sheet37!$A$2,1),255),MID(Sheet37!$A$2,SEARCH("SUP",Sheet37!$A$2,1),255))

 

[white84]

Posting my corrected formula for anyone else with a similar issue.

Example text pulling from
8 new A/C, 1/4/1/1 50% disc IP, SUP: HSE only
10 Retrofit A/C, 2/4/1/0.5 FOC IP, CPAL: HSE + W + B Parts + Structures

=IFERROR(MID(Sheet37!$A$2,SEARCH("CPAL",Sheet37!$A$2,1),255),MID(Sheet37!$A$2,SEARCH("SUP",Sheet37!$A$2,1),255))