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

Вход

Регистрация

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

 

= Мир MS Excel/Извлечение чисел из текста в скобках разделенных , и : в отд - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Извлечение чисел из текста в скобках разделенных , и : в отд (Формулы/Formulas)
Извлечение чисел из текста в скобках разделенных , и : в отд
sergei026 Дата: Среда, 20.03.2019, 19:10 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 36
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Как извлечь счет из Волейбольного матча?

Из 14:00Петрольерс (ж) — Нкамба (ж)3:0 (25:22, 25:22, 25:12)+12 получить 25 22 25 22 25 12 Каждая цифра должна быть в отдельной ячейке?
К сообщению приложен файл: _1.xls (80.0 Kb)


Сообщение отредактировал sergei026 - Четверг, 21.03.2019, 13:59
 
Ответить
СообщениеКак извлечь счет из Волейбольного матча?

Из 14:00Петрольерс (ж) — Нкамба (ж)3:0 (25:22, 25:22, 25:12)+12 получить 25 22 25 22 25 12 Каждая цифра должна быть в отдельной ячейке?

Автор - sergei026
Дата добавления - 20.03.2019 в 19:10
bmv98rus Дата: Среда, 20.03.2019, 19:56 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4098
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
Жуткая подстановка
Код
=IFERROR(--SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($E1;", ";" ");":";"\");"(";"\");")";"\");"\+";"\    ");FIND("#";SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($E1;", ";"\");":";"\");"(";"\");")";"\");"\";"#";6+COLUMN(A1)))+1;3);"\";"");"")

Код
=IFERROR(--SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($E1;", ";" ");":";"\");"(";"\");")";"\");"\+";"\    ");FIND("#";SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($E1;", ";"\");":";"\");"(";"\");")";"\");"\";"#";(LEN($E1)-LEN(SUBSTITUTE($E1;"(";)))*2+COLUMN(A1)));3);"\";"");"")
хотя
Каждая цифра должна быть


можно все ж это будут числа?
К сообщению приложен файл: Copy_of_741.xlsx (42.7 Kb)


Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Среда, 20.03.2019, 21:21
 
Ответить
СообщениеЖуткая подстановка
Код
=IFERROR(--SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($E1;", ";" ");":";"\");"(";"\");")";"\");"\+";"\    ");FIND("#";SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($E1;", ";"\");":";"\");"(";"\");")";"\");"\";"#";6+COLUMN(A1)))+1;3);"\";"");"")

Код
=IFERROR(--SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($E1;", ";" ");":";"\");"(";"\");")";"\");"\+";"\    ");FIND("#";SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($E1;", ";"\");":";"\");"(";"\");")";"\");"\";"#";(LEN($E1)-LEN(SUBSTITUTE($E1;"(";)))*2+COLUMN(A1)));3);"\";"");"")
хотя
Каждая цифра должна быть


можно все ж это будут числа?

Автор - bmv98rus
Дата добавления - 20.03.2019 в 19:56
sergei026 Дата: Среда, 20.03.2019, 20:33 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 36
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
спасибо но не все строки читает корректно
например строка 10:30Тхеконг — Виньлонг1:3 (25:16, 25:27, 20:25, 18:25)+16 ВЫДАЕТ 20 25 18 25 хотя должен выдавать 25 16 25 27 20 25 18 25
Подскажите пожалуйста как сделать чтобы все строки правильно считались?


Сообщение отредактировал sergei026 - Среда, 20.03.2019, 20:40
 
Ответить
Сообщениеспасибо но не все строки читает корректно
например строка 10:30Тхеконг — Виньлонг1:3 (25:16, 25:27, 20:25, 18:25)+16 ВЫДАЕТ 20 25 18 25 хотя должен выдавать 25 16 25 27 20 25 18 25
Подскажите пожалуйста как сделать чтобы все строки правильно считались?

Автор - sergei026
Дата добавления - 20.03.2019 в 20:33
Kuzmich Дата: Среда, 20.03.2019, 21:02 | Сообщение № 4
Группа: Проверенные
Ранг: Ветеран
Сообщений: 711
Репутация: 155 ±
Замечаний: 0% ±

Excel 2003
Для случая, когда пар счетов в скобках от 3 до 5
[vba]
Код

Sub Tablica()
Dim i As Long
Dim iLastRow As Long
Dim j As Integer
Dim temp As String
iLastRow = Cells(Rows.Count, "E").End(xlUp).Row
Range("F1:O" & iLastRow).ClearContents
  With CreateObject("VBScript.RegExp")
    .Global = True
    .MultiLine = True
    .Pattern = "(\d{1,2}):(\d{1,2}), (\d{1,2}):(\d{1,2}), (\d{1,2}):(\d{1,2})(, (\d{1,2}):(\d{1,2}))?(, (\d{1,2}):(\d{1,2}))?"
   For i = 1 To iLastRow
      If .test(Cells(i, "E")) Then
        temp = .Execute(Cells(i, "E"))(0)
            j = 6
            Cells(i, j) = .Replace(temp, "$1")
            Cells(i, j + 1) = .Replace(temp, "$2")
            Cells(i, j + 2) = .Replace(temp, "$3")
            Cells(i, j + 3) = .Replace(temp, "$4")
            Cells(i, j + 4) = .Replace(temp, "$5")
            Cells(i, j + 5) = .Replace(temp, "$6")
            Cells(i, j + 6) = .Replace(temp, "$8")
            Cells(i, j + 7) = .Replace(temp, "$9")
            Cells(i, j + 8) = .Replace(temp, "$11")
            Cells(i, j + 9) = .Replace(temp, "$12")
     End If
    Next
  End With
End Sub
[/vba]
 
Ответить
СообщениеДля случая, когда пар счетов в скобках от 3 до 5
[vba]
Код

Sub Tablica()
Dim i As Long
Dim iLastRow As Long
Dim j As Integer
Dim temp As String
iLastRow = Cells(Rows.Count, "E").End(xlUp).Row
Range("F1:O" & iLastRow).ClearContents
  With CreateObject("VBScript.RegExp")
    .Global = True
    .MultiLine = True
    .Pattern = "(\d{1,2}):(\d{1,2}), (\d{1,2}):(\d{1,2}), (\d{1,2}):(\d{1,2})(, (\d{1,2}):(\d{1,2}))?(, (\d{1,2}):(\d{1,2}))?"
   For i = 1 To iLastRow
      If .test(Cells(i, "E")) Then
        temp = .Execute(Cells(i, "E"))(0)
            j = 6
            Cells(i, j) = .Replace(temp, "$1")
            Cells(i, j + 1) = .Replace(temp, "$2")
            Cells(i, j + 2) = .Replace(temp, "$3")
            Cells(i, j + 3) = .Replace(temp, "$4")
            Cells(i, j + 4) = .Replace(temp, "$5")
            Cells(i, j + 5) = .Replace(temp, "$6")
            Cells(i, j + 6) = .Replace(temp, "$8")
            Cells(i, j + 7) = .Replace(temp, "$9")
            Cells(i, j + 8) = .Replace(temp, "$11")
            Cells(i, j + 9) = .Replace(temp, "$12")
     End If
    Next
  End With
End Sub
[/vba]

Автор - Kuzmich
Дата добавления - 20.03.2019 в 21:02
sergei026 Дата: Среда, 20.03.2019, 21:03 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 36
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Это получается нужна на каждую строку смотреть и отдельно формулу менять?
У меня несколько тысяч строк, а нельзя это как то одной формулой или скриптом сделать? (Чтобы просто вниз протянуть)
 
Ответить
СообщениеЭто получается нужна на каждую строку смотреть и отдельно формулу менять?
У меня несколько тысяч строк, а нельзя это как то одной формулой или скриптом сделать? (Чтобы просто вниз протянуть)

Автор - sergei026
Дата добавления - 20.03.2019 в 21:03
bmv98rus Дата: Среда, 20.03.2019, 21:22 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4098
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
sergei026, выше подправил. Как не углядел очевидное - не знаю


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщениеsergei026, выше подправил. Как не углядел очевидное - не знаю

Автор - bmv98rus
Дата добавления - 20.03.2019 в 21:22
Светлый Дата: Среда, 20.03.2019, 21:44 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
Мой вариант массивной формулы:
Код
=--ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПСТР($E1;МАКС((ПСТР($E1;СТРОКА($1:$99);1)="(")*СТРОКА($1:$99))+1;99);", ";":");")";":0:0:0:0:0:0:0:0:");":";ПОВТОР(" ";99));СТОЛБЕЦ(A1)*99-98;99)
Протягиваем вправо на 10 столбцов и вниз до конца.


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

