Как привести телефонные номера к единому формату?
В MS Excel предусмотрено множество форматов, в которых можно представить свои данные. Но что делать, когда ни один из стандартных форматов не подходит? Ответ прост — создайте свой формат.
Как это сделать?
В качестве примера, возьмем номер телефона в виде «9264177054», нам нужно добавить код страны и преобразовать этот номер к виду +7 (926) 417-70-54. Как это сделать? Очень просто!
Для того чтобы создать свой формат данных, переведите курсор в ячейку, к которой вы хотите применить пользовательский формат, и вызовите диалоговое окно Формат ячеек.
Для вызова этого окна используйте сочетание клавиш «Ctrl+1» или нажмите правую клавишу мышки и в контекстном меню выберите пункт «Формат ячеек. «:
В открывшемся окне, во вкладке «Число» выберите пункт «все форматы». В появившееся справа поле Тип: введите маску нужного Вам формата. В нашем примере это будет: +7″ «(#)» «000-00-00. Обратите внимание, что в окошке «Образец» данные поменяли свой вид. Если вас устраивает такой вид данных нажмите «Оk». Данные в указанной ячейке примут соответствующий вид +7 (926) 417-70-54.
Теперь ваш формат сохранен в Excel и его можно использовать для форматирования. Выделите ячейки, к которым надо применить пользовательский формат, и вызовите диалоговое окно Формат ячеек. В списке форматов найдите свой (он будет последним), выделите его и нажмите «Оk».
Как это работает?
На самом деле все очень просто. Excel использует несколько спецсимволов в масках форматов:
0 (ноль) — один обязательный разряд, т.е. это место в маске формата будет заполнено цифрой из числа, которое пользователь введет в ячейку. Если для этого разряда нет числа, то будет выведен ноль. Например, если к числу 23 применить маску 000, то получится 023, а если к дробному числу 23,032016 применить маску 00,000 — получится 23,032.
# (решетка) — один необязательный разряд, т.е. если для него нет числа, то ничего не выводится.
[] — в квадратных скобках, перед маской формата, указывает цвет шрифта. Разрешено использовать следующие цвета: черный, белый, красный, синий, зеленый, жёлтый, голубой.
«» — любой пользовательский текст (млн, уп., м и тому подобные) или любые символы (например, пробелы) — надо обязательно заключать в кавычки.
Как убрать 8 в номере телефона в эксель
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Как вычленить номера телефонов в excel
Добрый вечер. Имеется столбец таблицы excel, в ячейках которого лежит куча слов, среди которых лежит номер телефона в формате: +7 (xxx) xxx xx xx. Телефон может быть в любом месте ячейки. Как удалить из ячейки все, кроме этого телефона?
Отслеживать
задан 17 окт 2017 в 20:32
Roman Ryzh Roman Ryzh
61 1 1 серебряный знак 9 9 бронзовых знаков
В гуглодоках — регуляркой, в экселе лучше макросов пока не придумывается.
17 окт 2017 в 20:39
2 ответа 2
Сортировка: Сброс на вариант по умолчанию
Maxim Yenaleyv: . автозаменой. Можно заменить *+7 ( на +7
Еще проще, без доп. столбца с формулой.
- Левую часть отсекаем, как и написано, автозаменой.
- Выделяем диапазон, вкладка Данные-Текст_по_столбцам-Фиксированной_ширины-Далее-(задать ширину 18 символов)-Далее-(указать, с какой ячейки начать вставку)-Готово. Правая часть перенесена в отдельный столбец, его можно удалить.
Чтобы не искажать исходные данные, для поиска создать шаблон, применяя подстановочные знаки: знак вопроса(?) — один любой символ, или звездочку (*) — любые символы в количестве >=0
=ПСТР(A1;ПОИСК("+7 (. ) . ?? ??";A1);18) Шаблон можно сократить, если есть уверенность в том, что сокращенный не будет подходить к другим фрагментам текста. Вполне может оказаться достаточным «+7 (. )».
Шаблон можно оперативно менять, если записать его в в отдельную ячейку, в формуле поставить ссылку.
Если обрабатывается диапазон ячеек, в текстах которых номер телефона может отсутствовать:
=ЕСЛИОШИБКА(ПСТР(A1;ПОИСК("+7 (. )";A1);18);"") =ЕСЛИ(ЕЧИСЛО(ПОИСК("+7 (. )";A1));ПСТР(A1;ПОИСК("+7 (. )";A1);18);"") Как заменить в экселе первую цифру во всем столбце
Есть список телефонов в таблице эксель. Все телефоны начинаются с цифры 8, а требуется заменить во всех номерах чтобы была 7, возможно ли это сделать быстрым способом?
Лучший ответ
Если в таблице номера телефонов в числовом формате, то можно сделать почти, как предложил А. Давыдов, но проще, без функций и дополнительного столбца.
В любой ячейке, временно, пишете 10000000000. Копируете эту ячейку. Затем выделяете колонку с телефонами, правой кнопкой вызываете окно специальной вставки, ставите отметку на операции вычесть. Ок.
Ячейку с 10000000000 можно очистить.
Остальные ответы
Необходимо вставить функцию в новую клетку на против одного из номера, потом скопировать и вставить на против каждого номера. Функция такая [клетка с номером] — 10000000000. Таким образом мы получаем в начале семерку.
Пусть A столбец с телефонами, в соседнем столбце в B1 вставляем формулу
=»+7″ & ПСТР (A1;2;100)
(я считаю это лучше чем «+7» & ПРАВСИМВ (ДЛСТР (A1)-1) — вычислений меньше) ,
ессно если нужна просто 7 — плюс в формуле у «+7» не пишем =)
Берём за правый нижний угол ячейки и «растягиваем» формулу до конца данных
Выделяем столбец B
Правая кнопка на выделении, копировать
Выбираем столбец B
Правая кнопка вставить значения. Внимательно — именно значения в Excel 2010 это выглядит как значок 123
Всё. Столбец B у вас заменён вместо формул теперь их значения, если всё устраивает — выделяете столбец A и удаляете. Столбец B становится столбцом A
Если проблемы и нужно подробнее — пишите на почту
вот формулы подобрал недавно, кому надо поэксперементируйте
=ЕСЛИ (ДЛСТР (C1)<>11;ЗАМЕНИТЬ (C1;1;100;»0″);ЕСЛИ (ЛЕВСИМВ (C1)<>8;ЗАМЕНИТЬ (C1;1;1;»8″)))
и посложнее
=ЕСЛИ (ДЛСТР (C1)=11;ЗАМЕНИТЬ (C1;1;1;»8″);ЕСЛИ (ДЛСТР (C1)=12;ЗАМЕНИТЬ (C1;1;2;»8″);ЕСЛИ (ДЛСТР (C1)=10;ЗАМЕНИТЬ (C1;1;1;»89″);ЕСЛИ (ДЛСТР (C1)12;;)))))
тут столбец С1 меняете на свой.