Версия для слабовидящих
Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

 

= Мир MS Excel/Вытащить цифры из текста - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_, DrMini  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Вытащить цифры из текста (Формулы/Formulas)
Вытащить цифры из текста
monstr_ork Дата: Среда, 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
 
Ответить
СообщениеЗдравствуйте форумчане, помогите с формулой, если это конечно возможно с помощь нее сделать.
Есть файл (во вложении), там производят запись такого вида "4 от 13;15 от 6;7 от 9;15;4 от 18" примерно, она может изменять, т.е. "4 от 15" и т.д. Но единственное что, всегда новое значение идет после знака ";". мне важно вытащить только цифры после знака ";" после "от" меня не интересуют, а потом сложить. Должно получиться примерно это "4+15+7+15+4 = 59", т.е. мне важно число только 59.
Автор - monstr_ork
Дата добавления - 04.04.2018 в 22:24
Светлый Дата: Среда, 04.04.2018, 23:44 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1871
Репутация: 538 ±
Замечаний: 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
 
Ответить
СообщениеА почему вторая 4 не попала в сумму, а вторая 15 попала? И 18 как попала?
Если просто сумма нечётных по-порядку чисел формула массива:
=СУММ(--(0&СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(";"&ПОДСТАВИТЬ(A2;" от ";";");";";ПОВТОР(" ";99));СТРОКА($1:$19)*198-99;99))))

Или:
=СУММ(--(0&СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2;" от ";";");";";ПОВТОР(" ";99));СТРОКА($1:$19)*198-197;99))))
Автор - Светлый
Дата добавления - 04.04.2018 в 23:44
monstr_ork Дата: Четверг, 05.04.2018, 07:12 | Сообщение № 3
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 133
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Светлый, четные или не четные тут не причем, мне нужны начальные цифры, это обозначение штук товара. Мне нужно их посчитать
 
Ответить
СообщениеСветлый, четные или не четные тут не причем, мне нужны начальные цифры, это обозначение штук товара. Мне нужно их посчитать
Автор - monstr_ork
Дата добавления - 05.04.2018 в 07:12
китин Дата: Четверг, 05.04.2018, 07:22 | Сообщение № 4
Группа: Модераторы
Ранг: Экселист
Сообщений: 7035
Репутация: 1079 ±
Замечаний: 0% ±

Excel 2007;2010;2016
Цитата monstr_ork, 05.04.2018 в 07:12, в сообщении № 3 ( писал(а)):
мне нужны начальные цифры


а как в сумму в вашем файле попало 18? про это Светлый, и спрашивал
Цитата Светлый, 04.04.2018 в 23:44, в сообщении № 2 ( писал(а)):
И 18 как попала?

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
Сообщение
Цитата monstr_ork, 05.04.2018 в 07:12, в сообщении № 3 ( писал(а)):
мне нужны начальные цифры


а как в сумму в вашем файле попало 18? про это Светлый, и спрашивал
Цитата Светлый, 04.04.2018 в 23:44, в сообщении № 2 ( писал(а)):
И 18 как попала?
Автор - китин
Дата добавления - 05.04.2018 в 07:22
monstr_ork Дата: Четверг, 05.04.2018, 07:57 | Сообщение № 5
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 133
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
китин, Ошибся, 4+15+7+15+4, в общем только первые цифры меня интересуют
 
Ответить
Сообщениекитин, Ошибся, 4+15+7+15+4, в общем только первые цифры меня интересуют
Автор - monstr_ork
Дата добавления - 05.04.2018 в 07:57
китин Дата: Четверг, 05.04.2018, 08:21 | Сообщение № 6
Группа: Модераторы
Ранг: Экселист
Сообщений: 7035
Репутация: 1079 ±
Замечаний: 0% ±

Excel 2007;2010;2016
Цитата monstr_ork, 05.04.2018 в 07:57, в сообщении № 5 ( писал(а)):
только первые цифры

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

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
Сообщение
Цитата monstr_ork, 05.04.2018 в 07:57, в сообщении № 5 ( писал(а)):
только первые цифры

а формулу Светлый, проверяли? там походу все правильно решено
Автор - китин
Дата добавления - 05.04.2018 в 08:21
Pelena Дата: Четверг, 05.04.2018, 08:48 | Сообщение № 7
Группа: Админы
Ранг: Местный житель
Сообщений: 19521
Репутация: 4634 ±
Замечаний: ±

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
 
Ответить
СообщениеКак вариант, Данные -- Текст по столбцам -- с разделителем ; -- Готово
Дальше формула массива
=СУММ(--ЛЕВСИМВ(C2:G2;ПОИСК(" ";C2:G2&" ")-1))

Если одной формулой, то длинновато получилось
=СУММ(--(0&ЛЕВСИМВ(СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(A2;";";ПОВТОР(" ";99));СТРОКА($1:$19)*99-98;99));ПОИСК(" ";СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(A2;";";ПОВТОР(" ";99));СТРОКА($1:$19)*99-98;99))&" ")-1)))
Автор - Pelena
Дата добавления - 05.04.2018 в 08:48
monstr_ork Дата: Четверг, 05.04.2018, 08:52 | Сообщение № 8
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 133
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Светлый, Да, она работает, но т.к. я не правильно написал, там формула считает 18, а не 4 в конце. Может поправите пожалуйста, потому что я ни чего в ней не понимаю
Оффтоп:
Буду очень благодарен, если напишите как она работает, ну это чисто для меня. для развития
 
