Как в Excel номера телефонов привести к единому формату?
В загруженной в Excel базе столбец <номер телефона> записан разными способами:
- некоторые номера содержат всего 10 знаков, как добавить 7 к номеру телефона
- некоторые телефоны начинаются на +7
- некоторые начинаются 8. или +8.
какой формулой форматировать в Эксель номер телефона, чтобы преобразовать к единому формату в 11 знаков начиная с цифры 7
Чтобы обработать телефонный номер надо ввести дополнительный столбец в который записать формулу для преобразования номеров телефонов в единый формат можно просто добавить цифру 7 к последним 10 цифрам телефона.
для ячейки A2 формула имеет вид:
однако очень часто в поле телефонного номера встречаются комментарии, в этом случае
для преобразования формата потребуется добавить 7 к последним 10 символам перед первым пробелом (если пробела нет то придется добавить к концу текста) , для ячейки B2 эта формула примет вид:
если требуется преобразовать номер к формату +7. то формула примет вид:
В поле номера телефона очень часто попадают знаки, введенные для удобства чтения (пробелы, минусы, скобки и.т д.).
Очень удобно создать колонку с удалением таких знаков и в дальнейшем получать телефонный номер по строке с удалёнными лишними знаками.
Правда, после этой операции первый нецифровой символ придется находить формулой массива.
если номер находится в ячейке B3 то формула для обработки номера (с удалением скобок,плюсов, минусов и пробелов) выглядит так:
Формула массива для извлечения номера (если обработанный номер в ячейке C3) такая:
="+7"&ПРАВСИМВ( ПСТР(C3;1; ЕСЛИОШИБКА( ПОИСКПОЗ( ЛОЖЬ; ЕЧИСЛО( ЗНАЧЕН( ПСТР(C3; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( C3))) ; 1))); 0)-1; ДЛСТР(C3))) ; 10)
Поскольку это формула массива ввод формулы оканчивается комбинацией клавиш CTRL+SHIFT+ENTER
Как привести телефонные номера к единому формату?
В качестве примера, возьмем номер телефона в виде "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.
# (решетка) — один необязательный разряд, т.е. если для него нет числа, то ничего не выводится.
[] — в квадратных скобках, перед маской формата, указывает цвет шрифта. Разрешено использовать следующие цвета: черный, белый, красный, синий, зеленый, жёлтый, голубой.
"" — любой пользовательский текст (млн, уп., м и тому подобные) или любые символы (например, пробелы) — надо обязательно заключать в кавычки.
Как добавить 7 к номеру телефона в excel
Для добавления числа 7 к номеру телефона в Excel, можно использовать формулу CONCATENATE или оператор «&».
Например, если номер телефона находится в ячейке A1, то формулы будут выглядеть следующим образом:
Результатом обоих формул будет номер телефона с добавленным в начале числом 7. Обратите внимание, что в первом случае символ 7 указывается в кавычках, чтобы он был распознан как текст.
Также можно использовать функцию REPLACE для замены первой цифры номера телефона на 7:
В данном случае, функция заменит первый символ в ячейке A1 на 7 и вернет измененный номер телефона.
Если требуется добавить 7 к номерам телефонов в нескольких ячейках сразу, можно скопировать формулу в нужный диапазон ячеек или использовать автозаполнение. Для этого необходимо выделить ячейку с изначальной формулой, затем навести курсор на маленький квадратик в правом нижнем углу ячейки и перетащить его вниз до нужного диапазона ячеек.
Помимо этих способов, также можно использовать макросы и другие функции Excel для обработки номеров телефонов. Например, функцию SUBSTITUTE для замены определенных символов или подстрок в номере, или макросы для автоматической обработки телефонных номеров в больших таблицах и базах данных.
Важно помнить, что Excel предоставляет множество инструментов для работы с данными, и использование различных формул и функций может значительно облегчить процесс обработки информации. Например, можно использовать функцию LEFT или RIGHT для извлечения определенного количества символов из номера телефона, либо функцию LEN для определения длины строки.
Кроме того, в Excel можно использовать фильтры и сортировку для быстрого поиска и классификации телефонных номеров по различным критериям. Например, можно отфильтровать только те номера, которые начинаются с определенного префикса, или отсортировать номера по возрастанию или убыванию.
Если в таблице имеются дубликаты номеров телефонов, можно использовать функцию DISTINCT или удалить дубликаты вручную, используя сочетание клавиш Ctrl + F и выбрав опцию «Найти и заменить».
Также можно создавать сводные таблицы для анализа телефонных номеров и других данных. Например, можно посчитать количество номеров телефонов для каждого оператора связи или посчитать среднюю длину номера.
Наконец, для удобства работы с номерами телефонов можно использовать форматирование ячеек, чтобы числа отображались в нужном формате. Например, можно добавить скобки и дефисы для разделения номера на группы, или отформатировать номер в международном формате.
В целом, Excel предоставляет огромные возможности для работы с данными, в том числе и с телефонными номерами. Умение правильно использовать различные функции и инструменты может значительно упростить работу и повысить эффективность обработки информации.
Как в excel заменить первую цифру 8 на 7
Какой программой заменить 8 на 7 в базе номеров телефона?
Спарсил номера телефонов, но они в большинстве начинаются на 8, а нужно 7-ку.
Как-то делают в эксель, но я не умею вводить такую команду.
Нужно сохранить контакты с телефона в формате csv. Затем открыть этот файл в Excel и опцией "Заменить" (Ctrl+H) поменять ",8" на ",+7". Далее сохранить файл и импортировать контакты обратно в телефон.
В базе данных обычно номер телефона находится в отдельном столбце, например A, и если некоторые начинаются на 8(а требуется чтобы номер начинался на 7), то просто введите отдельный столбец для номера телефона в правильном формате и запишите следующую формулу, которая для первой ячейки A1 имеет вид:
При копировании формулы в свой проект ячейку A1 потребуется изменить на ячейку своего проекта, а затем эту формулу надо растянуть на весь столбец телефонов пример работы формулы:
Как привести телефонные номера к единому формату?
В качестве примера, возьмем номер телефона в виде "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.
# (решетка) — один необязательный разряд, т.е. если для него нет числа, то ничего не выводится.
[] — в квадратных скобках, перед маской формата, указывает цвет шрифта. Разрешено использовать следующие цвета: черный, белый, красный, синий, зеленый, жёлтый, голубой.
"" — любой пользовательский текст (млн, уп., м и тому подобные) или любые символы (например, пробелы) — надо обязательно заключать в кавычки.
Как в excel заменить первую цифру 8 на 7
Регистрация на форуме тут, о проблемах пишите сюда — alarforum@yandex.ru, проверяйте папку спам! Обязательно пройдите восстановить пароль
| Поиск по форуму |
| Расширенный поиск |
| К странице. |
в excel есть база телефонов в формате
89272290710
нужно заменить первую восьмерку на +7
чтобы получилось
+79272290710
подскажите, пожалуйста, как это сделать автоматически?
| Vladimir012270 |
| Посмотреть профиль |
| Найти ещё сообщения от Vladimir012270 |
| Vladimir012270 |
| Посмотреть профиль |
| Найти ещё сообщения от Vladimir012270 |
Интенсив по Python: Работа с API и фреймворками 24-26 ИЮНЯ 2022. Знаете Python, но хотите расширить свои навыки?
Slurm подготовили для вас особенный продукт! Оставить заявку по ссылке — https://slurm.club/3MeqNEk