Формула для номера телефона эксель - Каталог телефонных номеров компаний России и СНГ

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. Если вам не соответствует заранее созданный формат номера телефона, вы можете использовать его в качестве отправной точки для создания собственного формата. Дополнительные сведения см. в том, как создать или удалить пользовательский числовой формат.

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

Источник

Используем

Условное форматирование

для выделения номеров принадлежащих абонентам г.Москва.

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

Телефонные номера обычно отображают в формате (495) 111-1111. Для этого нужно установить формат ячеек:

Дополнительный/ Номер телефона

(нажав

CTRL+1

). В этом случае ввод в ячейку телефонного номера можно производить, опустив скобки кода региона и дефис, т.е. 4951111111.

Создадим правило

Условного форматирования

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

выделите ячейки, в которые будут вводиться телефонные номера (

A

2:

A

10

);
вызовите инструмент

Условное форматирование

(

). Откроется окно

Диспетчер правил условного форматирования

;
нажмите

Создать правило

;
выберите

Использовать формулу для определения форматируемых ячеек;
в поле «

Форматировать значения, для которых следующая формула является истинной

» введите

=ИЛИ(ЛЕВСИМВ(A2;3)=»499″; ЛЕВСИМВ(A2;3)=»495″)

– если хотим, чтобы выделялись телефоны с номерами начинающихся с кодов 495 и 499;

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

Вместо использования функции

ИЛИ()

, можно создать два правила соответствующих разным кодам с формулами

=ЛЕВСИМВ(A1;3)=»499″

и

=ЛЕВСИМВ(A1;3)=»495″

.

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

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

Условное форматирование

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

Ввод данных по шаблону

СОВЕТ

: Чтобы найти все ячейки на листе, к которым применены правила

Условного форматирования

необходимо:

на вкладке

Главная

в группе

Редактирование

щелкните стрелку рядом с командой

Найти и выделить

;
выберите в списке пункт

Условное форматирование;
будут выделены все ячейки, которым применены правила

Условного форматирования

.

Источник

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

maves

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

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

15.01.2020 12:31:32

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

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

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

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

Сообщений: 2838
Регистрация: 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

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

Сообщений: 84
Регистрация: 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

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

Сообщений: 7993
Регистрация: 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, которые пишут в заявках (и с пробелами и тире и префиксом). Уже хорошо, что есть решение.

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

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

Сообщений: 2838
Регистрация: 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, зашёл на форум как в магазин…

Источник

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

некоторые номера содержат всего 10 знаков, как добавить 7 к номеру телефона
некоторые телефоны начинаются на +7
некоторые начинаются 8. или +8.

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

Можно воспользоваться простой заменой, как в случаи «+7» заменить на «8» (ctrl+h). Еще можно воспользоваться в новом столбце формулой ПРАВСИМВ и указать сколько символов оставить. Потом присоединить к получившемуся значению необходимое значение с помощью &.

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

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

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

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

для преобразования формата потребуется добавить 7 к последним 10 символам перед первым пробелом (если пробела нет то придется добавить к концу текста) , для ячейки B2 эта формула примет вид:

=7&ПРАВСИМВ( ПСТР(B2;1; НАЙТИ(» «;B2&» «)-1); 10)&ПСТР( B2;НАЙТИ( » «;B2&» «); ДЛСТР(B2))

если требуется преобразовать номер к формату +7. то формула примет вид:

=»+7″&ПРАВСИМВ(ПСТР( B2;1;НАЙТИ(» «; B2&» «)-1);10)&ПСТР(B2; НАЙТИ(» «; B2&» «); ДЛСТР(B2))

Отклонение, это различие, разница между значениями.

Абсолютное отклонение вычислить очень просто, для этого понадобиться лишь вычесть одно значение из другого.

Если на примере, то выглядит это так:

В прошлом году налоги предприятия составили 100 000, а в этом году 120 000.

Абсолютное отклонения в этом случае будет 20 000, так как 120 000 — 100 000 = 20 000.

