Как суммировать значения внутри буквенно-цифровых текстовых строк в нескольких ячейках?
Например, в строке из 3 ячеек я хочу суммировать количество минут
A2 = Late 6 min,
B2 = Late 12 min, &
C2 = Late 150 min cells arranged in a row.
Суммирование вышесказанного дало бы мне 168.
Вы можете использовать эту формулу Array:
{=SUM(IFERROR(SUBSTITUTE(SUBSTITUTE(A2:C2,"Late",""),"Mints","")*1,0))}
Замечания:
Mints можно заменить на min .Если у вас есть флэш-заливка (2013 и более поздние версии), вы можете вставить вспомогательный столбец и использовать его.
Добавьте строку над вашими данными, вставьте строку и поместите в нее что-то вроде "s5t3", затем поместите "9876543210" в ячейку вспомогательного столбца, затем нажмите Ctrl-E, и числовые элементы из вашего столбца данных будут помещены в вспомогательная колонка. Минус ничего кроме цифр. Поскольку ваши данные имеют только числа в одном месте и должны быть взяты как целое, так сказать, их аналогами в столбце помощника будут данные, которые вы хотите добавить, и так далее. Если после этого его необходимо очистить, вставьте | Значения в ячейки результатов и удалите вспомогательный столбец и дополнительную строку.
Однако, если вы не можете этого сделать или не хотите, не можете и т.д. И действительно нуждаетесь в формуле, у Ablebits есть формула:
=IF(SUM(LEN(A1)-LEN(SUBSTITUTE(A1, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A1, LARGE(INDEX(ISNUMBER(--MID(A1,ROW(INDIRECT("$1:$"&LEN(A1))),1))* ROW(INDIRECT("$1:$"&LEN(A1))),0), ROW(INDIRECT("$1:$"&LEN(A1))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A1)))/10),"")
(все одна строка, конечно). Поместите его рядом с первой ячейкой в ваших данных (справа, как A2, если данные начинаются в A1). Выделите его и еще одну ячейку (в которой не будет ни одного экземпляра "A1") и используйте Ctrl-H, чтобы заменить "A1" адресом первой ячейки ваших данных. Или поместите его туда, где вам нужен вывод первой ячейки данных. Если вам это нужно внутри формулы, определите адрес ячейки, чтобы заменить "A1", и сделайте это, затем скопируйте и вставьте ее в формулу.
Мммм ... подождите, ваши перебирают столбцы ... поместите их под первую ячейку данных и замените "А1" соответственно. (Или не совсем в приведенном вами примере.)
В любом случае все цифры в исходных данных сохраняются, все остальное отбрасывается и создается одно числовое значение. Очевидно, что это будет проблемой для большего количества цифр, но ваши данные не будут иметь этих проблем.
Наконец, я укажу, что ваши данные очень хорошо сформированы, поэтому вы можете надежно извлечь цифры с помощью этого подхода:
=MID(A1,6,FIND(" min",A1)-6)
Поскольку первая цифра является 6-м символом, всегда, и «пробел-мин» ("мин") всегда следует за ним. Вы начинаете строку с символа 6 и динамически находите номер символа пробела в "min", затем вычитаете эту цифру 6, чтобы получить длину части цифры, чтобы функция MID () возвращала только цифры.
Это то, что вы всегда можете использовать, если ваши данные правильно сформированы и хорошо охарактеризованы. И ваш, так что ... облегчите себе!
Вы можете использовать "text to Columns" для разделения текста и чисел, а затем суммировать числа, следуя шагам ниже:
