Общая формула
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"-","")," ",""),".","")+0
Резюме
Один из способов очистить и переформатировать телефонные номера - удалить все лишние символы, а затем применить встроенный в Excel формат телефонных номеров.
В приведенной выше формуле используется ряд вложенных функций ЗАМЕНА для удаления пробелов, дефисов, точек, круглых скобок и запятых.
Вам нужно будет настроить фактические замены в соответствии с вашими данными.
Объяснение
Формула выполняется изнутри, причем каждая ЗАМЕНА удаляет один символ.
Самая внутренняя ПОДСТАВКА удаляет левые круглые скобки, а результат передается следующей ЗАМЕЩЕНИЕ, которая удаляет правые скобки и так далее.
Всякий раз, когда вы используете функцию ЗАМЕНА, результатом будет текст. Поскольку вы не можете применить числовой формат к тексту, нам нужно преобразовать текст в число. Один из способов сделать это - добавить ноль (+0), который автоматически преобразует числа в текстовом формате в числа в числовом формате.
Наконец, применяется формат телефонного номера «Особый» (столбец D).
Эта страница объясняет пользовательские форматы чисел и содержит множество примеров.
Уловка с белым пространством для лучшей читаемости
При вложении нескольких функций может быть сложно прочитать формулу и сбалансировать все круглые скобки. Excel не заботится о лишних пробелах в формуле, поэтому вы можете добавить в формулу разрывы строк, чтобы сделать формулу более читаемой. Например, формулу выше можно записать следующим образом:
= SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( A1, "(",""), ")",""), "-",""), " ",""), ".","")
Обратите внимание, что ячейка отображается посередине, с названиями функций выше и заменами ниже. Это не только упрощает чтение формулы, но и упрощает добавление и удаление замен.
Вы можете использовать этот же прием, чтобы упростить чтение вложенных операторов IF.