Сообщение отредактировал Светлый - Среда, 20.03.2019, 21:50
 
Ответить
СообщениеМой вариант массивной формулы:
Код
=--ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПСТР($E1;МАКС((ПСТР($E1;СТРОКА($1:$99);1)="(")*СТРОКА($1:$99))+1;99);", ";":");")";":0:0:0:0:0:0:0:0:");":";ПОВТОР(" ";99));СТОЛБЕЦ(A1)*99-98;99)
Протягиваем вправо на 10 столбцов и вниз до конца.

Автор - Светлый
Дата добавления - 20.03.2019 в 21:44
sergei026 Дата: Среда, 20.03.2019, 21:50 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 36
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Спасибо всем большое Вы Гении))
 
Ответить
СообщениеСпасибо всем большое Вы Гении))

Автор - sergei026
Дата добавления - 20.03.2019 в 21:50
bmv98rus Дата: Среда, 20.03.2019, 21:56 | Сообщение № 9
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4098
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
sergei026, ради статистики на своих тысячах строк, посмотрите на время расчета моей и формулы от Светлый.


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщениеsergei026, ради статистики на своих тысячах строк, посмотрите на время расчета моей и формулы от Светлый.

Автор - bmv98rus
Дата добавления - 20.03.2019 в 21:56
Светлый Дата: Четверг, 21.03.2019, 06:52 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
Пересмотрел метод поиска результатов. Формула простая, работает в офисе 2003:
Код
=--ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПСТР($E1;1+МИН(ПОИСК({"(??:*)";"(?:*)";"(???:???)"};$E1&"(0:(00:(000:000)"));99);", ";":");")";":0:0:0:0:0:0:0:0:");":";ПОВТОР(" ";99));СТОЛБЕЦ(A1)*99-98;99)
И быстрее, чем первая. :)


