Как изменить формат номера телефона в excel - Word и Excel

Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Еще…Меньше

Excel предоставляет специальный числовом формате, который позволяет форматирование номера в виде номера телефона. Например, можно отформатовать 10-значное число, например 5555551234, как (555) 555-1234.

Выберите ячейку или диапазон, которые нужно отформатирование. Как выбрать ячейки или диапазоны?
На вкладке Главная нажмите кнопку запуска диалогового окна рядом с полем Число.
В окне Категория выберите особый.
В списке Тип выберите Телефон число.

Совет: Чтобы увидеть код числовых форматов Телефон, щелкните категорию Пользовательские и посмотрите на поле Тип. Например, код [<=9999999]###-####;(###) ###-#### используется для отображения номера телефона (555) 555-1234. Если вам не соответствует заранее созданный формат номера телефона, вы можете использовать его в качестве отправной точки для создания собственного формата. Дополнительные сведения см. в том, как создать или удалить пользовательский числовой формат.

Нужна дополнительная помощь?

Источник

maves

Пользователь

Сообщений: 5
Регистрация: 15.01.2020

Добрый день! Нужно поменять из основного формата, чтобы номера телефонов отображались в виде: +7 (999) 999-99-99. Формат ячеек использовать нельзя, так как при загрузке файла по месту требования, формат файла теряется и номера остаются набраны в первоначальном виде. Файл прилагается.

Изменено: maves — 15.01.2020 11:32:08

Mershik Пользователь Сообщений: 8277 Регистрация: 03.12.2015	maves, а всегда они вбиваются как сейчас в примере? потому что это может влиять на решение…если возможны разные вводы тел. номеров покажите их в примере максимально больше Не бойтесь совершенства. Вам его не достичь.

maves Пользователь Сообщений: 5 Регистрация: 15.01.2020	Спасибо. Да они всегда вбиваются по разному, разными людьми. Поэтому и хотелось бы автоматически их как-то преобразовать в один формат вида +7 (999) 999-99-99 Прикрепила файл с различными вариантами

Сергей

Пользователь

Сообщений: 11251
Регистрация: 01.01.1970

15.01.2020 12:14:51

в соседний столбец протянуть скопировать вставить как значения в основной столбец

Код
=ТЕКСТ(--ПРАВБ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(I3;"-";"");"(";"");")";"");"+";"");" ";"");10);"+7 (000) 000-00-00")

Лень двигатель прогресса, доказано!!!

maves Пользователь Сообщений: 5 Регистрация: 15.01.2020	Спасибо за формулу. А можно ли в этом же столбце как то заменить. В этом файле должны быть указаны именно номера в одном столбце, чтобы они не дублировались, второй столбец чисто для второго контактного номера.

Сергей

Пользователь

Сообщений: 11251
Регистрация: 01.01.1970

15.01.2020 12:26:26

Цитата
maves написал: А можно ли в этом же столбце как то заменить.

можно но это уже макрос

Лень двигатель прогресса, доказано!!!

Polkilo Пользователь Сообщений: 87 Регистрация: 24.01.2019	maves, учтите, после +7 пробела нет, если он нужен, то оберните еще раз в ПОДСТАВИТЬ и замените «(» на » («

maves

Пользователь

Сообщений: 5
Регистрация: 15.01.2020

15.01.2020 12:31:32

Цитата
Сергей написал: можно но это уже макрос

А Вы сможете написать?

Михаил Лебедев

Пользователь

Сообщений: 2856
Регистрация: 17.01.2013

15.01.2020 12:38:09

Цитата
maves написал: А Вы сможете написать?

Цитата
Сергей написал: можно но это уже макрос

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

PS
Еще формула

Код
="+7-"&ТЕКСТ(ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(I3;"+";"");"(";"");")";"");"-";"");" ";"");2;30);"000-000-00-00")

Прикрепленные файлы

Торговые точки.xlsx (25.34 КБ)

Изменено: Михаил Лебедев — 15.01.2020 12:55:39
(Загрузил файл)

Всё сложное — не нужно. Всё нужное — просто /М. Т. Калашников/

Polkilo

Пользователь

Сообщений: 87
Регистрация: 24.01.2019

#10

15.01.2020 13:19:58

maves, проверяйте

Код

Sub ololo()
Dim i As Long
Dim MyPhone As String

