Текстовые функции Excel в примерах
Excel предлагает большое количество функций, с помощью которых можно обрабатывать текст. Область применения текстовых функций не ограничивается исключительно текстом, они также могут быть использованы с ячейками, содержащими числа. В рамках данного урока мы на примерах рассмотрим 15 наиболее распространенных функций Excel из категории Текстовые.
СЦЕПИТЬ
Для объединения содержимого ячеек в Excel, наряду с оператором конкатенации, можно использовать текстовую функцию СЦЕПИТЬ. Она последовательно объединяет значения указанных ячеек в одной строке.
СТРОЧН
Если в Excel необходимо сделать все буквы строчными, т.е. преобразовать их в нижний регистр, на помощь придет текстовая функция СТРОЧН. Она не заменяет знаки, не являющиеся буквами.
ПРОПИСН
Текстовая функция ПРОПИСН делает все буквы прописными, т.е. преобразует их в верхний регистр. Так же, как и СТРОЧН, не заменяет знаки, не являющиеся буквами.
ПРОПНАЧ
Текстовая функция ПРОПНАЧ делает прописной первую букву каждого слова, а все остальные преобразует в строчные.
Каждая первая буква, которая следует за знаком, отличным от буквы, также преобразуется в верхний регистр.
ДЛСТР
В Excel Вы можете подсчитать количество знаков, содержащихся в текстовой строке, для этого воспользуйтесь функцией ДЛСТР. Пробелы учитываются.
ЛЕВСИМВ и ПРАВСИМВ
Текстовые функции ЛЕВСИМВ и ПРАВСИМВ возвращают заданное количество символов, начиная с начала или с конца строки. Пробел считается за символ.
Текстовая функция ПСТР возвращает заданное количество символов, начиная с указанной позиции. Пробел считается за символ.
СОВПАД
Функция СОВПАД позволяет сравнить две текстовые строки в Excel. Если они в точности совпадают, то возвращается значение ИСТИНА, в противном случае – ЛОЖЬ. Данная текстовая функция учитывает регистр, но игнорирует различие в форматировании.
Если регистр для Вас не играет большой роли (так бывает в большинстве случаев), то можно применить формулу, просто проверяющую равенство двух ячеек.
СЖПРОБЕЛЫ
Удаляет из текста все лишние пробелы, кроме одиночных между словами.
В случаях, когда наличие лишнего пробела в конце или начале строки сложно отследить, данная функция становится просто незаменимой. На рисунке ниже видно, что содержимое ячеек А1 и B1 абсолютно одинаково, но это не так. В ячейке А1 мы намеренно поставили лишний пробел в конце слова Excel. В итоге функция СОВПАД возвратила нам значение ЛОЖЬ.
Применив функцию СЖПРОБЕЛЫ к значению ячейки А1, мы удалим из него все лишние пробелы и получим корректный результат:
Функцию СЖПРОБЕЛЫ полезно применять к данным, которые импортируются в рабочие листы Excel из внешних источников. Такие данные очень часто содержат лишние пробелы и различные непечатаемые символы. Чтобы удалить все непечатаемые символы из текста, необходимо воспользоваться функцией ПЕЧСИМВ.
ПОВТОР
Функция ПОВТОР повторяет текстовую строку указанное количество раз. Строка задается как первый аргумент функции, а количество повторов как второй.
НАЙТИ
Текстовая функция НАЙТИ находит вхождение одной строки в другую и возвращает положение первого символа искомой фразы относительно начала текста.
Данная функция чувствительна к регистру…
… и может начинать просмотр текста с указанной позиции. На рисунке ниже формула начинает просмотр с четвертого символа, т.е. c буквы «r«. Но даже в этом случае положение символа считается относительно начала просматриваемого текста.
ПОИСК
Текстовая функция ПОИСК очень похожа на функцию НАЙТИ, основное их различие заключается в том, что ПОИСК не чувствительна к регистру.
ПОДСТАВИТЬ
Заменяет определенный текст или символ на требуемое значение. В Excel текстовую функцию ПОДСТАВИТЬ применяют, когда заранее известно какой текст необходимо заменить, а не его местоположение.
Приведенная ниже формула заменяет все вхождения слова «Excel» на «Word»:
Заменяет только первое вхождение слова «Excel»:
Удаляет все пробелы из текстовой строки:
ЗАМЕНИТЬ
Заменяет символы, расположенные в заранее известном месте строки, на требуемое значение. В Excel текстовую функцию ЗАМЕНИТЬ применяют, когда известно где располагается текст, при этом сам он не важен.
Формула в примере ниже заменяет 4 символа, расположенные, начиная с седьмой позиции, на значение «2013». Применительно к нашему примеру, формула заменит «2010» на «2013».
Заменяет первые пять символов текстовой строки, т.е. слово «Excel», на «Word».
Вот и все! Мы познакомились с 15-ю текстовыми функциями Microsoft Excel и посмотрели их действие на простых примерах. Надеюсь, что данный урок пришелся Вам как раз кстати, и Вы получили от него хотя бы малость полезной информации. Всего доброго и успехов в изучении Excel!
7 примеров использования функции «Текст по столбцам» в Excel
Эта функция крайне удивительна и может послужить вам в разных ситуациях.
Самое простое её использование — разделить текст по нескольким столбикам, но её также можно использовать и для других целей.
Сегодня, я продемонстрирую несколько вариантов использования функции «Текст по столбцам».
Как использовать функцию «Текст по столбцам»
Все очень просто — щелкните «Данные» -> «Текст по столбцам»
Далее откроется окно функции. Само выполнение состоит из 3 этапов. На каждом определяются некоторые параметры, которые индивидуальны в каждом случае, не переживайте, мы их рассмотрим.
Итак, давайте я покажу вам варианты использования.
Деление строки
Допустим, у нас есть такая табличка:
И, например, нам нужно разделить имя + фамилию и поместить их в разные столбцы.
Как это сделать?
- Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);
- На первом этапе выбираем «с разделителями», так как мы будем делить строку по пробелу;
- На втором этапе, собственно, указываем что пробел в нашем случае будет разделителем строки;
- И на третьем этапе указываем куда поместить данные;
- Подтверждаем.
- Таким образом, в нашем конкретном случае пробел являлся разделителем. Если же у вас будет строка не просто имя пробел фамилия, а будут, например, инициалы, нужно будет менять схему деления;
- Также не забывайте о том, что результат выполнения функции будет статическим, т.е. если в изначальных ячейках меняются значения — вам нужно будет заново использовать функцию.
Делим электронную почту на имя пользователя и домен
Итак, в этом случае, мы будем использовать другой разделитель.
Допустим, у нас есть следующая табличка:
Я просто придумал эти почты исходя из фильмов по супергероям ��
Разделителем, как вы могли догадаться, в этом случае будет знак «@».
Давайте разделим строки.
Как это сделать?
- Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);
- На первом этапе выбираем «с разделителями», так как мы будем делить строку по знаку «@»;
- На втором этапе, в опции «другой» укажите наш разделитель;
- И укажите куда поместить данные после обработки;
- Подтвердите.
Получаем главный адрес сайта из ссылки
Бывает такое, что из множества ссылок вам нужно «вытащить» только главные адреса сайтов.
Например, в случае https://yandex.ru/primer1 главный адрес будет yandex.ru.
Итак, допустим у нас есть следующая табличка:
Давайте разделим строки и получим главные адреса сайтов!
Как это сделать?
- Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);
- На первом этапе выбираем «с разделителями»;
- На втором этапе выберите «другой» и укажите «/»;
- Укажите куда нужно поместить данные;
- Подтвердите.
Далее можно очистить таблицу от ненужных столбцов и дубликатов.
Важная информация: в случае, если ваши ссылки не имеют https:// в начале — все будет еще проще, вы получите главный адрес сайта в первом столбике.
Приведение дат к общему формату
Часто бывает так, что при импорте дат из разных источников они имеют разный формат.
Но что делать если этих данных очень много, а необходимо сделать так, чтобы все даты были в одном, «общем», формате?
Допустим, у нас есть такая табличка:
Давайте приведем все даты к одному формату!
Как это сделать?
- Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);
- На первом этапе выбираем «с разделителями»;
- На втором этапе уберите все галочки;
- На третьем этапе выберите опцию «дата — ДМГ» и укажите куда поместить обработанные данные;
- Подтвердите.
Готово! Таким образом даты были отформатированы.
Смена формата с текстового на числовой
Бывают случаи, когда вы получаете от кого-то табличку, но не можете быстро произвести расчеты, так как некоторые числа в ней имеют текстовый формат.
Такое может быть по нескольким причинам:
- Перед числом есть знак “ ‘ “;
- Эти числа выведны благодаря некоторым функциям, которые присваивают им текстовый формат.
Допустим, у нас есть такая табличка:
Давайте переведем их в формат «Числовой».
Как это сделать?
- Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);
- На первом этапе выбираем «с разделителями»;
- На втором этапе уберите все галочки;
- нужная опция «общий»;
- Подтвердите.
Итак, теперь все числа автоматически отформатируются как «общий» формат и их можно использовать при вычислениях.
Убираем несколько первых символов значения каждой ячейки
При работе в Excel вы можете столкнуться и с такой задачей.
Допустим, у нас есть такая табличка:
Давайте удалим первые пять букв с начала каждой строки.
Как это сделать?
- Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);
- На первом этапе выбираем «фиксированной ширины»;
- На втором этапе — поставьте указатель на нужное количество символов (в нашем случае пять);
- Укажите куда нужно поместить данные;
- Подтвердите.
Важная информация: можно использовать сразу несколько указателей, в таком случае строка будет разделена на большее количество частей.
Преобразуем числа со знаком минус на конце в отрицательные числа
Такое встречается довольно редко, но все равно будет полезно знать, что делать в такой ситуации.
Допустим, у нас есть такая табличка:
Давайте обработаем эти числа.
Как это сделать?
- Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);
- На первом этапе выбираем «с разделителями»;
- На втором этапе уберите все галочки;
- На третьем этапе щелкните на «Подробнее…»;
- В открывшемся окне поставьте галочку на опции указанной на картинке ниже;
- Укажите куда поместить обработанные данные;
- Подтвердите.
Готово, теперь числа будут отрицательными. Можете использовать их в формулах.