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

Вход

Регистрация

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

 

= Мир MS Excel/формат номеров телефона - Мир MS Excel

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

Excel 2016
Помогите решить задачу.
Есть таблица (более 10к строк) с данными. Один из столбцов - произвольная информация, нужно его разобрать.

Содержимое ячеек разнообразное: Адрес, Телефон, инд.номер, прочие пометки

Хорошо бы разделить все по столбцам, особенно интересует инд.номер и телефон.

Пример приложил
К сообщению приложен файл: 8707177.xlsx (10.1 Kb)
 
Ответить
СообщениеПомогите решить задачу.
Есть таблица (более 10к строк) с данными. Один из столбцов - произвольная информация, нужно его разобрать.

Содержимое ячеек разнообразное: Адрес, Телефон, инд.номер, прочие пометки

Хорошо бы разделить все по столбцам, особенно интересует инд.номер и телефон.

Пример приложил
Автор - yuriknsk
Дата добавления - 03.12.2018 в 09:42
китин Дата: Понедельник, 03.12.2018, 09:51 | Сообщение № 2
Группа: Модераторы
Ранг: Экселист
Сообщений: 7014
Репутация: 1073 ±
Замечаний: 0% ±

Excel 2007;2010;2016
интересно а где вы в фразе
Цитата
"с ребенок 12 лет Иван"
увидели дату
Цитата
05.07.2005
?

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
Сообщениеинтересно а где вы в фразе
Цитата
"с ребенок 12 лет Иван"
увидели дату
Цитата
05.07.2005
?
Автор - китин
Дата добавления - 03.12.2018 в 09:51
Pelena Дата: Понедельник, 03.12.2018, 10:07 | Сообщение № 3
Группа: Админы
Ранг: Местный житель
Сообщений: 19174
Репутация: 4413 ±
Замечаний: ±

Excel 365 & Mac Excel
Почитайте про Мгновенное заполнение. Для вашей задачи самый раз

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеПочитайте про Мгновенное заполнение. Для вашей задачи самый раз
Автор - Pelena
Дата добавления - 03.12.2018 в 10:07
китин Дата: Понедельник, 03.12.2018, 10:12 | Сообщение № 4
Группа: Модераторы
Ранг: Экселист
Сообщений: 7014
Репутация: 1073 ±
Замечаний: 0% ±

Excel 2007;2010;2016
чисто по примеру
для №
Код
=ЕСЛИОШИБКА(ПСТР(C3;ПОИСК("№";C3)+1;ПОИСК(" ";C3)-2);"")

для дата( формула массива)
Код
=ЕСЛИОШИБКА(ЕСЛИ(ДЛСТР(ПСТР(C3;ПОИСКПОЗ(1=1;ЕЧИСЛО(--ПСТР(C3;СТРОКА($1:$50);10));0);10))<>10;"";ПСТР(C3;ПОИСКПОЗ(1=1;ЕЧИСЛО(--ПСТР(C3;СТРОКА($1:$50);10));0);10));"")

для телефона
Код
=ЕСЛИОШИБКА(ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(C3;"-";"");"+7";8);ПОИСК(" 8";ПОДСТАВИТЬ(ПОДСТАВИТЬ(C3;"-";"");"+7";8))+1;11);"")
К сообщению приложен файл: yuriknsk.xlsx (11.6 Kb)

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
Сообщениечисто по примеру
для №
Код
=ЕСЛИОШИБКА(ПСТР(C3;ПОИСК("№";C3)+1;ПОИСК(" ";C3)-2);"")

для дата( формула массива)
Код
=ЕСЛИОШИБКА(ЕСЛИ(ДЛСТР(ПСТР(C3;ПОИСКПОЗ(1=1;ЕЧИСЛО(--ПСТР(C3;СТРОКА($1:$50);10));0);10))<>10;"";ПСТР(C3;ПОИСКПОЗ(1=1;ЕЧИСЛО(--ПСТР(C3;СТРОКА($1:$50);10));0);10));"")

