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

Вход

Регистрация

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

 

= Мир MS Excel/Извлечь число из текста - Мир MS Excel

  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_, DrMini  
Извлечь число из текста
ekuritsyn Дата: Понедельник, 03.08.2015, 15:56 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
проблема стара как мир.
нужно извлечь номера телефонов из текста.
телефоны записаны в разных форматах (6 цифр, 10 цифр, 11 цифр, +7)
располагаются в разных местах в ячейке
пример во вложении.
в ячейках помимо телефонов встречаются и другие цифры.
читал форум, не получилось найти решение...
К сообщению приложен файл: 7403382.xlsx (9.7 Kb)


Сообщение отредактировал ekuritsyn - Понедельник, 03.08.2015, 15:57
 
Ответить
Сообщениепроблема стара как мир.
нужно извлечь номера телефонов из текста.
телефоны записаны в разных форматах (6 цифр, 10 цифр, 11 цифр, +7)
располагаются в разных местах в ячейке
пример во вложении.
в ячейках помимо телефонов встречаются и другие цифры.
читал форум, не получилось найти решение...

Автор - ekuritsyn
Дата добавления - 03.08.2015 в 15:56
Pelena Дата: Понедельник, 03.08.2015, 16:13 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 19517
Репутация: 4632 ±
Замечаний: ±

Excel 365 & Mac Excel
Так подойдёт?
Код
=ПСТР(A1;ПОИСК("к.т";A1)+4;ПОИСК(" ";A1;ПОИСК("к.т";A1)+4)-ПОИСК("к.т";A1)-4)
К сообщению приложен файл: 4778496.xlsx (10.8 Kb)


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеТак подойдёт?
Код
=ПСТР(A1;ПОИСК("к.т";A1)+4;ПОИСК(" ";A1;ПОИСК("к.т";A1)+4)-ПОИСК("к.т";A1)-4)

Автор - Pelena
Дата добавления - 03.08.2015 в 16:13
krosav4ig Дата: Понедельник, 03.08.2015, 16:51 | Сообщение № 3
Группа: Друзья
Ранг: Старожил
Сообщений: 2348
Репутация: 997 ±
Замечаний: 0% ±

Excel 2007,2010,2013
как вариант
Код
=МАКС(ЕСЛИОШИБКА(--ПСТР(A1;СТРОКА($1:$99);СТОЛБЕЦ($A:$L));))
К сообщению приложен файл: 1683264.xlsx (10.5 Kb)


email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
 
Ответить
Сообщениекак вариант
Код
=МАКС(ЕСЛИОШИБКА(--ПСТР(A1;СТРОКА($1:$99);СТОЛБЕЦ($A:$L));))

Автор - krosav4ig
Дата добавления - 03.08.2015 в 16:51
krosav4ig Дата: Понедельник, 03.08.2015, 18:16 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 2348
Репутация: 997 ±
Замечаний: 0% ±

Excel 2007,2010,2013
еще вариант с UDF
[vba]
Код
Function Phone(str$)
     With CreateObject("VBScript.RegExp")
         .Pattern = "(((\+7|8)(|-| )?)?((\(|-| )?\d{3}(\)|-| )?){2})?((-| )?\d{2}){2,3}"
         Phone = IIf(.Test(str), Trim(.Execute(str)(0)), "")
     End With
End Function
[/vba]
К сообщению приложен файл: 7403382-1-.xls (36.0 Kb)


email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Сообщение отредактировал krosav4ig - Понедельник, 03.08.2015, 18:27
 
Ответить
Сообщениееще вариант с UDF
[vba]
Код
Function Phone(str$)
     With CreateObject("VBScript.RegExp")
         .Pattern = "(((\+7|8)(|-| )?)?((\(|-| )?\d{3}(\)|-| )?){2})?((-| )?\d{2}){2,3}"
         Phone = IIf(.Test(str), Trim(.Execute(str)(0)), "")
     End With
End Function
[/vba]

Автор - krosav4ig
Дата добавления - 03.08.2015 в 18:16
ekuritsyn Дата: Понедельник, 03.08.2015, 19:55 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
А что если в некоторых случаях перед номером телефона стоит "к.т." или "кт" или вообще ничего не стоит?
 
Ответить
СообщениеА что если в некоторых случаях перед номером телефона стоит "к.т." или "кт" или вообще ничего не стоит?

Автор - ekuritsyn
Дата добавления - 03.08.2015 в 19:55
Nic70y Дата: Понедельник, 03.08.2015, 20:20 | Сообщение № 6
Группа: Друзья
Ранг: Экселист
Сообщений: 9131
Репутация: 2415 ±
Замечаний: 0% ±

