У Рона есть рабочий лист со столбцом телефонных номеров. Некоторые из них представляют собой семизначные числа, например, 123-4567, с предполагаемым кодом города 727.

В других есть коды городов, например (890) 123-4567. Рон хочет, чтобы все телефонные номера были в последнем формате, и задается вопросом, как лучше всего обнаружить более короткие телефонные номера и добавить к ним недостающие (727).

Лучший способ сделать это будет зависеть от характера ваших данных. Видите ли, данные могут быть текстовыми или числовыми. Это возможно, потому что Excel предоставляет специальный формат ячеек, который позволяет отображать числа типа 8015551212 как (801) 555-1212. Вы можете определить, являются ли ваши данные числовыми или текстовыми, сравнив то, что вы видите в ячейке, с тем, что отображается в строке формул. (См. Рис. 1.)

image

Рисунок 1. Определение типа номера телефона: текстовый или цифровой.

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

=IF(A1<=9999999,7270000000+A1, A1)

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

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

=IF(LEN(A1) = 8, "(727) " & A1, A1)

Таким образом, если номер телефона 555-1212 (длина 8 символов), то эта формула возвращает (727) 555-1212. Конечно, в номере телефона могут быть лишние пробелы; это часто происходит, если номера телефонов вставляются на лист из другого источника. В этом случае просто включите функцию TRIM в формулу:

=IF(LEN(TRIM(A1)) = 8, "(727) " & TRIM(A1), TRIM(A1))

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

=RIGHT("(727) " & TRIM(A1), 14)

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

Все становится немного сложнее, если в ваших данных есть номера телефонов, которые являются числовыми, а некоторые — текстовыми. В этом случае вы все равно можете составить формулу, но она станет длиннее:

=IF(LEN(TRIM(A1)) > 9, TRIM(A1), IF(LEN(TRIM(A1)) = 8, "(724) " & TRIM(A1), IF(LEN(TRIM(A1)) = 7, 7240000000+A1, "Undetermined")))

Помните, что это единая формула. Он определяет длину всего, что находится в ячейке A1. Если длина превышает 9 символов, телефонный номер считается правильным. Если длина ровно 8, то формула предполагает, что это «краткая текстовая» версия телефонного номера, например 555-1212, и добавляет код города в начало. Если длина ровно 7, то предполагается, что это «короткий цифровой» номер телефона, и к нему добавляется правильный номер. Если номер телефона не соответствует ни одному из них, формула возвращает текст «Не определено». После использования этой формулы вам все равно нужно будет отформатировать полученные ячейки с помощью специального формата номера телефона.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (13564) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.