For i = 3 To ThisWorkbook.Sheets("Форма").Cells(Rows.Count, 9).End(xlUp).Row 'Крутим цикл по 9 столбцу за исключением шапки
    MyPhone = OnlyNumbers(ThisWorkbook.Sheets("Форма").Cells(i, 9)) 'Оставляем в номере только цифры
    If Len(MyPhone) >= 10 Then
        MyPhone = Right(MyPhone, 10) 'забираем последние 10 символов
        MyPhone = "+7 (" & Mid(MyPhone, 1, 3) & ") " & Mid(MyPhone, 4, 3) & "-" & Mid(MyPhone, 7, 2) & "-" & Mid(MyPhone, 9, 2) 'Сцепляем
        ThisWorkbook.Sheets("Форма").Cells(i, 9) = MyPhone 'выводим на лист
    End If
Next

End Sub

Function OnlyNumbers(ByVal MyString As String) As String
Dim i As Long
Dim Numbers As String

If Len(MyString) >= 1 Then
    For i = 1 To Len(MyString)
        If IsNumeric(Mid(MyString, i, 1)) Then
            Numbers = Numbers & Mid(MyString, i, 1)
        End If
    Next
    OnlyNumbers = CStr(Numbers)
End If

End Function

Изменено: Polkilo — 15.01.2020 13:21:19

	Вар.2 Всё сложное — не нужно. Всё нужное — просто /М. Т. Калашников/

maves Пользователь Сообщений: 5 Регистрация: 15.01.2020	Всем спасибо за ответы! Изменено: maves — 15.01.2020 13:45:30 (Неправильное использование цитат)

А если номер вбит по ошибке без префикса +7 или 8, то ни одно решение выше не работает, что привести к единому формату +7(000)000-00-00 или 8(000)000-00-00
Оговорка: макрос от

Polkilo

работает, но

Формула от

Сергей

не приводит к форме номера без префикса +7 или 8. Такое часто бывает
Макрос от

Михаил Лебедев

то же, не приводит к форме номера без префикса +7 или 8. Такое часто бывает

Макрос от

Polkilo

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

Что бы добить тему до готового решения, нужно:
— иметь возможность выбрать диапазон
— иметь возможность выбрать формат: +7(000)000-00-00 или 8(000)000-00-00 или 80000000000 (такой формат для всех ячеек, иначе с + формула считается)

Изменено: RUSBelorus — 15.01.2020 19:50:33

Сергей Пользователь Сообщений: 11251 Регистрация: 01.01.1970	RUSBelorus, выше было обозначено приведите пример возможных вариантов для них и сделано если у вас корявые вводные создайте отдельную тему и покажите их Лень двигатель прогресса, доказано!!!

RUSBelorus

Пользователь

Сообщений: 110
Регистрация: 15.11.2014

#15

15.01.2020 20:27:31

Цитата
Сергей написал: корявые вводные

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

Сергей Пользователь Сообщений: 11251 Регистрация: 01.01.1970	RUSBelorus, тема по приведению телефонных номеров к одному виду избита на нашей планете и формулы и удф и макросы если захотеть можно найти всё Лень двигатель прогресса, доказано!!!

RUSBelorus

Пользователь

Сообщений: 110
Регистрация: 15.11.2014

#17

15.01.2020 20:38:06

Цитата
Сергей написал: — иметь возможность выбрать диапазон- иметь возможность выбрать формат: +7(000)000-00-00 или 8(000)000-00-00 или 80000000000 (такой формат для всех ячеек, иначе с + формула считается)

Тема избита вдоль и поперек, а дальше будет мусолиться такими как Я (простите). Я говорю про законченное решение для всех и всякого. Такого нет нигде

— иметь возможность выбрать диапазон
— иметь возможность выбрать формат: +7(000)000-00-00 или 8(000)000-00-00 или 80000000000 (такой формат для всех ячеек, иначе с + формула считается)

PS сам пользуюсь приведением телефонов к формату и знаю о чем говорю.

Изменено: RUSBelorus — 15.01.2020 20:39:10

RUSBelorus, человеческие корявые ручки могут такого написать, что никакие формулы, а возможно и макросы это не исправят. В таком случае лучше вводить данные по шаблону и запрещать ввод данных не соответвущих ему.

Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл

	Даже если и по шаблону с любым префиксом. Все равно нет РЕШЕНИЯ выбрать диапазон и один из трех форматов телефона одновременно) Изменено: RUSBelorus — 15.01.2020 20:50:07

Сергей

Пользователь

Сообщений: 11251
Регистрация: 01.01.1970

#20

15.01.2020 20:49:01

Цитата
RUSBelorus написал: PS сам пользуюсь приведением телефонов к формату и знаю о чем говорю.

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