Что касается вычисления абсолютного отклонения в таблице Эксель, то для вычисления жмём клавишу «равно», затем левой кнопкой мышки на ячейку с одним значением, затем знак «минус» и снова левой кнопкой на второе значение во второй ячейке.

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

=ЕСЛИ( ИЛИ( ЦЕЛОЕ( ОСТАТ(A1;100)/10)=1 ; ОСТАТ(A1 ; 10)>=5;ОСТАТ(A1 ; 10)=0) ; «штук» ; ЕСЛИ( ОСТАТ(A1;10)=1 ; «штука» ; «штуки»))

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

=A1&» «&ЕСЛИ( ИЛИ( ЦЕЛОЕ( ОСТАТ(A1;100)/10)=1 ; ОСТАТ(A1;10)>=5 ; ОСТАТ(A1;10)=0) ; «штук» ; ЕСЛИ( ОСТАТ(A1;10)=1 ; «штука»;»штуки»))

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

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

=ЕСЛИ( ИЛИ( ЦЕЛОЕ( ОСТАТ(A1;100)/10) = 1 ; ОСТАТ( A1 ; 10 )>=5 ; ОСТАТ(A1 ; 10)=0) ; «ящиков» ; ЕСЛИ( ОСТАТ( A1 ; 10)=1 ; «ящик» ; «ящика»))

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

=A1&» «&ЕСЛИ( ИЛИ( ЦЕЛОЕ( ОСТАТ(A1;100)/10) = 1 ; ОСТАТ( A1 ; 10 )>=5 ; ОСТАТ(A1 ; 10)=0) ; «ящиков» ; ЕСЛИ( ОСТАТ( A1 ; 10)=1 ; «ящик» ; «ящика»))

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

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

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

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

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

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

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

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

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

В радианы можно перевести с помощью формулы

Чтобы определить угол в градусах возьмём формулы перевода градусов минут и секунд в радианы и подставим в эту формулу.

Если исходные данные заданы в ячейке D2 формате строки ГГ°ММ’СС» (57°11’35»)

то формула перевода в радианы выглядит так:

=РАДИАНЫ( ЗНАЧЕН( ЛЕВСИМВ(D2; НАЙТИ(«°»;D2) — 1)) + ЗНАЧЕН( ПСТР(D2; НАЙТИ(«°»;D2) + 1; НАЙТИ(«‘»; D2) — НАЙТИ(«°»; D2)-1))/60 + ЗНАЧЕН( ПСТР(D2; НАЙТИ(«‘»; D2) + 1; НАЙТИ(«»»»; D2) — НАЙТИ(«‘»; D2)-1))/60/60 )

Эту длинную формулу можно сделать более понятной, с помощью Alt+Enter

Если исходные данные заданы в ячейке D2 формате строки ГГ град ММ мин СС сек (57 град 11 мин 35 сек)

то формула перевода в радианы выглядит так:

=РАДИАНЫ( ЗНАЧЕН( ЛЕВСИМВ(D2; НАЙТИ(«град»;D2)-1)) + ЗНАЧЕН( ПСТР(D2; НАЙТИ(«град»; D2)+4; НАЙТИ(«мин»; D2) — НАЙТИ(«град»; D2)-4))/60 + ЗНАЧЕН( ПСТР(D2; НАЙТИ(«мин»;D2)+3; НАЙТИ(«сек»;D2) — НАЙТИ(«мин»; D2)-3))/60/60 )

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

Как заменить в экселе первую цифру во всем столбце

Пусть A столбец с телефонами, в соседнем столбце в B1 вставляем формулу
=»+7″ & ПСТР (A1;2;100)
(я считаю это лучше чем «+7» & ПРАВСИМВ (ДЛСТР (A1)-1) — вычислений меньше) ,
ессно если нужна просто 7 — плюс в формуле у «+7» не пишем =)