Ответить
СообщениеСветлый, Да, она работает, но т.к. я не правильно написал, там формула считает 18, а не 4 в конце. Может поправите пожалуйста, потому что я ни чего в ней не понимаю
[offtop]Буду очень благодарен, если напишите как она работает, ну это чисто для меня. для развития[/offtop]
Автор - monstr_ork
Дата добавления - 05.04.2018 в 08:52
monstr_ork Дата: Четверг, 05.04.2018, 09:13 | Сообщение № 9
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 133
Репутация: 0 ±
Замечаний: 0% ±

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


Сообщение отредактировал monstr_ork - Четверг, 05.04.2018, 09:14
 
Ответить
СообщениеPelena, все работает, всем спасибо за помощь, тему можно закрыть
Автор - monstr_ork
Дата добавления - 05.04.2018 в 09:13
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 ( писал(а)):
это чисто для меня. для развития

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

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

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

Яндекс: 410016850021169
 
Ответить
Сообщение
Цитата 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 = Тrue        .Pattern = " от \d+"        q = Split(.Replace(t; ""); ";")            For i = 0 To UBound(q)                monstr_ork = monstr_ork + Val(q(i))            Next    End WithEnd Function
[/vba]
Автор - sboy
Дата добавления - 05.04.2018 в 10:45
Светлый Дата: Четверг, 05.04.2018, 11:09 | Сообщение № 11
Группа: Друзья
Ранг: Старожил
Сообщений: 1871
Репутация: 538 ±
Замечаний: 0% ±

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

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

Сообщение отредактировал Светлый - Четверг, 05.04.2018, 11:26
 
Ответить
СообщениеСначала заменяет " от " на ";", потом все ";" заменяет на 99 пробелов каждую. Потом в массив извлекает фрагменты получившейся строки по 99 символов с пропуском между ними по 99 символов. В этих фрагментах удаляет пробелы, слева присоединяет незначащий 0. (для чисел он ничего не меняет, а для пустых фрагментов он предотвращает появление ошибки при операции "--") -- преобразует текстовые фрагменты в числа и СУММ их суммирует. Таким образом получаем сумму нечётных (по порядку нумерации слева направо) чисел из исходной строки.
Эта формула не обращает внимания на ; и от . Если это возможно, лучше исправить исходные данные, чтобы они были парами 4 от 9;7 от 20 и т.д. а там встречается ;15;
Автор - Светлый
Дата добавления - 05.04.2018 в 11:09
Светлый Дата: Четверг, 05.04.2018, 12:11 | Сообщение № 12
Группа: Друзья
Ранг: Старожил
Сообщений: 1871
Репутация: 538 ±
Замечаний: 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
 
Ответить
СообщениеЕсли ориентироваться на ";", то получается массивная формула:
=СУММ(--ЛЕВБ(0&СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(A2;";";ПОВТОР(" ";99));СТРОКА($1:$19)*99-98;99));3))

Для неё числа максимум двухзначные.
Для бОльших чисел:
=СУММ(--ЛЕВБ(0&ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(A2;";";ПОВТОР(" ";99));СТРОКА($1:$19)*99-98;99));" ";"   ");5))
Автор - Светлый
Дата добавления - 05.04.2018 в 12:11
monstr_ork Дата: Четверг, 05.04.2018, 13:02 | Сообщение № 13
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 133
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Светлый, мне не важно какое число, четное или нет. Это просто подсчет первое число это шт. а второе это дата. Но очень благодарен за разбор формулы.
А почему вы берете диапозон строк до 19? Это что то значит или просто как пример взяли?
sboy, спасибо за функцию.
 
Ответить
СообщениеСветлый, мне не важно какое число, четное или нет. Это просто подсчет первое число это шт. а второе это дата. Но очень благодарен за разбор формулы.
А почему вы берете диапозон строк до 19? Это что то значит или просто как пример взяли?
sboy, спасибо за функцию.
Автор - monstr_ork
Дата добавления - 05.04.2018 в 13:02
Светлый Дата: Четверг, 05.04.2018, 19:21 | Сообщение № 14
Группа: Друзья
Ранг: Старожил
Сообщений: 1871
Репутация: 538 ±
Замечаний: 0% ±

Excel 2013, 2016
Цитата monstr_ork, 05.04.2018 в 13:02, в сообщении № 13 ( писал(а)):
А почему вы берете диапозон строк до 19

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

Программировать проще, чем писать стихи.
 
Ответить
Сообщение
Цитата monstr_ork, 05.04.2018 в 13:02, в сообщении № 13 ( писал(а)):
А почему вы берете диапозон строк до 19

В образце 9 чисел. Я взял с запасом, т.е. может быть до 19 чисел.
Автор - Светлый
Дата добавления - 05.04.2018 в 19:21
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Вытащить цифры из текста (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Рейтинг@Mail.ru Яндекс.Метрика Яндекс цитирования
© 2010-2025 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!