Extract and add numbers from a text range

I have columns in a range like this

Column1,Column2,Column3
5O,5O2N,2N

What I need is to sum all the values before O and N separately. Like the following,

Column4,Column5
10,4

FYI. This scenario can also include decimal values. Also, as I need to implement this in Google Sheets, I need this in a direct excel formula rather than a VBA code.

Is this possible? Kindly confirm.

Please post a file with representative set of data.

Click "Go Advanced" the "Paper Clip" icon to upload a file.

Yes. Always O comes first in any cell.

Thanks for your reply.

Here is the sample file. Samples.xlsx

Wow!! This worked like a charm.

Thanks a lot for your response.