Как получить сумму числовых значений в буквенно-цифровой строке в формуле Microsoft Excel?

Question

У меня есть данные в ячейке, как:

Хороший ответ -6, РНР -2, Нет голоса -1

Мне нужно получить сумму только чисел в ячейке. Как я могу сделать это в одной формуле?

Answer 1

Я использую надстройку Excel RegEx Find/Replace, она добавляет функциональность регулярных выражений в Excel.
(Я никак не связан с этой надстройкой, просто пользователь-энтузиаст)

Здесь вы можете использовать следующую формулу, чтобы найти и сложить все числа:
=SUM(RegExFind(A1,"\d+"))

Или также учитывать отрицательные значения:
=SUM(RegExFind(A1,"[-\d]+"))

Answer 2

Я хотел бы предложить небольшую функцию VBA. Обратите внимание, что разделитель может быть настроен, как показано ниже.

Function SumNumbers(rngS As Range, Optional strDelim As String = "-") As Double

    Dim xNums As Variant, lngNum As Long
    xNums = Split(rngS, strDelim)

    For lngNum = LBound(xNums) To UBound(xNums) Step 1
        SumNumbers = SumNumbers + Val(xNums(lngNum))
    Next lngNum

End Function

Заметки:

• Используйте эту функцию обычным способом: = SumNumbers(Cell Address).
• Если в качестве разделителя используется пробел, используйте strDelim As String = "" в списке аргументов.
• Если разделителем является COMMA, тогда используйте strDelim As String = "," в списке аргументов.

Я надеюсь, это поможет вам.

Answer 3

Основываясь на предоставленной информации и предполагая, что будет когда-либо извлечено только 3 значения и каждая длина 1 цифра, ниже будет работать для строки в A2:

=LEFT(SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10),1)+RIGHT(SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10),1)+RIGHT(A2,1)

Конечно, я сделал несколько предположений,

Если это не сработает, объясните мне более подробно, как структурированы ваши данные и т.д., и я постараюсь внести изменения.

Answer 4

Если ваши данные действительно такие, как вы их показываете, с числом, которому предшествует <space> , а за которым следует <comma> или конец строки, то вы можете использовать следующее.

Определите формулу и назовите ее seq_99 где

seq_99 refers to:  =IF(ROW(INDEX($1:$65535,1,1):INDEX($1:$65535,255,1))=1,1,(ROW(INDEX($1:$65535,1,1):INDEX($1:$65535,255,1))-1)*99)

Затем вы можете использовать эту формулу, введенную в массив :

=SUM(IF(ISNUMBER(--TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ",REPT(" ",99)),seq_99,99))),--TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ",REPT(" ",99)),seq_99,99))))

Чтобы ввести формулу в массив , удерживайте Ctrl + Shift при нажатии Enter. Если вы сделаете это правильно, Excel поместит фигурные скобки {...} вокруг формулы, которую вы видите на панели формул.

Я предположил - обозначает отрицательное число. Если это не так, тривиально изменить формулу, чтобы исключить ее.