Как в эксель преобразовать номера телефонов в нужный формат

Обновлено: 06.07.2024

Не получается обновить формат ячеек. Присутствуют номера телефонов в следующем формате:

Нужно привести их к виду :

После добавления формата cледующим образом:

Обновляется только первая ячейка, для обновления формата по остальным-нужно выбирать каждую ячейку и нажимать enter.

Подскажите, как обновить все ячейки сразу? Пробовал выбрать ячейку в нужном формате и применить ко всем остальным через cntrl+alt+v , однако вставляется не формат ячейки, а она на место всех остальных.

Перед изменением формата выделить диапазон (или весь столбец), а не ячейку. Кстати, значение в ячейке не меняется, меняется только его визуальное представление Правый клик на названии столбца - столбец выделен. Задаем формат

Формат ячеек, который задается, работает с числовыми данными. А в ячейках, похоже, числа, представленные в текстовом формате. Это могло случиться по разным причинам: при загрузке со сторонних программ; если формат столбца перед вводом данных задан как Текстовый; если данные получены формулой, которая возвращает текст.

Следует заметить, что формат ячейки и формат значения - не одно и то же.

В левом верхнем углу ячейки зеленый треугольник информирует о том, что число сохранено как текст. Эта индикация может быть отключена в параметрах.

Расширить столбец; если данные прижаты к левой стороне ячеек - текст. Но это не показатель, если в ячейках уже задано горизонтальное выравнивание.

Формулой =ЕТЕКСТ(ссылка) или =ЕЧИСЛО(ссылка)

Проверить формат ячеек. Изменить, если Текстовый. Если данные получены формулой, нужно что-то менять в формуле.

Выделить диапазон с данными, вкладка Данные-Текст по столбцам-ОК. Текстовые числовые значения преобразуются в числа. После этого можно задать формат ячеек для всего диапазона.

Привести данные к нужному формату (формат данных, не ячейки!) можно в другом столбце формулой:

Возможно форматирование телефонных номеров в Excel. Это небольшая, но очень инновационная функция в Excel. В каждой стране свой код страны и формат написания телефонных номеров. В некоторых номерах номера телефонов разделены пробелами, в некоторых разрешены скобки для кода страны, в некоторых разница в длине номера, а в некоторых ставятся пробелы после кода страны, а остаток номера телефона следует последовательности.

Форматирование телефонных номеров может быть выполнено путем доступа к ячейкам « Формат» в главном меню в раскрывающемся списке « Формат ».

Как отформатировать телефонные номера в Excel?

Форматирование телефонных номеров в Excel очень просто и удобно. Давайте разберемся с разными методами на нескольких примерах.

Давайте рассмотрим несколько случайных телефонных номеров для их форматирования. У нас есть номера телефонов длиной 10 символов, как показано ниже.

Теперь для форматирования телефонных номеров в Excel сначала выберите телефонные номера, затем перейдите в Главное меню и нажмите Формат, как показано ниже. Как только мы нажмем на «Формат», мы получим выпадающий список, в котором мы выберем « Формат ячеек», который расположен внизу списка, как показано на скриншоте ниже.

Как только мы нажмем на «Форматировать ячейки», мы получим диалоговое окно, которое содержит различные функции, связанные с форматированием ячейки, такие как «Число», «Выравнивание», «Шрифт», «Граница», «Заливка» и «Защита».

Поскольку наша задача состоит в том, чтобы отформатировать телефонные номера, мы перейдем на вкладку « Число » и выберем « Пользовательский параметр», как показано на скриншоте ниже.

Как мы видим и проверяем, прокручивая вверх и вниз поле Тип, нет предустановленного формата телефонных номеров. Поэтому для настройки телефонных номеров удалите уже заполненные номера или текст и введите свой собственный формат. Как здесь, мы удалили слово General из раздела type и добавили « +91 » в качестве кода страны Индии, затем «-» и затем 10 нулей, которые являются стандартными 10-значными числами.

Как только мы это сделаем, мы увидим, что в одну из выбранных ячеек с номером телефона мы также добавим «+ 91-» чуть выше к отформатированной структуре в качестве образца. Затем нажмите ОК, как показано ниже.

Как только мы нажмем кнопку ОК, мы увидим, что выбранные ячейки будут отформатированы в соответствии с типом, который мы исправили в поле Формат ячейки, как показано на снимке экрана ниже.

