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

Вход

Регистрация

Напомнить пароль

 

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

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Вытащить цифры из текста (Формулы/Formulas)
Вытащить цифры из текста
monstr_ork Дата: Среда, 04.04.2018, 22:24 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 70
Репутация: 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
Группа: Проверенные
Ранг: Обитатель
Сообщений: 407
Репутация: 92 ±
Замечаний: 0% ±

Excel 2007
А почему вторая 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
Группа: Пользователи
Ранг: Участник
Сообщений: 70
Репутация: 0 ±
Замечаний: 0% ±

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

Автор - monstr_ork
Дата добавления - 05.04.2018 в 07:12
китин Дата: Четверг, 05.04.2018, 07:22 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4774
Репутация: 763 ±
Замечаний: 0% ±

Excel 2007;Excel 2010
мне нужны начальные цифры


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


Не судите строго:я пытаюсь научиться
ЯД 41001877306852/WM R249698041931; Z239672726538
 
Ответить
Сообщение
мне нужны начальные цифры


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

Автор - китин
Дата добавления - 05.04.2018 в 07:22
monstr_ork Дата: Четверг, 05.04.2018, 07:57 | Сообщение № 5
Группа: Пользователи
Ранг: Участник
Сообщений: 70
Репутация: 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
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4774
Репутация: 763 ±
Замечаний: 0% ±

Excel 2007;Excel 2010
только первые цифры

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


Не судите строго:я пытаюсь научиться
ЯД 41001877306852/WM R249698041931; Z239672726538
 
Ответить
Сообщение
только первые цифры

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

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

Excel 2010, 2016 & 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)


"Черт возьми, Холмс! Но как??!!"
ЯД 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
Группа: Пользователи
Ранг: Участник
Сообщений: 70
Репутация: 0 ±
Замечаний: 0% ±

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

Автор - monstr_ork
Дата добавления - 05.04.2018 в 08:52
monstr_ork Дата: Четверг, 05.04.2018, 09:13 | Сообщение № 9
Группа: Пользователи
Ранг: Участник
Сообщений: 70
Репутация: 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
Группа: Проверенные
Ранг: Старожил
Сообщений: 1696
Репутация: 482 ±
Замечаний: 0% ±

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

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

пользовательская функция
[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)
 
Ответить
Сообщение
тему можно закрыть

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

пользовательская функция
[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]

Автор - sboy
Дата добавления - 05.04.2018 в 10:45
Светлый Дата: Четверг, 05.04.2018, 11:09 | Сообщение № 11
Группа: Проверенные
Ранг: Обитатель
Сообщений: 407
Репутация: 92 ±
Замечаний: 0% ±

Excel 2007
Сначала заменяет " от " на ";", потом все ";" заменяет на 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
Группа: Проверенные
Ранг: Обитатель
Сообщений: 407
Репутация: 92 ±
Замечаний: 0% ±

Excel 2007
Если ориентироваться на ";", то получается массивная формула:
Код
=СУММ(--ЛЕВБ(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
Группа: Пользователи
Ранг: Участник
Сообщений: 70
Репутация: 0 ±
Замечаний: 0% ±

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

Автор - monstr_ork
Дата добавления - 05.04.2018 в 13:02
Светлый Дата: Четверг, 05.04.2018, 19:21 | Сообщение № 14
Группа: Проверенные
Ранг: Обитатель
Сообщений: 407
Репутация: 92 ±
Замечаний: 0% ±

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

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


Программировать проще, чем писать стихи.
 
Ответить
Сообщение
А почему вы берете диапозон строк до 19

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

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

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