Загальна формула
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"-","")," ",""),".","")+0
Резюме
Одним із способів очищення та переформатування телефонних номерів є видалення всіх сторонніх символів, а потім застосування вбудованого формату телефонного номера в Excel.
Ця наведена вище формула використовує ряд вкладених функцій ЗАМІНИ для вилучення пробілів, дефісів, крапок, дужок та коми.
Вам потрібно буде скоригувати фактичні заміни відповідно до ваших даних.
Пояснення
Формула працює зсередини, при цьому кожен ЗАМІННИК видаляє один символ.
Самий внутрішній ЗАМІННИК видаляє ліві дужки, а результат передається наступному ЗАМІННИКУ, який видаляє праві дужки тощо.
Кожного разу, коли ви використовуєте функцію ЗАМІНИТЬ, результатом буде текст. Оскільки ви не можете застосувати числовий формат до тексту, нам потрібно перетворити текст на числовий. Один із способів зробити це - додати нуль (+0), який автоматично перетворює числа в текстовому форматі в числа в числовому форматі.
Нарешті, застосовується "Спеціальний" формат телефонного номера (стовпець D).
Ця сторінка пояснює користувацькі формати чисел і містить безліч прикладів.
Трюк пробілів для кращої читабельності
При вкладенні декількох функцій може бути важко прочитати формулу і зберегти всі дужки збалансованими. Excel не дбає про зайвий пробіл у формулі, тому ви можете додати розриви рядків у формулу, щоб зробити формулу більш читабельною. Наприклад, формулу вище можна записати наступним чином:
= SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( A1, "(",""), ")",""), "-",""), " ",""), ".","")
Зверніть увагу, що клітинка відображається посередині, з назвами функцій вгорі та замінами нижче. Це не тільки полегшує читання формули, але й полегшує додавання та видалення замін.
Ви можете використовувати цей самий фокус, щоб полегшити читання вкладених операторів IF.
