Excel Formula assistance requested

[KimberlyWelty]

Wondering if someone can assist with this formula. Ultimately I am trying to pull out figures from column AO in column BC. The formula I have works most of the time, but not in all scenarios. One does not make sense, but the other 2 could be related to the length of the number.
Here is the formula I am using: =IFERROR(SUBSTITUTE(MID(AO2054,AGGREGATE(15,6,SEARCH(ROW($17:$2350),AO2054),1)-1,SEARCH(" ",AO2054,AGGREGATE(15,6,SEARCH(ROW($17:$2350),AO2054),1))-AGGREGATE(15,6,SEARCH(ROW($17:$2350),AO2054),1)),",","")+0,"")

The items in red are not working, hoping someone can assist me in correcting the formula so it works regardless of the number or value.
Let me know if you have any questions.

Thank you
Kimberly

 

[CatyH]

I think i would parse the text as mid using the end of "Commission - Healthcare Commission Plan," as initial and the "ANnual" with subtracting off 5 characters as the final. then take the value of what's left in the middle...
e.g. for Row 4...

=VALUE(MID(AO4,(FIND(",",AO4)+2),FIND("Annual",AO4)-5-(FIND(",",AO4)+2)))

NOte: This only works if the Comm Currency is always 3 characters. If that's a variable you can build that in instead of the "-5" you can make that be -(2+lengthofCommCurr)

 

[KimberlyWelty]

I think i would parse the text as mid using the end of "Commission - Healthcare Commission Plan," as initial and the "ANnual" with subtracting off 5 characters as the final. then take the value of what's left in the middle...
e.g. for Row 4...

=VALUE(MID(AO4,(FIND(",",AO4)+2),FIND("Annual",AO4)-5-(FIND(",",AO4)+2)))

NOte: This only works if the Comm Currency is always 3 characters. If that's a variable you can build that in instead of the "-5" you can make that be -(2+lengthofCommCurr)

Thank you!