Count the numbers if a cell contains a range

Question

I have this issue on Google spreadsheets but I know spreadsheets and Excel are similar.

I generated this spreadsheet with pseudo-data but the problem remains the same.

I have a column where the rows have a string of words such as "Apple/5" where the category I want is before the "/". Each number that proceeds the "/" represents an instance of that category. So "Orange/2-3" represents 2 oranges and "Orange/6,7,8" represents 3 oranges so it would calculate to 5 oranges.

Is this even possible with Excel/google spreadsheet?

The numbers can be signified by both the comma and the dash but all numbers are after the "/" — jyp95, Feb 20 '18 at 23:55

score 0 · Answer 1 · answered Feb 21 '18 at 00:12

0

Assuming the text you want to evaluate is in A2: =LEN(RIGHT(A2,LEN(A2)-FIND("/",A2)-LEN(SUBSTITUTE(SUBSTITUTE(RIGHT(A2,LEN(A2)-FIND("/",A2)),"-",""),",",""))+1))

answered Feb 21 '18 at 00:12

cybernetic.nomad

6,100
3
18
31

Hmm... I need it to sum all the counts together to get my desired output. Also this formula doesn't seem to account for the "-" range? I put in 2-6 for oranges and the count is still the same. – jyp95 Feb 21 '18 at 00:20

score 0 · Accepted Answer · answered Feb 21 '18 at 01:10

No idea in Google Spreadsheet, but in Excel with and UDF you could extract in 1 column how many fruits you have and then maybe make your sum. For this answer, I used UDF ONLYDIGITS designed by @paxdiablo, so all credits goes to him.

Public Function HOW_MANY(ByRef ThisCell As Range) As Long
If ThisCell.Count <> 1 Then 'If we select more than 1 cell, it won't work
    HOW_MANY = ""
Else
    HOW_MANY = Len(onlyDigits(ThisCell.Value))
End If

End Function
Private Function onlyDigits(s As String) As String
    ' Variables needed (remember to use "option explicit").   '
    Dim retval As String    ' This is the return string.      '
    Dim i As Integer        ' Counter for character position. '

    ' Initialise return string to empty                       '
    retval = ""

    ' For every character in input string, copy digits to     '
    '   return string.                                        '
    For i = 1 To Len(s)
        If Mid(s, i, 1) >= "0" And Mid(s, i, 1) <= "9" Then
            retval = retval + Mid(s, i, 1)
        End If
    Next

    ' Then return the return string.                          '
    onlyDigits = retval
End Function

Count the numbers if a cell contains a range

2 Answers2