для телефона
Код
=ЕСЛИОШИБКА(ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(C3;"-";"");"+7";8);ПОИСК(" 8";ПОДСТАВИТЬ(ПОДСТАВИТЬ(C3;"-";"");"+7";8))+1;11);"")
Автор - китин
Дата добавления - 03.12.2018 в 10:12
_Boroda_ Дата: Понедельник, 03.12.2018, 10:16 | Сообщение № 5
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Такой вариант
Номера
Код
=ЕСЛИОШИБКА(ПСТР(C3;ПОИСК("№";C3)+1;ПОИСК(" ";C3;ПОИСК("№";C3))-2);"")

Даты
Код
=ЕСЛИОШИБКА(ПСТР(C3;ПОИСК(" ??.??.???? ";" "&C3&" ");10);"")

Телефоны
Код
=ТЕКСТ(ПРАВБ(МАКС(ИНДЕКС(--ТЕКСТ(ПСТР(" "&ПОДСТАВИТЬ(СЖПРОБЕЛЫ(C3);"-";)&" ";СТРОКА($1:$99);13);"[>9999999999]0;\0;\0;\0");));10);"\8#;;")


Всё-всё не найдет, нужна будет последующая визуальная проверка
К сообщению приложен файл: 8707177_1.xlsx (12.1 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТакой вариант
Номера
Код
=ЕСЛИОШИБКА(ПСТР(C3;ПОИСК("№";C3)+1;ПОИСК(" ";C3;ПОИСК("№";C3))-2);"")

Даты
Код
=ЕСЛИОШИБКА(ПСТР(C3;ПОИСК(" ??.??.???? ";" "&C3&" ");10);"")

Телефоны
Код
=ТЕКСТ(ПРАВБ(МАКС(ИНДЕКС(--ТЕКСТ(ПСТР(" "&ПОДСТАВИТЬ(СЖПРОБЕЛЫ(C3);"-";)&" ";СТРОКА($1:$99);13);"[>9999999999]0;\0;\0;\0");));10);"\8#;;")


Всё-всё не найдет, нужна будет последующая визуальная проверка
Автор - _Boroda_
Дата добавления - 03.12.2018 в 10:16
yuriknsk Дата: Понедельник, 03.12.2018, 11:11 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 40
Репутация: 1 ±
Замечаний: 20% ±

Excel 2016
китин, да, данные могут быть разные, могут быть и совсем пустые ячейки
 
Ответить
Сообщениекитин, да, данные могут быть разные, могут быть и совсем пустые ячейки
Автор - yuriknsk
Дата добавления - 03.12.2018 в 11:11
yuriknsk Дата: Понедельник, 03.12.2018, 11:36 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 40
Репутация: 1 ±
Замечаний: 20% ±

Excel 2016
Предложенные варианты изучаю, спасибо.

Есть ли способ такой хотелки: если текст соответствует маске "?-???-???-??-??", то "удалить между цифрами тире". Далее проверяем другие маски, например "+?-???-???????"
аналогично с датой
 
Ответить
СообщениеПредложенные варианты изучаю, спасибо.

Есть ли способ такой хотелки: если текст соответствует маске "?-???-???-??-??", то "удалить между цифрами тире". Далее проверяем другие маски, например "+?-???-???????"
аналогично с датой
Автор - yuriknsk
Дата добавления - 03.12.2018 в 11:36
_Boroda_ Дата: Понедельник, 03.12.2018, 11:50 | Сообщение № 8
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Цитата yuriknsk, 03.12.2018 в 11:36, в сообщении № 7 ()
Есть ли способ такой хотелки
Есть. Но зачем? Чем-то не устраивают предложенные варианты?

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Цитата yuriknsk, 03.12.2018 в 11:36, в сообщении № 7 ()
Есть ли способ такой хотелки
Есть. Но зачем? Чем-то не устраивают предложенные варианты?
Автор - _Boroda_
Дата добавления - 03.12.2018 в 11:50
yuriknsk Дата: Понедельник, 03.12.2018, 13:38 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 40
Репутация: 1 ±
Замечаний: 20% ±

Excel 2016
_Boroda_, скажите, что такое СТРОКА($1:$99) в вашей третьей формуле? У нас более 10тыс. строк, туда нужно вписать "СТРОКА($1:$10000)"?

Цитата _Boroda_, 03.12.2018 в 10:16, в сообщении № 5 ()
=ТЕКСТ(ПРАВБ(МАКС(ИНДЕКС(--ТЕКСТ(ПСТР(" "&ПОДСТАВИТЬ(СЖПРОБЕЛЫ(C3);"-";)&" ";СТРОКА($1:$99);13);"[>9999999999]0;\0;\0;\0");));10);"\8#;;")


Сообщение отредактировал yuriknsk - Понедельник, 03.12.2018, 13:40
 
Ответить
Сообщение_Boroda_, скажите, что такое СТРОКА($1:$99) в вашей третьей формуле? У нас более 10тыс. строк, туда нужно вписать "СТРОКА($1:$10000)"?

Цитата _Boroda_, 03.12.2018 в 10:16, в сообщении № 5 ()
=ТЕКСТ(ПРАВБ(МАКС(ИНДЕКС(--ТЕКСТ(ПСТР(" "&ПОДСТАВИТЬ(СЖПРОБЕЛЫ(C3);"-";)&" ";СТРОКА($1:$99);13);"[>9999999999]0;\0;\0;\0");));10);"\8#;;")
Автор - yuriknsk
Дата добавления - 03.12.2018 в 13:38
_Boroda_ Дата: Понедельник, 03.12.2018, 13:50 | Сообщение № 10
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Нет, не нужно. Это массив номеров строк с первой по 99-ю.
дает массив чисел - 1,2,3,4,...,98,99
Никак не зависит от количества строк обрабатываемого массива.
Увеличивать нужно только в случае, если длина текста в какой-либо из проверяемых ячеек может быть больше 99 символов. Но, если увеличивать будуте, то без фанатизма

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеНет, не нужно. Это массив номеров строк с первой по 99-ю.
дает массив чисел - 1,2,3,4,...,98,99
Никак не зависит от количества строк обрабатываемого массива.
Увеличивать нужно только в случае, если длина текста в какой-либо из проверяемых ячеек может быть больше 99 символов. Но, если увеличивать будуте, то без фанатизма
Автор - _Boroda_
Дата добавления - 03.12.2018 в 13:50
yuriknsk Дата: Понедельник, 03.12.2018, 13:51 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 40
Репутация: 1 ±
Замечаний: 20% ±

Excel 2016
Цитата китин, 03.12.2018 в 09:51, в сообщении № 2 ()
интересно а где вы в фразе
"с ребенок 12 лет Иван"
увидели дату?

очепятка :D
 
Ответить
Сообщение
Цитата китин, 03.12.2018 в 09:51, в сообщении № 2 ()
интересно а где вы в фразе
"с ребенок 12 лет Иван"
увидели дату?

очепятка :D
Автор - yuriknsk
Дата добавления - 03.12.2018 в 13:51
yuriknsk Дата: Понедельник, 03.12.2018, 14:05 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 40
Репутация: 1 ±
Замечаний: 20% ±

Excel 2016
_Boroda_, неправильно обрабатывает такие данные: "№5700 01.01.1950 8-913-000-11-11" формула возвращает номер телефона в виде "8891300011"


Сообщение отредактировал yuriknsk - Понедельник, 03.12.2018, 14:05
 
Ответить
Сообщение_Boroda_, неправильно обрабатывает такие данные: "№5700 01.01.1950 8-913-000-11-11" формула возвращает номер телефона в виде "8891300011"
Автор - yuriknsk
Дата добавления - 03.12.2018 в 14:05
_Boroda_ Дата: Понедельник, 03.12.2018, 14:14 | Сообщение № 13
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Так?
Код
=ТЕКСТ(ПРАВБ(МАКС(ИНДЕКС(--ТЕКСТ(ПСТР("!"&ПОДСТАВИТЬ(ПОДСТАВИТЬ(C3;" ";"!");"-";)&"!";СТРОКА($1:$99);11);"[>9999999999]0;\0;\0;\0");));10);"\8#;;")
К сообщению приложен файл: 8707177_2.xlsx (12.2 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТак?
Код
=ТЕКСТ(ПРАВБ(МАКС(ИНДЕКС(--ТЕКСТ(ПСТР("!"&ПОДСТАВИТЬ(ПОДСТАВИТЬ(C3;" ";"!");"-";)&"!";СТРОКА($1:$99);11);"[>9999999999]0;\0;\0;\0");));10);"\8#;;")
Автор - _Boroda_
Дата добавления - 03.12.2018 в 14:14
Мир MS Excel » Вопросы и решения » Вопросы по Excel » формат номеров телефона (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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