How to find total number of values in a number ran

问题:

For example:

DATA: 23 TO 26, 40, 22, 7, 27 TO 28, 49, 50 TO 51, 48, 136, 169, 190 TO 192, 17 TO 22

I have these datas as shown above in each individual cell, how do I come up with a formula that sums up all the total number of values?

I need to do some updating of datas.

THANKS GUYS!

回答1:

This will work for " - " or " TO ":

=ROWS(INDIRECT(SUBSTITUTE(SUBSTITUTE(A1," - ",":")," TO ", ":")))

回答2:

If your delimiter is '-' or 'TO' then split(Data -> Text to Columns) it into two columns and then use subtract between them.

Formula would be =(B1-A1) + 1

回答3:

Note that you can directly use this formula:

=MID(A1,FIND("-",A1)+1,LEN(A1))*1 - LEFT(A1,FIND("-",A1)-1)*1 +1

And drag and drop it till the end of your values.