Программировать проще, чем писать стихи.
 
Ответить
СообщениеПересмотрел метод поиска результатов. Формула простая, работает в офисе 2003:
Код
=--ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПСТР($E1;1+МИН(ПОИСК({"(??:*)";"(?:*)";"(???:???)"};$E1&"(0:(00:(000:000)"));99);", ";":");")";":0:0:0:0:0:0:0:0:");":";ПОВТОР(" ";99));СТОЛБЕЦ(A1)*99-98;99)
И быстрее, чем первая. :)

Автор - Светлый
Дата добавления - 21.03.2019 в 06:52
bmv98rus Дата: Четверг, 21.03.2019, 08:03 | Сообщение № 11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4098
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
Светлый, Ну меня прям задело :-) . На самом деле, спасибо что напомнили про маски в Search , но моя формула по скорости в несколько раз быстрее. Проверил на 100к строк. у меня получилось разница в 4 раза.


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеСветлый, Ну меня прям задело :-) . На самом деле, спасибо что напомнили про маски в Search , но моя формула по скорости в несколько раз быстрее. Проверил на 100к строк. у меня получилось разница в 4 раза.

Автор - bmv98rus
Дата добавления - 21.03.2019 в 08:03
Светлый Дата: Четверг, 21.03.2019, 10:15 | Сообщение № 12
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
моя формула по скорости в несколько раз быстрее

bmv98rus, зато моя формула правильно работает в строках, где есть "(Золотая партия ??:??)", правда результат этой партии я отбрасываю. У Вас начинает выводить результаты со второй игры.
И ещё ошибка в строках "(???:???)". Выводит только две цифры из трёх. Хотя, в задании это не упоминалось.
Не в обиду. Просто заметил ошибки в решении.


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

bmv98rus, зато моя формула правильно работает в строках, где есть "(Золотая партия ??:??)", правда результат этой партии я отбрасываю. У Вас начинает выводить результаты со второй игры.
И ещё ошибка в строках "(???:???)". Выводит только две цифры из трёх. Хотя, в задании это не упоминалось.
Не в обиду. Просто заметил ошибки в решении.

Автор - Светлый
Дата добавления - 21.03.2019 в 10:15
bmv98rus Дата: Четверг, 21.03.2019, 10:28 | Сообщение № 13
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4098
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
Цитата Светлый, 21.03.2019 в 10:15, в сообщении № 12 ()
И ещё ошибка в строках "(???:???)". Выводит только две цифры из трёх.

ну тут я сепециально ограничил, ибо я слабо представляю волейбольную игру с таким счетом 56:54 пока рекорд. исправить не сложно и на быстродействие не повлияет.
А вот ехал в метро и думал "Сейчас придет Александр и выпишет мне "золотую партию" за помощь в теме с таким названием!" Задание было интересным , давайте название придумывать.
Извлечение чисел и набора в скобках разделенных , и : в отдельные ячейки. может так?


Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Четверг, 21.03.2019, 10:52
 
Ответить
Сообщение
Цитата Светлый, 21.03.2019 в 10:15, в сообщении № 12 ()
И ещё ошибка в строках "(???:???)". Выводит только две цифры из трёх.

ну тут я сепециально ограничил, ибо я слабо представляю волейбольную игру с таким счетом 56:54 пока рекорд. исправить не сложно и на быстродействие не повлияет.
А вот ехал в метро и думал "Сейчас придет Александр и выпишет мне "золотую партию" за помощь в теме с таким названием!" Задание было интересным , давайте название придумывать.
Извлечение чисел и набора в скобках разделенных , и : в отдельные ячейки. может так?