Берём за правый нижний угол ячейки и «растягиваем» формулу до конца данных
Выделяем столбец B
Правая кнопка на выделении, копировать
Выбираем столбец B
Правая кнопка вставить значения. Внимательно — именно значения в Excel 2010 это выглядит как значок 123
Всё. Столбец B у вас заменён вместо формул теперь их значения, если всё устраивает — выделяете столбец A и удаляете. Столбец B становится столбцом A

Как заменить 8 на 7 в excel в номере телефона

Регистрация на форуме тут, о проблемах пишите сюда — alarforum@yandex.ru, проверяйте папку спам! Обязательно пройдите восстановить пароль

Поиск по форуму

Расширенный поиск

в excel есть база телефонов в формате
89272290710
нужно заменить первую восьмерку на +7
чтобы получилось
+79272290710

подскажите, пожалуйста, как это сделать автоматически?

Vladimir012270

Посмотреть профиль

Найти ещё сообщения от Vladimir012270

Vladimir012270

Посмотреть профиль

Найти ещё сообщения от Vladimir012270

Интенсив по Python: Работа с API и фреймворками 24-26 ИЮНЯ 2022. Знаете Python, но хотите расширить свои навыки?
Slurm подготовили для вас особенный продукт! Оставить заявку по ссылке — https://slurm.club/3MeqNEk

Источник

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

Преобразуйте формат номера телефона в цифры с помощью формулы
Преобразование формата номера телефона в цифры с помощью Kutools for Excel

Преобразуйте формат номера телефона в цифры с помощью формулы

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

1. Выберите пустую ячейку (C2) для вывода результата, введите в нее приведенную ниже формулу и нажмите Enter .

=—SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,»(«,»»),»)»,»»),» «,»»),»-«,»»)

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

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

Теперь все номера телефонов в списке преобразованы в цифры.

Преобразование формата номера телефона в цифры с помощью Kutools for Excel

Этот раздел будет рекомендовать Удалить символы полезности Kutools for Excel. С помощью этой утилиты вы можете с легкостью преобразовать номер телефона в цифры в Excel.

1. Выберите список с телефонными номерами, которые нужно преобразовать в цифры, затем нажмите Кутулс > Текст > Удалить символы.

2. в Удалить символы диалоговое окно, проверьте На заказ поле введите дефис – в текстовое поле и, наконец, нажмите OK кнопка. Смотрите скриншот:

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

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

Статьи по теме:

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

Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

Комментарии (1)

Оценок пока нет. Оцените первым!

Источник

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

Отформатировать номер телефона в excel можно двумя способами:

Щелчком правой кнопкой мыши по ячейке.
На вкладке ленты в Excel.

Давайте изучим форматирование номера телефона в excel на нескольких примерах.

Вы можете скачать этот шаблон Excel для форматирования телефонных номеров здесь — Шаблон для форматирования телефонных номеров в Excel

Пример №1 — Форматирование номера телефона по почтовому индексу + 4

У нас есть данные о телефоне сотрудника компании XYZ в общем числовом формате. Здесь нам нужно будет преобразовать эти данные в легко понятную форму.

Шаги по форматированию номера телефона в следующей форме Excel:

Шаг 1 — Данные некоторых телефонных номеров в другом формате показаны ниже:

Шаг 2 — Теперь щелкните ячейку правой кнопкой мыши, и вы получите список элементов.

Шаг 3 — Нажмите на опцию Format Cells, чтобы отформатировать число,

Шаг 4 — После нажатия на ячейку формата в excel вы снова получите список, как показано на рисунке

Шаг # 5 — Теперь из списка нажмите на Специальную опцию,

Шаг # 6 — Нажмите на специальную опцию, и вы снова получите набор опций.

Шаг # 7 — Теперь щелкните почтовый индекс + 4, а затем нажмите кнопку ОК, чтобы получить результат после форматирования, как показано на рисунке.

Теперь давайте изучим еще несколько примеров с несколькими критериями.

Пример # 2 — Форматирование телефонного номера в удобочитаемые телефонные номера

