Sign up, and you'll be able to customize your font size and more! Sign up
Jul 30, 2014
1:01:47pm
It's likely to get a bit messy if you're trying to do too much in a single cell
But here's the solution. I'm assuming that the format will always be a bracket then a number (could be multiple digits) then a letter s then a comma then a number (multiple digits works again) then the letter n. Anything after that is ignored.

A1= [4s,5n]
A2= [8s,6n]
A3= [1s,9n]

A formula to pull out just the first numeric value is "=MID(A1, 2, FIND("s", A1)-2)*1" and similarly for the second "=MID(A1, FIND(",", A1)+1, FIND("n", A1)-(FIND(",", A1)+1))*1" with the multiplying by 1 just there to force it to a numeric value rather than text. You can test that in a cell next to those values to see that it works.

If you really want to get the sum of these without using intermediate cells to hold the values, you could just add the formula three times (this would get really messy) or you can use array formulas as below. In case you aren't familiar with array formulas, you enter the formula without the curly brackets and then hit Ctrl+Shift+Enter rather than just regular enter.

"{=SUM(MID(A1:A3, 2, FIND("s", A1:A3)-2)*1)}"
"{=SUM(MID(A1:A3, FIND(",", A1:A3)+1, FIND("n", A1:A3)-(FIND(",", A1:A3)+1))*1)}"
JadoX
Bio page
JadoX
Joined
Jul 12, 2011
Last login
Apr 25, 2024
Total posts
1,379 (12 FO)
Messages
Author
Time

Posting on CougarBoard

In order to post, you will need to either sign up or log in.