Текстовые функции Excel значительно экономят время, если ваша работа связана с управлением массивными данными, особенно данными, импортированными из других источников.
К счастью, все данные ASCII легко импортируются, но формат импортируемых данных может сильно варьироваться от одного источника к другому.
К примеру, поля могут быть разделены определёнными символами или быть с вкладками, пробелами, запятыми или периодами. Все остальные символы предпочтительнее пробелов. Это потому, что пространства существуют не только между полями, но и между отдельными словами в полях, что создаёт отдельную проблему при сортировке. Вот почему следующие текстовые функции очень полезны.
A. Используйте функцию SUBSTITUTE для замены одной строки текста на другую.
Синтаксис функции: Синтаксис (или структура предложения) функции SUBSTITUTE заключается в следующем:
= SUBSTITUTE (текст, старый текст, новый текст, [Номер экземпляра]).
Примечание. Если вы не укажете экземпляр номера, каждое появление старого текста будет изменено на новый текст. Если вы укажете экземпляр Num, заменяется только это появление старого текста. Например, ввод числа «1» означает, что вы хотите изменить только первое вхождение этого слова в строку.
1. Введите некоторые фразы в столбце A (от A2 до A13).
2. Введите слово или фразу, которую вы хотите изменить в столбце B.
3. Введите слово или фразу, которую вы хотите заменить старым текстом, в столбце C.
4. Введите следующую формулу в ячейки от D2 до D7 (или половину базы данных): = ЗАМЕСТИТЕЛЬ (A2, B2, C2,1).
Это изменяет / заменяет только первое вхождение старого текста в новый текст.
5. Затем введите эту формулу в оставшиеся ячейки (в нашем случае от D8 до D13): = ЗАМЕСТИТЕЛЬ (A2, B2, C2). Это изменяет / заменяет все вхождения старого текста на новый текст.
Примечание. Эта функция чувствительна к регистру, поэтому, если ваши результаты не работают, измените текст на все те же случаи.
B. Извлеките последнее слово в строке текста, используя TRIM, RIGHT и SUBSTITUTE
В этом примере объект должен извлечь последнее слово, то есть фамилию, из строки текста (полные имена списка клиентов).
1. Введите некоторые имена в столбце A: имя, фамилия, имя и инициалы.
2. Введите эту формулу в B2: = TRIM (ПРАВО (ЗАМЕНА (A2, «», REPT («», 50)), 50)).
3. Скопируйте формулу из B2, вплоть до B3-B1000 (или в конец вашей базы данных). В этом примере мы предполагаем, что ваша база данных имеет 1000 записей.
4. Эта формула работает, потому что функция SUBSTITUTE находит все пробелы в строке текста, а затем заменяет каждое пространство пробелом на 50 пробелов. Функция RIGHT удаляет 50 символов (справа налево), а функция TRIM удаляет все лишние ведущие пространства, оставляя только одно последнее слово. Если у вас есть более длинные строки текста, попробуйте заменить 100 или более для 50 значений в приведенной выше формуле.
C. Извлеките первое слово / имя в строке текста, используя LEFT & SEARCH
Эта формула работает, когда вам нужно отделить первое имя от среднего и последнего имени списка клиентов.
1. Введите некоторые имена в столбце A (или используйте те же имена из предыдущего упражнения).
2. Введите эту формулу в B2-B1000: = LEFT (A2, SEARCH («», A2) -1), чтобы извлечь первое имя каждого клиента в отдельный столбец.
D. Извлеките все, кроме первого слова в строке текста, используя TRIM, RIGHT, REPT и SUBSTITUTE
Цель этого упражнения — удалить репутацию из списка имен клиентов. Эти клиенты предоставляют конфиденциальную информацию опроса, поэтому компания не хочет, чтобы названия и псевдонимы каждого человека указывали на должности.
1. Введите еще несколько имен в столбце A (или используйте те же имена из предыдущего упражнения). Введите некоторые отличия перед каждым именем; например, г-н, мисс, фрау, г-жа, д-р, сэр, лорд, леди, капитан и т. д.
2. Введите эту формулу в B2-B1000: = TRIM (ПРАВО (ЗАМЕСТИТЬ (TRIM (A2), «», REPT («», 60)), 180)), чтобы извлечь полные имена всех клиентов за вычетом обращения.
3. И если вы хотите извлечь почетную награду (по какой-то причине), введите эту формулу в C2 через C1000: = LEFT (A2, SEARCH («», A2) -1).
E. Извлеките имена из адресов электронной почты, используя LEFT, FIND и SUBSTITUTE
Наихудшей задачей является частое ручное переименование имен клиентов или доменных имен с адресов электронной почты, особенно если список превышает 5000 имен. Используйте следующие формулы для выполнения этой задачи за считанные минуты.
1. Введите некоторые адреса электронной почты в столбце A.
2. Введите эту формулу в B2-B5000: = LEFT (A2, FIND («@», a2) -1), чтобы извлечь полные имена всех клиентов.
3. Введите эту формулу с C2 по C5000, чтобы удалить подчеркивание между первым и последним именем: = SUBSTITUTE (B2, «», «_»).
4. Переместитесь в ячейку F2. Выберите «Формулы» > « Текст» >. Введите C2 в поле «Текст» на экране диалога «Аргументы функций» или щелкните ячейку C2 и нажмите «ОК» . Эта формула преобразует имена в правильный случай (то есть первая буква первого и последнего имени, заглавная буква, все остальные буквы в нижнем регистре).
5. Скопируйте формулу в F2 в F3-F5000 и нажмите Enter.
G. Извлечение доменов из адресов электронной почты с использованием TRIM, LEFT, SUBSTITUTE, MID, FIND, LEN и REPT
1. Введите эту формулу в D2-D5000 для извлечения доменных имен из адресов электронной почты:
= TRIM (LEFT (ЗАМЕНА (MID (A2, FIND («@», A2), LEN (A2)), «», REPT («», 100)), 100))
2. И последнее, введите эту формулу в E2 — E5000, чтобы удалить знаки @ из извлеченных доменных имен: = SUBSTITUTE (D2, «@», «»).