После того, как мы отформатировали несколько тестовых телефонных номеров, мы можем отформатировать любую ячейку, содержащую телефонные номера, в том же формате.

Существует еще один способ форматирования телефонных номеров в Excel. Давайте рассмотрим те же наборы образца телефонного номера, которые мы видели в примере-1. Теперь для форматирования телефонных номеров сначала выберите данные, затем сделайте прямо на них. И выберите параметры форматирования ячеек, как показано на скриншоте ниже.

После этого мы получим диалоговое окно «Формат ячеек», как показано ниже.

Здесь мы введем новый формат для номера телефона. Здесь мы разделили код страны с помощью скобок и разделили половину чисел на «-» на 3 части и нажмите «ОК», как показано ниже.

Как мы видим ниже, формат, который мы установили в ячейках формата для телефонных номеров, теперь отражается на выбранных данных.

После того, как наш формат установлен, мы можем использовать его где угодно, когда видим, что телефонные номера должны быть отформатированы.

Чтобы проверить, виден ли наш недавно созданный формат телефонных номеров в ячейках формата или нет, сделайте это в любом месте на листе и выберите параметры Формат ячеек в нижней части меню. После этого появится то же самое окно ячеек формата. Перейдите на вкладку Number и выберите Custom option. Прокрутите вниз, чтобы найти наш недавно созданный формат.

Как мы можем видеть на скриншоте ниже, мы обвели форматы телефонных номеров, которые мы создали в примере 1 и примере 2.

Таким образом, мы можем создавать различные типы форматов телефонных номеров и использовать их в соответствии с нашими требованиями.

Плюсы формата телефонных номеров в Excel

Для данных большого телефонного номера становится очень легко понять код страны и фактический номер телефона, если данные правильно структурированы. Мы можем классифицировать и сортировать телефонные номера разных стран по их коду.

То, что нужно запомнить

Изменение формата номера телефона

Не получается обновить формат ячеек. Присутствуют номера телефонов в следующем формате:

Нужно привести их к виду :

После добавления формата cледующим образом:

1 ответ 1

Формулой =ЕТЕКСТ(ссылка) или =ЕЧИСЛО(ссылка)

Привести данные к нужному формату (формат данных, не ячейки!) можно в другом столбце формулой:

Как привести телефонные номера к единому формату?

Как это сделать?

Для того чтобы создать свой формат данных, переведите курсор в ячейку, к которой вы хотите применить пользовательский формат, и вызовите диалоговое окно Формат ячеек.

Как это работает?

На самом деле все очень просто. Excel использует несколько спецсимволов в масках форматов:

Как в Excel номера телефонов привести к единому формату?

В загруженной в Excel базе столбец записан разными способами:

какой формулой форматировать в Эксель номер телефона, чтобы преобразовать к единому формату в 11 знаков начиная с цифры 7

Такие возможности описаны в большом количестве в сети. Главное, описать правильно, что хотите сделать со значением ячейки

Чтобы обработать телефонный номер надо ввести дополнительный столбец в который записать формулу для преобразования номеров телефонов в единый формат можно просто добавить цифру 7 к последним 10 цифрам телефона.

для ячейки A2 формула имеет вид:

однако очень часто в поле телефонного номера встречаются комментарии, в этом случае

Если слово пишется в графе единицы измерения, в этом случае формула будет выглядеть так:

Если требуется написать в тексте число и количество штук через пробел, в этом случае формула примет вид:

Обе формулы рабочие (можно копировать в свою таблицу, с заменой ячейки A1 на ячейку таблицы, в которой записано целое число определяющее количество штук)

Если слово надо писать в отдельную графу, то числа из ячейки A1 формула примет вид:

Для использования в тексте потребуется писать число, а потом слово, формула будет выглядеть так:

Эту формулу можно копировать отсюда в свою таблицу, но необходимо заменить A1 на нужную ячейку.

В формуле обозначения ячеек записываются латинскими буквами. Теперь выделяете ячейку F7, подводите курсор к правому нижнему углу ячейки (там черный квадратик). Тогда курсор из обычного вида перейдёт в вид прямой чёрный крестик (+). Теперь, нажимаете левую клавишу мыши и удерживая её нажатой, протягиваете курсор по всему столбцу F, включая ячейку F135, и после этого отпускаете клавишу мышки. Тем самым Вы скопируете формулу на весь столбец F, точнее на диапазон ячеек F7-F135. В тех строках, в которых разность между значениями в текущей и вышестоящей строке больше 1, появятся звёздочки (*).

