Группы и рассылки:

Вы здесь

Работа со строками в Excel. Текстовые функции Excel

Версия для печати

   Часто в Excel приходится тем или иным образом обрабатывать текстовые строки. Вручную такие операции проделывать очень сложно когда кол-во строк составляет не одну сотню. Для удобства в Excel реализован не плохой набор функций для работы со строковым набором данных. В этой статье я коротко опишу необходимые функции для работы со строками категории "Текстовые" и некоторые рассмотрим на примерах.

Функции категории "Текстовые"

Итак, рассмотрим основные и полезные функции категории "Текстовые", с остальными можно ознакомиться самостоятельно.

  • БАТТЕКСТ (Значение) – функция преобразующая число в текстовый тип;
  • ДЛСТР (Значение) – вспомогательная функция, очень полезна при работе со строками. Возвращает длину строки, т.е. кол-во символов содержащихся в строке;
  • ЗАМЕНИТЬ (Старый текст, Начальная позиция, число знаков, новый текст) – заменяет указанное кол-во знаков с определенной позиции в старом тексте на новый;
  • ЗНАЧЕН (Текст) – преобразует текст в число;
  • ЛЕВСИМВ (Строка, Кол-во знаков) – очень полезная функция, возвращает указанное кол-во символов, начиная с первого символа;
  • ПРАВСИМВ (Строка, Кол-во знаков) – аналог функции ЛЕВСИМВ, с той лишь разницей, что возврат символов с последнего символа строки;
  • НАЙТИ (текст для поиска, текст в котором ищем, начальная позиция) – функция возвращает позицию, с которой начинается вхождение искомого текста. Регистр символов учитывается. Если необходимо не различать регистр символов, воспользуйтесь функцией ПОИСК. Возвращается позиция только первого вхождения в строке!
  • ПОДСТАВИТЬ (текст, старый текст, новый текст, позиция) – интересная функция, на первый взгляд похожа на функцию ЗАМЕНИТЬ, но функция ПОДСТАВИТЬ способна заменить на новую подстроку все вхождения в строке, если опущен аргумент «позиция»;
  • ПСТР (Текст, Начальная позиция, Кол-во знаков) – функция похожа на ЛЕВСИМВ, но способна возвратить символы с указанной позиции:
  • СЦЕПИТЬ (Текст1, Текст 2 …. Текст 30) – функция позволяет соединить до 30-ти строк. Так же, можно воспользоваться символом «&», выглядеть будет так «=”Текст1” & ”Текст2” & ”Текст3”»;

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

Пример 1
Дан набор строк:

пример работы текстовых функций в Excel

Необходимо из этих строк извлечь даты, номера накладных, а так же,  добавить поле месяц для фильтрации строк по месяцам.

    Извлечем в столбец В номера накладных. Для этого найдем так называемый ключевой символ или слово. В нашем примере видно, что перед каждым номером накладной стоит «№», а длина номера накладной 6 символов. Воспользуемся функциями НАЙТИ и ПСТР. Пишем в ячейку B2 следующую формулу :

=ПСТР(A2;НАЙТИ("№";A2)+1;6)

Разберем формулу. Из строки А2 с позиции следующей после найденного знака «№», мы извлекаем 6 символов номера.

Теперь извлечем дату. Тут все просто. Дата расположена в конце строки и занимает 8 символов. Формула для С2 следующая:

=ПРАВСИМВ(A2;8)

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

=ЗНАЧЕН(ПРАВСИМВ(A2;8))

а затем, задать формат отображения в ячейке, как это сделать было описано в статье «Формат данных в Excel».

Ну и последнее, для удобства дальнейшей фильтрации строк, введем столбец месяц, который мы получим из даты. Только для создания месяца нам необходимо откинуть день и заменить его на «01». Формула для D2:

=ЗНАЧЕН(СЦЕПИТЬ("01";ПРАВСИМВ(A2;6))) или =ЗНАЧЕН("01"&ПРАВСИМВ(A2;6))

Задайте формат ячеке «ММММ ГГГГ». Результат:

Пример 2
   В строке "Пример работы со строками в Excel" необходимо все пробелы заменить на знак "_", так же перед словом "Excel" добавить "MS".

Формула будет следующая:

=ПОДСТАВИТЬ(ЗАМЕНИТЬ(A1;ПОИСК("excel";A1);0;"MS ");" ";"_")

Для того, чтоб понять данную формулу, разбейте ее на три столбца. Начните с ПОИСК,  последней будет ПОДСТАВИТЬ.

Все. Если есть вопросы, задавайте, не стесняйтесь wink

 

Прикрепленный файл: text_func_excel.zip

 

Автор: 
Deys
Категории: 

Комментарии

Спасибо за статью. Я новичок и не понял, где я могу разместить вопрос.У меня планшет с Office Suite которая допускает работу с файлами эксель. Вопрос:
В моем эксель-дкументе пользуюсь функцией
=TODAY(), для определения текущей даты.
Я слежу за этой датой, параллельно отражая её в столбце "Дата",
Как мне текущую дату в в этом столбце превратить в константу, если наступило ожидаемое событие. Например изменилось содержимое ячейки Z100.
Спасибо.

 
Приветствую! Не совсем ясен вопрос. Если Вам требуется привязать ячейку и чтоб при копировании или протяжке формул ссылка на эту ячейку не менялась используйте $ в ссылке на константу например: =$A$1*B2. Подробнее описано здесь  
 

Здраствуйте,

Подскажите пожалуйста, с помощью каких функций Excel можно заменить в предложении несколько разных слов в предложениях (из столбца2) на слова из столбца1

Если заменять одно слово, то у меня получается с помощью функции ПОДСТАВИТЬ(столбец2строка1;"прожива ет по улице";столбец1строка1)

столбец1 ------------- столбец2 (предложения)
адрес прописки ---Анна Петровна проживает по улице
место проживания --- Алексей Васильевич живет на улице

После преобразования должно получиться так:

столбец1 --------------столбец2 (предложения)
адрес прописки-- ---- Анна Петровна адрес прописки
место проживания --- Алексей Васильевич место проживания

Может быть можно как то модифицировать функция ПОДСТАВИТЬ?

Пробовал подставить =ПОДСТАВИТЬ(F3;"ИЛИ(живет на улице;проживает по улице)";C3)

Не работает.

 

Приветствую! Стандартными функциями не получится. Возможно только путем написания пользовательской функции на VBA

 

Добавить комментарий

Plain text

  • HTML-теги не обрабатываются и показываются как обычный текст
  • Адреса страниц и электронной почты автоматически преобразуются в ссылки.
  • Строки и параграфы переносятся автоматически.
CAPTCHA
Для предотвращения регистрации спам-роботов введите символы с картинки.
CAPTCHA на основе изображений
Введите символы, изображенные на картинке.
Разместить свое видео на сайте
Вы можете разместить свои 
видео-уроки на сайте
БЕСПЛАТНО!


Подробнее>>>