Автор - bmv98rus
Дата добавления - 21.03.2019 в 10:28
Светлый Дата: Четверг, 21.03.2019, 10:50 | Сообщение № 14
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
sergei026, Желательно поменять название темы
Извлечение чисел из текста в скобках разделенных , и : в отдельные ячейки
Заходите в режим Правка в первом посте и меняете название.


Программировать проще, чем писать стихи.
 
Ответить
Сообщениеsergei026, Желательно поменять название темы
Извлечение чисел из текста в скобках разделенных , и : в отдельные ячейки
Заходите в режим Правка в первом посте и меняете название.

Автор - Светлый
Дата добавления - 21.03.2019 в 10:50
Светлый Дата: Четверг, 21.03.2019, 13:51 | Сообщение № 15
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
Сейчас и из золотых партий будет числа выковыривать:
Код
=--ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПСТР($E1;1+МИН(ПОИСК({"(??:*)";"(?:*)";"(???:???)"};$E1&"(0:(00:(000:000)"));99);") (Золотая партия ";":");", ";":");")";":0:0:0:0:0:0:0:0:0:0:");":";ПОВТОР(" ";99));СТОЛБЕЦ(A1)*99-98;99)
Растянуть вправо на 12 и вниз до конца.


Программировать проще, чем писать стихи.
 
Ответить
СообщениеСейчас и из золотых партий будет числа выковыривать:
Код
=--ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПСТР($E1;1+МИН(ПОИСК({"(??:*)";"(?:*)";"(???:???)"};$E1&"(0:(00:(000:000)"));99);") (Золотая партия ";":");", ";":");")";":0:0:0:0:0:0:0:0:0:0:");":";ПОВТОР(" ";99));СТОЛБЕЦ(A1)*99-98;99)
Растянуть вправо на 12 и вниз до конца.

Автор - Светлый
Дата добавления - 21.03.2019 в 13:51
Светлый Дата: Пятница, 22.03.2019, 12:23 | Сообщение № 16
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
Кому покажется интересным. Сократил формулу извлечения фрагмента "(11:11, 11...):
Код
=ПРОСМОТР(9;-(ПСТР($E2;СТРОКА($1:$99);2)&")");ПСТР($E2;СТРОКА($1:$99);99))
И формула для темы:
Код
=--ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПРОСМОТР(;--(ПСТР($E1;СТРОКА($1:$98);2)&")");ПСТР($E1;СТРОКА($2:$99);99));") (Золотая партия ";":");", ";":");")";":0:0:0:0:0:0:0:0:0:0:");":";ПОВТОР(" ";99));СТОЛБЕЦ(A1)*99-98;99)
*
Код
=ПСТР($E2;-ПРОСМОТР(;-ПОИСК("(?"&{"?:*)";":*)";"??:???)"};$E2));99)
Код
=--ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПСТР($E3;1-ПРОСМОТР(;-ПОИСК("(?"&{"?:*)";":*)";"??:???)"};$E3));99);") (Золотая партия ";":");", ";":");")";":0:0:0:0:0:0:0:0:0:0:");":";ПОВТОР(" ";99));СТОЛБЕЦ(A3)*99-98;99)


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

Сообщение отредактировал Светлый - Пятница, 22.03.2019, 18:41
 
Ответить
СообщениеКому покажется интересным. Сократил формулу извлечения фрагмента "(11:11, 11...):
Код
=ПРОСМОТР(9;-(ПСТР($E2;СТРОКА($1:$99);2)&")");ПСТР($E2;СТРОКА($1:$99);99))
И формула для темы:
Код
=--ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПРОСМОТР(;--(ПСТР($E1;СТРОКА($1:$98);2)&")");ПСТР($E1;СТРОКА($2:$99);99));") (Золотая партия ";":");", ";":");")";":0:0:0:0:0:0:0:0:0:0:");":";ПОВТОР(" ";99));СТОЛБЕЦ(A1)*99-98;99)
*
Код
=ПСТР($E2;-ПРОСМОТР(;-ПОИСК("(?"&{"?:*)";":*)";"??:???)"};$E2));99)
Код
=--ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПСТР($E3;1-ПРОСМОТР(;-ПОИСК("(?"&{"?:*)";":*)";"??:???)"};$E3));99);") (Золотая партия ";":");", ";":");")";":0:0:0:0:0:0:0:0:0:0:");":";ПОВТОР(" ";99));СТОЛБЕЦ(A3)*99-98;99)

Автор - Светлый
Дата добавления - 22.03.2019 в 12:23
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Извлечение чисел из текста в скобках разделенных , и : в отд (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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