Чтобы посчитать сумму значений ячеек обычно используется функция СУММ, а в качестве аргументов пишутся либо значения, либо диапазоны ячеек либо сами ячейки.

Если требуется вычислить сумму значений ячеек разных диапазонов, то эти диапазоны перечисляются как аргументы функции сумм.

вычислит сумму чисел, находящихся в ячейках из диапазона A3:A8 и диапазона C3:H3

Если диапазоны чисел находятся на разных листах одной книги, то перед ссылкой на диапазон надо указать наименование листа с восклицательным знаком

=СУММ( A3:A8 ; C3:H3 ; Лист2!A1:A6 )

если диапазоны суммирования находятся в разных книгах, то придется указать и название книги в квадратных скобках пример:

=СУММ(A3:A8 ; C3:H3 ; [Книга3]Лист2!A1:A6 )

всего в функции СУММ можно указывать от 1 до 255 аргументов.

Имя файла эксель выдает в квадратных скобках, поэтому в наименовании файла лучше не использовать квадратные скобки (они преобразуются в круглые, а это может вызвать ошибку).

Если таблица не была сохранена функция выдаст пустую строку.

Для получения имени файла надо сначала убедиться, что таблица была сохранена в файл, а затем взять все символы внутри квадратных скобок, функцией ПСТР.

Вот формула для записи назваия файла в ячейку Excel:

Формулу можно скопировать отсюда в любую ячейку таблицы.

Как в Excel изменить значение столбца «Телефон» с «244-44-44 это номер Кати» на «244-44-44»?

У нас ввели учет клиентов в Excel когда-то. Единого стандарта не было, как и контроля за введенными данными. Сотрудники в столбце номера телефонов ввели все что угодно, от любых знаков, цифр и текста.

Только не забудьте предварительно выделить нужный диапазон ячеек, иначе замена произойдет на всем листе!
Удаление пробелов

Если из текста нужно удалить вообще все пробелы (например они стоят как тысячные разделители внутри больших чисел), то можно использовать ту же замену: нажать Ctrl+H, в первую строку ввести пробел, во вторую ничего не вводить и нажать кнопку Заменить все (Replace All).

Однако, часто возникает ситуация, когда удалить надо не все подряд пробелы, а только лишние – иначе все слова слипнутся друг с другом. В арсенале Excel есть специальная функция для этого – СЖПРОБЕЛЫ (TRIM) из категории Текстовые. Она удаляет из текста все пробелы, кроме одиночных пробелов между словами, т.е. мы получим на выходе как раз то, что нужно:

Удаление непечатаемых символов

Вариантов решения два:

Аккуратно выделить мышью эти спецсимволы в тексте, скопировать их (Ctrl+C) и вставить (Ctrl+V) в первую строку в окне замены (Ctrl+H). Затем нажать кнопку Заменить все (Replace All) для удаления.
Использовать функцию ПЕЧСИМВ (CLEAN). Эта функция работает аналогично функции СЖПРОБЕЛЫ, но удаляет из текста не пробелы, а непечатаемые знаки. К сожалению, она тоже способна справится не со всеми спецсимволами, но большинство из них с ее помощью можно убрать.

Замену одних символов на другие можно реализовать и с помощью формул. Для этого в категории Текстовые в Excel есть функция ПОДСТАВИТЬ (SUBSTITUTE). У нее три обязательных аргумента:

Текст в котором производим замену
Старый текст – тот, который заменяем
Новый текст – тот, на который заменяем

Удаление апострофов в начале ячеек

Sub Apostrophe_Remove()
For Each cell In Selection
If Not cell.HasFormula Then
v = cell.Value
cell.Clear
cell.Formula = v
End If
Next
End Sub

Теперь, если выделить на листе диапазон и запустить наш макрос (Alt+F8 или вкладка Разработчик – кнопка Макросы), то апострофы перед содержимым выделенных ячеек исчезнут.
Английские буквы вместо русских