Лень двигатель прогресса, доказано!!!

	Так задача простая, объединить решения в одно. Не будет больше (или станет меньше на порядок) подобной PS Получается, надеяться можно от снисхождения Polkilo ) Изменено: RUSBelorus — 15.01.2020 20:58:18

Kuzmich

Пользователь

Сообщений: 7998
Регистрация: 21.12.2012

#22

15.01.2020 20:59:32

UDF формат +7(000)000-00-00

Код

Public Function RgxPhone(iString As Range) As String
 Dim re As Object
 Dim tempString
  Set re = CreateObject("vbscript.regexp")
    re.Pattern = "(-|s|+|(|))"
    re.Global = True
    re.IgnoreCase = True
 tempString = re.Replace(iString, "")
 re.Pattern = "((8)|(7))(d{3})+(d{3})+(d{2})+(d{2})+"
  If re.Test(tempString) Then
   RgxPhone = re.Replace(tempString, "$1$2($4) $5-$6-$7")
    If (Left(RgxPhone, 1) <> "8") Then
        RgxPhone = "+" + RgxPhone
    Else
       RgxPhone = "+7" + Mid(RgxPhone, 3)
    End If
  End If
End Function

Сергей

Пользователь

Сообщений: 11251
Регистрация: 01.01.1970

#23

15.01.2020 21:01:00

Цитата
RUSBelorus написал: Так задача простая,

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

Лень двигатель прогресса, доказано!!!

Вы все усложняете. Вот все 4 формата номера, которые я постоянно встречаю от клиентов, не считаю что это искусственный интеллект:
+79250966362
79250966362
89250966362
9250966362
PS только макрос

Polkilo

их все обрабатывает

а то что они могут быть в разных столбиках и это не ИК.

Изменено: RUSBelorus — 15.01.2020 21:41:28

vikttur

Пользователь

Сообщений: 47199
Регистрация: 15.09.2012

#25

15.01.2020 23:06:19

Цитата
RUSBelorus написал: Вы все усложняете.

А Вы оптимист
Приходилось обрабатывать, насмотрелся. +/7/8 слева в разных вариациях — цветочки. Номер слитно, с пробелами, с разными разделителями, номер отделен от текста пробелом, номер слитно с текстом ,экспоненциальный формат, со знаками вопроса вместо некоторых цифр…

Удивитесь, макрос

Polkilo

+79250966362	+7 (925) 096-63-62
79250966362	+7 (925) 096-63-62
89250966362	+7 (925) 096-63-62
9250966362	+7 (925) 096-63-62
+7(925) 096 63-62	+7 (925) 096-63-62
7-925-096-63-62	+7 (925) 096-63-62
8 9 2 5 0 9 6 6 3 6 2	+7 (925) 096-63-62
(9)(2)(5)(0)(9)6 6 3 6 2	+7 (925) 096-63-62
+79250966362	+7 (925) 096-63-62
+7+9+250+966+362	+7 (925) 096-63-62
89-2509-6636-2	+7 (925) 096-63-62
9250966362	+7 (925) 096-63-62

PS Речь об обработке телефона 10 знаков, а не об выделении телефона из текста

Изменено: RUSBelorus — 15.01.2020 23:25:46

vikttur

Пользователь

Сообщений: 47199
Регистрация: 15.09.2012