Excel 2010
А что если
формула массива:
Код
=ПСТР(A1;МИН(ЕСЛИ(ЕЧИСЛО(--ПСТР(ПОДСТАВИТЬ(A1;" ";"д");СТРОКА($1:$999);6));СТРОКА($1:$999)));6)&ЕСЛИ(ЕЧИСЛО(--ПСТР(A1;ПОИСК(ПСТР(A1;МИН(ЕСЛИ(ЕЧИСЛО(--ПСТР(ПОДСТАВИТЬ(A1;" ";"д");СТРОКА($1:$999);6));СТРОКА($1:$999)));6);A1)+6;5));ПСТР(A1;ПОИСК(ПСТР(A1;МИН(ЕСЛИ(ЕЧИСЛО(--ПСТР(ПОДСТАВИТЬ(A1;" ";"д");СТРОКА($1:$999);6));СТРОКА($1:$999)));6);A1)+6;5);"")
К сообщению приложен файл: 000.1.23.49.xlsx (10.9 Kb)


ЮMoney 41001841029809
 
Ответить
Сообщение
А что если
формула массива:
Код
=ПСТР(A1;МИН(ЕСЛИ(ЕЧИСЛО(--ПСТР(ПОДСТАВИТЬ(A1;" ";"д");СТРОКА($1:$999);6));СТРОКА($1:$999)));6)&ЕСЛИ(ЕЧИСЛО(--ПСТР(A1;ПОИСК(ПСТР(A1;МИН(ЕСЛИ(ЕЧИСЛО(--ПСТР(ПОДСТАВИТЬ(A1;" ";"д");СТРОКА($1:$999);6));СТРОКА($1:$999)));6);A1)+6;5));ПСТР(A1;ПОИСК(ПСТР(A1;МИН(ЕСЛИ(ЕЧИСЛО(--ПСТР(ПОДСТАВИТЬ(A1;" ";"д");СТРОКА($1:$999);6));СТРОКА($1:$999)));6);A1)+6;5);"")

Автор - Nic70y
Дата добавления - 03.08.2015 в 20:20
ekuritsyn Дата: Вторник, 04.08.2015, 21:05 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Спасибо. В четверг смогу проверить и отписаться.
 
Ответить
СообщениеСпасибо. В четверг смогу проверить и отписаться.

Автор - ekuritsyn
Дата добавления - 04.08.2015 в 21:05
Gekko Дата: Четверг, 06.08.2015, 11:38 | Сообщение № 8
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Здравствуйте уважаемые форумчане, столкнулся с такой проблемой. Сразу оговорюсь что пользователь начинающий (exel) потому сильно не пинайте :)
С копировал из одной программы данные с цифрами в ексель (числа) и они почему-то стали отображаться по-разному все. Какие-то смещены вправо а какие-то выравниваются с левой стороны, я так понимаю что это из-за разности форматов. Те, что справа это воспринимается как текст а те что слева как числа.
Вот и вопрос возник, как все перевести в формат числа?
Менял формат выделенных ячеек, ничего не получилось. Одни товарищи сказали что нужно руками все переписывать, но я сомневаюсь в этом. Какое-то совсем дикое решение на мой взгляд.
Потому что может быть и 1000 ячеек и 5000. Руками не вариант. Уверен что есть решение, и оно весьма просто должно быть.
***
Я не прошу дать мне готовый ответ, буду рад ссылке, что читать и куда смотреть. Сам прочитаю и разберусь. Но куда смотреть сейчас просто не могу сообразить.

Заранее спасибо!
Тему не стал новую создавать, показалось что похожи.
[moder]А зря. Почитайте Правила форума, там как раз про это написано.
Эта тема закрыта, создавайте свою.
К сообщению приложен файл: 9331270.xlsx (11.1 Kb)


Я готов казнить троих ради жизней четверых

Сообщение отредактировал _Boroda_ - Четверг, 06.08.2015, 11:57
 
Ответить
СообщениеЗдравствуйте уважаемые форумчане, столкнулся с такой проблемой. Сразу оговорюсь что пользователь начинающий (exel) потому сильно не пинайте :)
С копировал из одной программы данные с цифрами в ексель (числа) и они почему-то стали отображаться по-разному все. Какие-то смещены вправо а какие-то выравниваются с левой стороны, я так понимаю что это из-за разности форматов. Те, что справа это воспринимается как текст а те что слева как числа.
Вот и вопрос возник, как все перевести в формат числа?
Менял формат выделенных ячеек, ничего не получилось. Одни товарищи сказали что нужно руками все переписывать, но я сомневаюсь в этом. Какое-то совсем дикое решение на мой взгляд.
Потому что может быть и 1000 ячеек и 5000. Руками не вариант. Уверен что есть решение, и оно весьма просто должно быть.
***
Я не прошу дать мне готовый ответ, буду рад ссылке, что читать и куда смотреть. Сам прочитаю и разберусь. Но куда смотреть сейчас просто не могу сообразить.

