How do you use the Left/right/substitute functions to find values inbetween commas

Question

ie: Messenger, 5, 8.5 - Facebook < 1.2B >

I need to pull out the 5 in between the two commas and the 8.5 in between the "," & "-" The function has to allow for more decimals ie: DoorDash, 6.2, 2.7 - DoorDash < 4.9M >

I was doing =IFERROR(TRIM(MID(A4,SEARCH(",",A4)+1,SEARCH("-",A4)-SEARCH(",",A4)-1)),"") but the output I keep getting is 5, 5.8 – and I cannot seem to separate the two.

Other: There are only two commas in what is given and all of the given ones follow the same structure (text, number , number - text < number >). Need to use the formulas left, right, mid, search, rept, find, switch, or text

I need two formulas: one to find the 5 and the other to find the 5.8

Answer 1

Use FILTERXML:

=INDEX(FILTERXML("<z>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,"<",""),">",""),"-","</y>"),",","</y><y>",2),",","<y>")&"</z>","//y"),COLUMN(A1))

Put that in the first cell changing only the $A1 reference to the cell reference leaving the other A1 as it is.

Depending on one's version of Excel this may need to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

Then copy over and down the data set.