Extract specific strings that is next to a specific text - inconsistent data format

Hello everyone,

This is a pretty tough one to figure out, so major major thank you if are able to help me out.

Here is a rather inconsistent data format. My goal here is to extract the target percentage out of these data. So in the bottom example, I want the second column to spit out 21.5%, 24%, and 20.53% respectively. We only want to extract the percentage that is adjacent to "target"

Column A Column B
James Doe homework 21.5% target 21.5%
All member discount 18%|24% target 24%
Other members discount 15%|20.53% target 20.53%

Thanks you!

This is assuming 2 things:
1. Percentage always follows "target "
and
2. That the percentage is always at the end of the cell

=RIGHT(A1,LEN(A1)-SEARCH("target ",A1)-6)

The above extracts as TEXT. If you need the actual number as a percentage, try this:

=VALUE(MID(A1,SEARCH("target ",A1)+6,LEN(A1)-SEARCH("target ",A1)-6))/100

(and set the field to format of "Percentage")

Thanks for the response Gregb11

I tried both of your suggestions, but the first returned a blank, and the second one prompt a Value error message. Any thoughts on what might be the issue? I switched A1 to A2 in the formula by the way.

My original post had weird formatting, so attaching what I want the outcome to be.

Thanks!

Attachment 793590

Please read the yellow banner at the top of the page for attaching a sample workbook. The link doesn't work.

And attached is my workbook with my formulas in it.

You're welcome. Thanks for the feedback and rep!