Теперь, если выделить на листе диапазон и запустить наш макрос (Alt+F8 или вкладка Разработчик – кнопка Макросы), то все английские буквы, найденные в выделенных ячейках, будут заменены на равноценные им русские. Только будьте осторожны, чтобы не заменить случайно нужную вам латиницу 🙂

Предположим, что пользователи заполняют таблицу содержащую список телефонных номеров для звонков клиентам. Пусть важно быстро определить те из них, у которых есть московский номер.

Телефонные номера обычно отображают в формате (495) 111-1111. Для этого нужно установить формат ячеек: Дополнительный/ Номер телефона (нажав CTRL+1 ). В этом случае ввод в ячейку телефонного номера можно производить, опустив скобки кода региона и дефис, т.е. 4951111111.

Создадим правило Условного форматирования для выделения номеров принадлежащих абонентам г.Москва (телефоны с номерами начинающихся с кодов 495 и 499):

выделите ячейки, в которые будут вводиться телефонные номера ( A2:A10 );
вызовите инструмент Условное форматирование ( Главная/ Стили/ Условное форматирование/ Управление правилами ). Откроется окно Диспетчер правил условного форматирования ;
нажмите Создать правило ;
выберите Использовать формулу для определения форматируемых ячеек;
в поле « Форматировать значения, для которых следующая формула является истинной » введите =ИЛИ(ЛЕВСИМВ(A2;3)="499"; ЛЕВСИМВ(A2;3)="495") – если хотим, чтобы выделялись телефоны с номерами начинающихся с кодов 495 и 499;

выберите требуемый формат, например, красный цвет шрифта.

Вместо использования функции ИЛИ() , можно создать два правила соответствующих разным кодам с формулами =ЛЕВСИМВ(A1;3)="499" и =ЛЕВСИМВ(A1;3)="495" .

В результате получим.

У данного примера имеется существенный недостаток: при вводе ошибочного номера, например, (495) 111, т.е. ввели неполный номер, Условное форматирование не покажет ошибки. для того, чтобы номер телефона соответствовал определенному шаблону используйте идеи из статьи Ввод данных по шаблону .

СОВЕТ : Чтобы найти все ячейки на листе, к которым применены правила Условного форматирования необходимо:

Как отформатировать телефонные номера в Excel?

Вы можете скачать этот шаблон номера телефона формата Excel здесь - Формат номера телефона формата Excel

Форматирование телефонных номеров в Excel - Пример № 1

Форматирование телефонных номеров в Excel - пример № 2

После этого мы получим диалоговое окно «Формат ячеек», как показано ниже.

Теперь перейдите к опции Custom вниз на вкладке Number . Там, где мы найдем поле типа, ниже находится список всех настроенных типов номеров, но не формат номера телефона. Для этого удалите / удалите текст или номера, присутствующие в поле ввода, здесь мы удалили слово «Общее», чтобы ввести новый настроенный формат номера телефона.

Плюсы формата телефонных номеров в Excel

То, что нужно запомнить

Всегда выбирайте данные перед форматированием телефонных номеров в Excel.
Каждая страна и регион следуют своему собственному способу написания телефонных номеров. Чтобы отсортировать числа в соответствии с нашим требуемым форматом, сначала поймите значение системы счисления другой страны. Например, если вы форматируете американские телефонные номера, их телефонные номера начинаются с +1 и следуют за правильными 10 цифрами телефонного номера. Аналогичным образом, другие страны также следуют такой последовательности.
В основном номера телефонов разных стран могут иметь разные номера. Перед применением любого формата к телефонным номерам сначала рассортируйте номер по длине. Благодаря этому мы можем избежать неправильного формата тех номеров, которые могут не подойти.

Как в эксель преобразовать номера телефонов в нужный формат

Как отформатировать телефонные номера в Excel?

Плюсы формата телефонных номеров в Excel

То, что нужно запомнить

Рекомендуемые статьи

Изменение формата номера телефона

1 ответ 1

Как привести телефонные номера к единому формату?

Как это сделать?

Как это работает?

Как в Excel номера телефонов привести к единому формату?

Как в Excel изменить значение столбца «Телефон» с «244-44-44 это номер Кати» на «244-44-44»?

Как отформатировать телефонные номера в Excel?

Форматирование телефонных номеров в Excel - Пример № 1

Форматирование телефонных номеров в Excel - пример № 2

Плюсы формата телефонных номеров в Excel

То, что нужно запомнить

Рекомендуемые статьи