Изменить телефонные коды с помощью формулы REPLACE Excel
>> СКАЧАТЬ БЕСПЛАТНО <<
ПОЛНОЕ РУКОВОДСТВО ПО ФОРМУЛАМ EXCEL!
Что он делает?
Заменяет часть текстовой строки на основе указанного количества символов другой текстовой строкой. Что это значит:
=ЗАМЕНИТЬ(эта ячейка, начиная с этого номера, вплоть до этого номера, с этим новым текстом)
Бывают случаи, когда нам дают кучу данных, которые не являются чистыми, и нам нужно было сделать вручную коррективы к нему.
Например, у нас есть набор телефонных номеров с неправильными кодами городов.
Мы хотим заменить старый код города каждого телефонного номера новым кодом города , , указанным в столбце NEW :
Мы хотим изменить код города 370 на код города 111.
Для второго номера телефона мы хотим изменить код города 961 на 222… и так далее.
Формула ЗАМЕНИТЬ в Excel может очень легко очистить и заменить текст… прощай медленные ручные корректировки!
Я объясню, как вы можете это сделать, и перейдите к нижней части страницы, чтобы увидеть анимированный учебник в формате GIF:
СКАЧАТЬ EXCEL WORKBOOK
Шаг 1: Нам нужно Введите Заменить Функция Рядом с ячейкой, которую мы хотим очистить данные из:
= заменить
Шаг 2: .
old_textКакой текст мы хотим изменить?
Ссылка на ячейку, содержащую текстовую строку:
=ЗАМЕНИТЬ(A11,
start_num
С какого символа мы хотим начать замену?
Мы хотим заменить код города 370, который является вторым символом в номере телефона. Открытая скобка ( — 1 -й персонаж:
= Заменить (A11, 2,
Num_Chars. Сколько символов нам нужно заменить? для замены кода города 370, состоящего из 3 символов:
= Заменить (A11, 2, 3,
NEW_TEXTКакой текст будет служить заменой?
Мы хотим заменить код области 370 из 111.
Значения замены находятся в следующем столбце (столбец B):
=ЗАМЕНИТЬ(A11, 2, 3, B11)
ШАГ 3: Проделайте то же самое для остальных ячеек перетащив ЗАМЕНИТЬ 9Формула 0048 до конца вниз с помощью левой кнопки мыши.
Обратите внимание, что все телефонные номера теперь чисты:
ПОЛЕЗНЫЕ РЕСУРСЫ:
Получите доступ к 30+ курсам ТОЛЬКО по Microsoft Excel и Office за $1.
Нажмите здесь, чтобы получить это ограниченное по времени предложение за 1 доллар!
>> СКАЧАТЬ БЕСПЛАТНО <<
ПОЛНОЕ РУКОВОДСТВО ПО ФОРМУЛАМ EXCEL!
Об авторе
Брайан =»author»>
Брайан является автором бестселлеров серии 101 Excel в мягкой обложке.
Добавление кодов городов к телефонным номерам (Microsoft Excel)
У Рона есть рабочий лист со столбцом телефонных номеров.
Некоторые из них представляют собой семизначные номера, например 123-4567, с предполагаемым кодом города 727. В другие включены коды городов, например (890) 123-4567. Рон хочет, чтобы каждый телефонный номер был в последнем формате, и задается вопросом, как лучше всего обнаружить более короткие телефонные номера и добавить к этим номерам отсутствующие (727).
Лучший способ сделать это будет зависеть от характера ваших данных. Видите ли, данные могут быть текстовыми или числовыми. Это возможно, потому что Excel предоставляет специальный формат ячеек, который позволяет отображать такие числа, как 8015551212, как (801) 555-1212. Вы можете определить, являются ли ваши данные числовыми или текстовыми, сравнив то, что вы видите в ячейке, с тем, что показано в строке формул. (См. рис. 1.)
Если ваши данные являются числовыми, вы можете легко добавить правильный код города, используя такую формулу:
=ЕСЛИ(А1<=9999999,7270000000+А1, А1)
В результате будет добавлен правильный код города, но вам все равно нужно будет отформатировать ячейку, содержащую эту формулу, в специальном формате номера телефона.
Если данные текстовые, то вы можете просто использовать формулу для проверки длины ячейки, а затем при необходимости добавить код города:
= ЕСЛИ (ДЛСТР (A1) = 8, "(727) " & A1, A1)
Таким образом, если номер телефона 555-1212 (8 символов), то эта формула возвращает (727) 555-1212. Конечно, возможно, что вокруг номера телефона могут быть лишние пробелы; это часто происходит, если номера телефонов вставлены в лист из другого источника. В этом случае просто включите в формулу функцию TRIM:
. = ЕСЛИ (ДЛСТР (ОБРЕЗАТЬ (A1)) = 8, "(727) " & ОБРЕЗАТЬ (A1), ОБРЕЗАТЬ (A1))
Есть еще одна формула, которая может оказаться очень полезной для добавления кода города, и она короче тех, что мы видели до сих пор:
=ВПРАВО("(727) " & ОБРЕЗАТЬ(A1), 14)
Эта формула добавляет код города ко всем ячейкам, а затем просто берет крайние правые 14 символов результата.
Все становится немного сложнее, если в ваших данных есть телефонные номера, которые являются числовыми, а некоторые — текстовыми.
В этом случае вы все равно можете составить формулу, но она станет длиннее:
=ЕСЛИ(ДЛСТР(ОБРЕЗАТЬ(A1)) > 9, ОБРЕЗАТЬ(A1), ЕСЛИ (ДЛСТР (ОБРЕЗАТЬ (A1)) = 8, "(724) " & ОБРЕЗАТЬ (A1), ЕСЛИ(LEN(TRIM(A1)) = 7, 7240000000+A1, "Не определено")))
Помните, что это единая формула. Он отключает длину всего, что находится в ячейке A1. Если длина превышает 9 символов, то номер телефона считается правильным. Если длина равна 8, то формула предполагает, что это «краткая текстовая» версия номера телефона, например 555-1212, и добавляет в начало код города. Если длина равна ровно 7, то предполагается, что это «короткий числовой» номер телефона, и к нему добавляется правильный номер. Если номер телефона не является ни одним из них, формула возвращает текст «Не определено». После использования этой формулы вам все равно нужно будет отформатировать полученные ячейки, используя специальный формат номера телефона.
ExcelTips — ваш источник экономичного обучения работе с Microsoft Excel.