Вытащить цифры из текста

Дата: Среда, 04.04.2018, 22:24 | Сообщение № 1

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 133

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Здравствуйте форумчане, помогите с формулой, если это конечно возможно с помощь нее сделать.
Есть файл (во вложении), там производят запись такого вида "4 от 13;15 от 6;7 от 9;15;4 от 18" примерно, она может изменять, т.е. "4 от 15" и т.д. Но единственное что, всегда новое значение идет после знака ";". мне важно вытащить только цифры после знака ";" после "от" меня не интересуют, а потом сложить. Должно получиться примерно это "4+15+7+15+4 = 59", т.е. мне важно число только 59.

К сообщению приложен файл: 5842112.xlsx (11.5 Kb)

Сообщение отредактировал monstr_ork - Четверг, 05.04.2018, 07:57

Дата: Среда, 04.04.2018, 23:44 | Сообщение № 2

Группа: Друзья

Ранг: Старожил

Сообщений: 1829

Репутация: 510 ±

Замечаний: 0% ±

Excel 2013, 2016

А почему вторая 4 не попала в сумму, а вторая 15 попала? И 18 как попала?
Если просто сумма нечётных по-порядку чисел формула массива:

Код

=СУММ(--(0&СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(";"&ПОДСТАВИТЬ(A2;" от ";";");";";ПОВТОР(" ";99));СТРОКА($1:$19)*198-99;99))))

Или:

Код

=СУММ(--(0&СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2;" от ";";");";";ПОВТОР(" ";99));СТРОКА($1:$19)*198-197;99))))

Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Четверг, 05.04.2018, 00:19

Дата: Четверг, 05.04.2018, 07:12 | Сообщение № 3

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 133

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Светлый, четные или не четные тут не причем, мне нужны начальные цифры, это обозначение штук товара. Мне нужно их посчитать

китин

Дата: Четверг, 05.04.2018, 07:22 | Сообщение № 4

Группа: Модераторы

Ранг: Экселист

Сообщений: 7014

Репутация: 1073 ±

Замечаний: 0% ±

Excel 2007;2010;2016

Цитата monstr_ork, 05.04.2018 в 07:12, в сообщении № 3 ()

мне нужны начальные цифры

а как в сумму в вашем файле попало 18? про это Светлый, и спрашивал

Цитата Светлый, 04.04.2018 в 23:44, в сообщении № 2 ()

И 18 как попала?

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

Дата: Четверг, 05.04.2018, 07:57 | Сообщение № 5

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 133

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

китин, Ошибся, 4+15+7+15+4, в общем только первые цифры меня интересуют

китин

Дата: Четверг, 05.04.2018, 08:21 | Сообщение № 6

Группа: Модераторы

Ранг: Экселист

Сообщений: 7014

Репутация: 1073 ±

Замечаний: 0% ±

Excel 2007;2010;2016

Цитата monstr_ork, 05.04.2018 в 07:57, в сообщении № 5 ()

только первые цифры

а формулу Светлый, проверяли? там походу все правильно решено

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

Pelena

Дата: Четверг, 05.04.2018, 08:48 | Сообщение № 7

Группа: Админы

Ранг: Местный житель

Сообщений: 19174

Репутация: 4413 ±

Замечаний: ±

Excel 365 & Mac Excel

Как вариант, Данные -- Текст по столбцам -- с разделителем ; -- Готово
Дальше формула массива

Код

=СУММ(--ЛЕВСИМВ(C2:G2;ПОИСК(" ";C2:G2&" ")-1))

Если одной формулой, то длинновато получилось

Код

=СУММ(--(0&ЛЕВСИМВ(СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(A2;";";ПОВТОР(" ";99));СТРОКА($1:$19)*99-98;99));ПОИСК(" ";СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(A2;";";ПОВТОР(" ";99));СТРОКА($1:$19)*99-98;99))&" ")-1)))