Заранее спасибо!
Тему не стал новую создавать, показалось что похожи.
[moder]А зря. Почитайте Правила форума, там как раз про это написано.
Эта тема закрыта, создавайте свою.

Автор - Gekko
Дата добавления - 06.08.2015 в 11:38
ekuritsyn Дата: Четверг, 06.08.2015, 12:30 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
К сожалению, предыдущую тему закрыли, а вопрос до конца не решен...
История начинается здесь:
http://www.excelworld.ru/forum/2-18638-1

Я воспользовался формулой массива от Nic70y.

Во вложении несколько сложных случаев. Можно их как-то учесть в формуле, чтобы она сделала правильную выборку?
[moder]Ну да, там я поторопился, пожалуй. Открыл ту тему и перенес туда этот пост
К сообщению приложен файл: _2.xlsx (10.4 Kb)


Сообщение отредактировал _Boroda_ - Четверг, 06.08.2015, 12:41
 
Ответить
СообщениеК сожалению, предыдущую тему закрыли, а вопрос до конца не решен...
История начинается здесь:
http://www.excelworld.ru/forum/2-18638-1

Я воспользовался формулой массива от Nic70y.

Во вложении несколько сложных случаев. Можно их как-то учесть в формуле, чтобы она сделала правильную выборку?
[moder]Ну да, там я поторопился, пожалуй. Открыл ту тему и перенес туда этот пост

Автор - ekuritsyn
Дата добавления - 06.08.2015 в 12:30
Nic70y Дата: Четверг, 06.08.2015, 23:06 | Сообщение № 10
Группа: Друзья
Ранг: Экселист
Сообщений: 9131
Репутация: 2415 ±
Замечаний: 0% ±

Excel 2010
воспользовался формулой массива от Nic70y
вот и зря, ему нельзя верить.
вариант с UDF
от krosav4ig почти работает
К сообщению приложен файл: 7403382-2-.xls (38.5 Kb)


ЮMoney 41001841029809
 
Ответить
Сообщение
воспользовался формулой массива от Nic70y
вот и зря, ему нельзя верить.
вариант с UDF
от krosav4ig почти работает

Автор - Nic70y
Дата добавления - 06.08.2015 в 23:06
krosav4ig Дата: Пятница, 07.08.2015, 03:00 | Сообщение № 11
Группа: Друзья
Ранг: Старожил
Сообщений: 2348
Репутация: 997 ±
Замечаний: 0% ±

Excel 2007,2010,2013
поправочка...
[vba]
Код
Function Phone(str$)
     With CreateObject("VBScript.RegExp")
         .Global = True
         .Pattern = "(((\+7|8)(|-| )?)?((\(|-| )?\d{3}(\)|-| )?){2}((-| )?\d{2}){2})|\d{2}((-| )?\d{2}){2}"
         If .Test(str) Then
             Dim Item As Object
             For Each Item In .Execute(str)
                 Phone = Phone & IIf(Phone > "", vbLf, "") & Trim(Item)
             Next
         End If
     End With
End Function
[/vba]
К сообщению приложен файл: 7403382-3-.xls (41.5 Kb)


email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
 
Ответить
Сообщениепоправочка...
[vba]
Код
Function Phone(str$)
     With CreateObject("VBScript.RegExp")
         .Global = True
         .Pattern = "(((\+7|8)(|-| )?)?((\(|-| )?\d{3}(\)|-| )?){2}((-| )?\d{2}){2})|\d{2}((-| )?\d{2}){2}"
         If .Test(str) Then
             Dim Item As Object
             For Each Item In .Execute(str)
                 Phone = Phone & IIf(Phone > "", vbLf, "") & Trim(Item)
             Next
         End If
     End With
End Function
[/vba]

Автор - krosav4ig
Дата добавления - 07.08.2015 в 03:00
ekuritsyn Дата: Понедельник, 10.08.2015, 13:28 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
krosav4ig, Спасибо за помощь!!!

Nic70y, тебе тоже спасибо! ;)
 
Ответить
Сообщениеkrosav4ig, Спасибо за помощь!!!

Nic70y, тебе тоже спасибо! ;)

Автор - ekuritsyn
Дата добавления - 10.08.2015 в 13:28
  • Страница 1 из 1
  • 1
Поиск:

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