How to extract number before and after "-"

[ziyan89]

Dear all

I have
SUSAR No. 218-239 >>> I want 239 - 218 = 21
SUSAR No. 1218-1239 >>>I want 1239 - 1218 = 21
SUSAR No. 918-1039 >>>I want 1039 - 918 = 121

There is a variety of the # of digits. And I need to take bigger value - smaller value

How do I write the formula

Thank you

[Gregb11]

Well, this uses a LOT of assumptions, like:
Your numbers always have a dash (-) between them and no spaces between them (and no other dash in the cell). There is always only 1 period and a space before the first number. If this is true, if "SUSAR No. 218-239" was in cell a1, in cell b1 you can use this formula:
=MAX(NUMBERVALUE(RIGHT(A1,LEN(A1)-(SEARCH("-",A1,1)))),NUMBERVALUE(MID(A1,SEARCH(".",A1,1)+1,SEARCH("-",A1,1)-SEARCH(".",A1,1)-1)))-MIN(NUMBERVALUE(RIGHT(A1,LEN(A1)-(SEARCH("-",A1,1)))),NUMBERVALUE(MID(A1,SEARCH(".",A1,1)+1,SEARCH("-",A1,1)-SEARCH(".",A1,1)-1)))

I have no doubt this formula can be simplified, but it works (as long as assumptions are correct.

[ziyan89]

Yes there is be no spaces between the digit and the "-"
also the number will be before and after the 1st "-". wonder if there is simplier formulae?
Thanks Gregb11

[GeoffW283]

Same assumptions as Gregb11.

Please try in B1:

Please Login or Register  to view this content.

[Phuocam]

Try:

=RIGHT(A1,LEN(A1)-FIND("-",A1))-(MID(SUBSTITUTE(SUBSTITUTE(A1,"No.","-"),"-",REPT(" ",100)),100,100))

[Bo_Ry]

Please try
=SUM(MID(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"-"," ")," ",REPT(" ",40)),80),{1,41},40)*{-1,1})

[Pepe Le Mokko]

And how about the good old hidden EVALUATE function ?
Open the Name manager - Enter a name (test, say) -Enter the following formula in "refers to" :=ABS(EVALUATE(TRIM(SUBSTITUTE(Sheet1!$A1,"SUSAR No.","")))) - Close the wizard
Now enter =test in B1, say, and pronto, that's it.
Your wbk MUST be saved as xlsm, but you do NOT need to bother with VBA.

NB I know it's not as impressing as the above formulas, I like the KISS method

[Gregb11]

Besides Pepe's solution, the other above solutions assume the first number is less than the second but I was under the impression that they could be in either order. If that assumption is true, you'll need to wrap those formulas with min and max's like I have in mine.

[Bo_Ry]

Just wrap with ABS to get positive number
=ABS(SUM(MID(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"-"," ")," ",REPT(" ",40)),80),{1,41},40)*{-1,1}))

[Gregb11]

Yes, I thought of that right after I posted - much better