К сообщению приложен файл: 5233146.xlsx (12.0 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Дата: Четверг, 05.04.2018, 08:52 | Сообщение № 8

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 133

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Светлый, Да, она работает, но т.к. я не правильно написал, там формула считает 18, а не 4 в конце. Может поправите пожалуйста, потому что я ни чего в ней не понимаю
[offtop]Буду очень благодарен, если напишите как она работает, ну это чисто для меня. для развития[/offtop]

Дата: Четверг, 05.04.2018, 09:13 | Сообщение № 9

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 133

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Pelena, все работает, всем спасибо за помощь, тему можно закрыть

Сообщение отредактировал monstr_ork - Четверг, 05.04.2018, 09:14

sboy

Дата: Четверг, 05.04.2018, 10:45 | Сообщение № 10

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2566

Репутация: 724 ±

Замечаний: 0% ±

Excel 2010

Цитата monstr_ork, 05.04.2018 в 09:13, в сообщении № 9 ()

тему можно закрыть

не торопитесь все закрывать

Цитата monstr_ork, 05.04.2018 в 08:52, в сообщении № 8 ()

это чисто для меня. для развития

пользовательская функция
[vba]

Код

Function monstr_ork(t As String)
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = " от \d+"
        q = Split(.Replace(t, ""), ";")
            For i = 0 To UBound(q)
                monstr_ork = monstr_ork + Val(q(i))
            Next
    End With
End Function

[/vba]

К сообщению приложен файл: 5842112.xlsm (17.5 Kb)

Яндекс: 410016850021169

Дата: Четверг, 05.04.2018, 11:09 | Сообщение № 11

Группа: Друзья

Ранг: Старожил

Сообщений: 1829

Репутация: 510 ±

Замечаний: 0% ±

Excel 2013, 2016

Сначала заменяет " от " на ";", потом все ";" заменяет на 99 пробелов каждую. Потом в массив извлекает фрагменты получившейся строки по 99 символов с пропуском между ними по 99 символов. В этих фрагментах удаляет пробелы, слева присоединяет незначащий 0. (для чисел он ничего не меняет, а для пустых фрагментов он предотвращает появление ошибки при операции "--") -- преобразует текстовые фрагменты в числа и СУММ их суммирует. Таким образом получаем сумму нечётных (по порядку нумерации слева направо) чисел из исходной строки.
Эта формула не обращает внимания на ; и от . Если это возможно, лучше исправить исходные данные, чтобы они были парами 4 от 9;7 от 20 и т.д. а там встречается ;15;

Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Четверг, 05.04.2018, 11:26

Дата: Четверг, 05.04.2018, 12:11 | Сообщение № 12

Группа: Друзья

Ранг: Старожил

Сообщений: 1829

Репутация: 510 ±

Замечаний: 0% ±

Excel 2013, 2016

Если ориентироваться на ";", то получается массивная формула:

Код

=СУММ(--ЛЕВБ(0&СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(A2;";";ПОВТОР(" ";99));СТРОКА($1:$19)*99-98;99));3))

Для неё числа максимум двухзначные.
Для бОльших чисел:

Код

=СУММ(--ЛЕВБ(0&ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(A2;";";ПОВТОР(" ";99));СТРОКА($1:$19)*99-98;99));" ";" ");5))

Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Четверг, 05.04.2018, 12:12

Дата: Четверг, 05.04.2018, 13:02 | Сообщение № 13

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 133

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Светлый, мне не важно какое число, четное или нет. Это просто подсчет первое число это шт. а второе это дата. Но очень благодарен за разбор формулы.
А почему вы берете диапозон строк до 19? Это что то значит или просто как пример взяли?
sboy, спасибо за функцию.

Дата: Четверг, 05.04.2018, 19:21 | Сообщение № 14

Группа: Друзья

Ранг: Старожил

Сообщений: 1829

Репутация: 510 ±

Замечаний: 0% ±

Excel 2013, 2016

Цитата monstr_ork, 05.04.2018 в 13:02, в сообщении № 13 ()

А почему вы берете диапозон строк до 19

В образце 9 чисел. Я взял с запасом, т.е. может быть до 19 чисел.

Программировать проще, чем писать стихи.