У нас есть данные телефонного номера сотрудника компании XYZ в общем числовом формате. Здесь нам нужно будет преобразовать эти данные в легко понятную форму.

Шаги по форматированию номера телефона в Excel в следующей форме:

Шаг 1 — Данные некоторых телефонных номеров в другом формате показаны ниже:

Шаг 2 — Теперь щелкните ячейку правой кнопкой мыши, и вы получите список элементов.

Шаг 3 — Нажмите на опцию Форматировать ячейки Excel, чтобы отформатировать число,

Шаг 4 — После нажатия на ячейку формата вы снова получите список, как показано на рисунке

Шаг # 5 — Теперь из списка нажмите на Специальную опцию,

Шаг # 6 — Нажмите на специальную опцию, и вы снова получите набор опций.

Шаг # 7- Теперь выберите из списка опцию «Телефонный номер», как показано на рисунке.

Шаг # 8 — Теперь нажмите кнопку ОК, чтобы получить результат после форматирования, как показано на рисунке.

Пример # 3 — преобразование номера телефона в номер социального страхования

Шаги по форматированию номера телефона в Excel в следующей форме:

Шаг 1 — Данные некоторых телефонных номеров в другом формате показаны ниже:

Шаг 2 — Теперь щелкните ячейку правой кнопкой мыши, и вы получите список элементов.

Шаг 3 — Нажмите на опцию «Форматировать ячейки», чтобы отформатировать число,

Шаг 4 — После нажатия на ячейку формата вы снова получите список, как показано на рисунке

Шаг # 5 — Теперь из списка нажмите на Специальную опцию,

Шаг # 6 — Нажмите на специальную опцию, и вы снова получите набор опций.

Шаг # 7 — Теперь выберите вариант «Номер социального страхования» из списка, как показано на рисунке.

Шаг # 8 — Теперь нажмите кнопку ОК, чтобы получить результат после форматирования, как показано на рисунке.

Пример # 4 — Форматирование номера телефона до почтового индекса + 4, удобочитаемого номера телефона и номера социального страхования

Шаги по форматированию номера телефона в Excel в следующей форме:

Шаг 1 — Ниже представлены данные некоторых телефонных номеров в другом формате:

Шаг 2 — Теперь щелкните ячейку правой кнопкой мыши, и вы получите список элементов.

Шаг 3 — Нажмите на опцию Format Cells, чтобы отформатировать число,

Шаг 4 — После нажатия на ячейку формата вы снова получите список, как показано на рисунке

Шаг # 5 — Теперь из списка нажмите на Специальную опцию,

Шаг # 6 — Нажмите на специальную опцию, и вы снова получите набор опций.

Шаг # 7 — Теперь щелкните почтовый индекс + 4 / номер телефона / номер социального страхования в соответствии с требованиями, а затем нажмите кнопку ОК, чтобы получить результат после форматирования, как показано на рисунке.

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

При форматировании телефонных номеров в Excel нужно помнить несколько вещей:

Нам нужно знать текущий формат ячейки.
Форматирование по образцу копирует то же форматирование в другую ячейку.

УЗНАТЬ БОЛЬШЕ >>

Post Views: 2 041

Источник

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

Как заменить в экселе первую цифру во всем столбце

Как заменить 8 на 7 в excel в номере телефона

Преобразуйте формат номера телефона в цифры с помощью формулы

Преобразование формата номера телефона в цифры с помощью Kutools for Excel

Статьи по теме:

Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Пример №1 — Форматирование номера телефона по почтовому индексу + 4

Пример # 2 — Форматирование телефонного номера в удобочитаемые телефонные номера

Пример # 3 — преобразование номера телефона в номер социального страхования

Пример # 4 — Форматирование номера телефона до почтового индекса + 4, удобочитаемого номера телефона и номера социального страхования

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

УЗНАТЬ БОЛЬШЕ >> Post Views: 2 041

УЗНАТЬ БОЛЬШЕ >>

Post Views: 2 041