Я писал о «всех 4-х форматах», которые Вы встречаете.
И о том, что кроме написания номера, он может быть в тексте с другими числовыми днными (тоже, кстати, написанными «мизинцем правой ноги черех левое плечо». И обрабка таких шедевров не заканчивается написанием макроса — через время возврат к работе — «а мы еще вот такое обнаружили, доработайте, пожалуйста»

RUSBelorus

Пользователь

Сообщений: 110
Регистрация: 15.11.2014

#28

15.01.2020 23:29:19

Цитата
vikttur написал: Я писал о «всех 4-х форматах», которые Вы встречаете.

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

Михаил Лебедев

Пользователь

Сообщений: 2856
Регистрация: 17.01.2013

#29

16.01.2020 04:55:08

Цитата

RUSBelorus написал:
Формула от Сергей не приводит к форме номера без префикса +7 или 8. Такое часто бывает
Макрос от Михаил Лебедев то же, не приводит к форме номера без префикса +7 или 8. Такое часто бывает
Макрос от Polkilo работает корректно, но только по первому столбцу, нет формы выбора столбца или диапазона
RUSBelorus написал:
Так задача простая, объединить решения в одно.

Вы что, и с простой задачей сами справиться не можете?

Прикрепленные файлы

Обработка номеров телефонов макросом или функцией.xlsm (36.95 КБ)

Изменено: Михаил Лебедев — 16.01.2020 05:18:43
(добавил файл-пример со всяким рожном)

Всё сложное — не нужно. Всё нужное — просто /М. Т. Калашников/

Андрей VG

Пользователь

Сообщений: 11878
Регистрация: 22.12.2012

Excel 2016, 365

#30

16.01.2020 08:13:28

Цитата
Михаил Лебедев написал: Вы что, и с простой задачей сами справиться не можете?

Добрый день, Михаил.
А вы уверены, что он собирался? RUSBelorus, зашёл на форум как в магазин…

Источник

When you type a seven-or-ten-digit phone number into an Excel cell that uses the phone number format, Excel will automatically change the way the number looks so that it appears as a phone number. If you want to change the number’s appearance, there are two ways to do it, depending on how you want the final result to look.

Series in Single Cell

Open the Excel 2010 file that contains the phone numbers you want to change. Click on the cell that contains the phone number, or click on the range of cells if you want to change the format for several cells on your spreadsheet.
Select the «Home» tab at the top of the Excel window.
Click the drop-down arrow in the «Number» area of the ribbon. Choose «General» from the list of options and your phone numbers will now appear as just a series of numbers.

Series in Columns

Open the Excel 2010 files that holds your phone numbers. Click on the cell that you want to change into a series of numbers spread out over several columns, or click on the letter above the column that contains multiple phone numbers. Because of how this process will overwrite items on the spreadsheet, you can’t perform this task on multiple columns at once.
Select the «Data» tab at the top of the Excel window, then click the «Text to Columns» button. The text to columns wizard will appear on the screen.
Click the radio button next to «Fixed Width,» then click «Next.» Then click the mouse button just after the first number that appears in the white box in the wizard. You will see a line appear, which signifies where a column break will go. Click after the second number to add another break, then continue to add breaks after each number. Click «Next» to continue.
Select «General» from the list that appears so that you numbers will display as numbers on the spreadsheet. Click the small box next to «Destination,» then click the cell on the spreadsheet where you want your phone number to start. The numbers will overwrite the information in the columns to the right of the cell where you choose to start, so be sure you are choosing some place that doesn’t have any important data to the right of it.
Click «Finish» to close the wizard and your phone number will appear as a series of numbers spread out over several columns.

Источник

В MS Excel существует бесчисленное множество форматов, в которых можно представить свои числовые данные. Тут вам и даты, и валюты и даже номера телефонов. Но что делать если нам не подходит ни один из предустановленных форматов? Создать свой.

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

Для того чтобы создать формат данных установите курсор на ячейку с данными (в данном случае на номер телефона, введенный без «восьмерки») и нажмите сочетание клавиш «Ctrl+1» или нажмите правую клавишу мышки и в контекстном меню выберите пункт «Формат ячеек…»:

В открывшемся диалоговом окне во вкладке «Число» слева в списке «Числовые форматы:» кликните мышкой по пункту «(все форматы)»:

В поле «Тип:» вместо слова «Основной» вставьте +7″ «(0##)» «###-##-##:

Обратите внимание что в окошке «Образец» данные поменяли свой вид. Если вас устраивает такой вид данных нажмите «ОК»:

Данные в указанной ячейке примут соответствующий вид:

Теперь ваш формат сохранен в MS Excel и его можно применять к другим ячейкам. Для этого выделите необходимые ячейки и нажмите сочетание клавиш «Ctrl+1» или нажмите правую клавишу мышки и в контекстном меню выберите пункт «Формат ячеек…». В открывшемся диалоговом окне во вкладке «Число» слева в списке «Числовые форматы:» кликните мышкой по пункту «(все форматы)». В списке форматов найдите свой (он будет последним), выделите его и нажмите «ОК»:

Для закрепления знаний попробуйте создать еще один формат. Введите в любую ячейку любое число, например, «127». Откройте диалоговое окно «Формат ячеек» проделав операции, описанные выше и введите в поле «Тип:» следующий формат # ##0″ «уп. и нажмите «ОК»:

Если данные в выделенной ячейке приняли нужный вид, значит вы все сделали правильно:

Чтобы удалить ненужный формат — откройте диалоговое окно «Формат Ячеек» найдите в списке формат, который вы хотите удалить и нажмите кнопку «Удалить»:

Добавить комментарий

Источник

Нужна дополнительная помощь?

Series in Single Cell

Series in Columns

Добавить комментарий

А вот еще интересные статьи: