ЛЕВСИМВ, ЛЕВБ (функции ЛЕВСИМВ, ЛЕВБ)

Как выделить N-е слово, фрагмент текста между символами, число из текстовой строки, фамилию, имя, отчество и многое другое.

Извлечение первого слова из текста

Для того, чтобы выполнить эту задачу можно при помощи формулы:

=ЛЕВСИМВ(A2;НАЙТИ(«*»;ПОДСТАВИТЬ(A2;» «;»*»;1)))

Извлечение первого слова из ячейки Excel

Важно! Последняя цифра 1 отвечает за количество слов, которое будет скопированы, поэтому заменив ее, например, на 3 можно получить первых 3 значения.

Пример для трех.

=ЛЕВСИМВ(A2;НАЙТИ(«*»;ПОДСТАВИТЬ(A2;» «;»*»;3)))

Извлечение первых трех слов из таблицы Excel

Пример:

Извлеките первое слово с помощью текстовых формул

Предположим, у вас есть следующий набор данных, в котором вы хотите получить первое слово из каждой ячейки.

7620510d21fb92d54dbb049a52f058e8.pngПриведенная ниже формула сделает это:=IFERROR(LEFT(A2,FIND(” “,A2)-1),A2)9dad747f05c315243fdc6c1971246d2c.pngПозвольте мне объяснить, как работает эта формула.

Часть формулы FIND используется для поиска позиции символа пробела в текстовой строке. Когда формула находит позицию символа пробела, функция LEFT используется для извлечения всех символов перед этим первым пробелом в текстовой строке.

Хотя одной формулы LEFT должно быть достаточно, она выдаст ошибку, если в ячейке есть только одно слово и нет пробелов.

Чтобы справиться с этой ситуацией, я заключил формулу LEFT в формулу IFERROR, которая просто возвращает исходное содержимое ячейки (поскольку нет пробелов, указывающих, что она пуста или содержит только одно слово).

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

Если вам не нужна формула, вы можете преобразовать ее в значения.

Описание

Функция ЛЕВСИМВ возвращает первый символ или несколько первых символов текстовой строки на основе заданного числа символов.

Функция ЛЕВБ возвращает первый символ или несколько первых символов текстовой строки на основе заданного числа байтов.

Важно: 

  • Эти функции могут быть доступны не на всех языках.

  • Функция ЛЕВБ отсчитывает по два байта на каждый символ, только если языком по умолчанию является язык с поддержкой двухбайтовой кодировки. В противном случае функция ЛЕВБ работает так же, как функция ЛЕВСИМВ, и отсчитывает по одному байту на каждый символ.

К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.

Первые N символов — формула в Excel

Вытащить первый символ или N символов ячейки формулой проще простого. И есть даже 2 варианта:

  1. ЛЕВСИМВ
  2. ПСТР

А так будут выглядеть формулы, извлекающие первые N символов из ячейки A1. Подставьте свое число вместо N, чтобы формулы работали:

=ЛЕВСИМВ(A1;N)=ПСТР(A1;1;N)

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

Например:

  • извлечь текст после цифр
  • извлечь первые N цифр
  • сделать первую букву ячейки заглавной

Ввод формулы

Если строка находится в ячейке A1, введите в ячейку B1 следующую формулу: = IFERROR (LEFT (A1, FIND (“”, A1) -1), A1)

Microsoft Excel – группа работает!Обсуждения

Просмотр темы5

как в ячейке оставить только первое слово

Сергей Васильевич
Сергей Васильевич 13 фев 2016 в 17:52

Всем привет! очень нужен совет, необходимо убрать в ячейке все слова кроме первого … заранее спасибо за совет.

НравитсяПоказать список оценивших
Андрей Рязанцев
Андрей Рязанцев 13 фев 2016 в 17:55

Только макрос. Пробежаться по всем ячейкам, вычленить первое слово, переопределить значение ячейки.

НравитсяПоказать список оценивших
Марсель Салимьянов
Марсель Салимьянов 13 фев 2016 в 18:08

можно формулой ЛЕВСИМВ()

НравитсяПоказать список оценивших
Дмитрий Якушев
Дмитрий Якушев 14 фев 2016 в 7:57

=левсимв(A1;найти(” “;A1))

1

НравитсяПоказать список оценивших

Андрей Мастеров
Андрей Мастеров 28 янв 2018 в 12:54
Марсель

,

https://vk.com/page-69078253_53069159

НравитсяПоказать список оценивших

Выделение первого слова в ячейке в Excel

C помощью функции НАЙТИ найдем позицию первого пробела, а затем воспользуемся функцией ЛЕВСИМВ, которая возвращает указанное количество символов с начала текста (как раз до первого найденного пробела):

Выделение первой части. Вариант 1
Вместо ЛЕВСИМВ можно воспользоваться функцией ПСТР, которая более универсальна и позволяет вернуть заданное количество символов начиная с указанной позиции, а не только с начала строки (в данном случае с 1, так как мы выделяем именно первое слово):

Выделение первой части. Вариант 2
Недостаток такого способа в том, что если в ячейке содержится ровно 1 слово, то она вернет значение ошибки, так как пробелов в нем нет:

Вид ошибки
Поэтому дополнительно добавим проверку на ошибку — в случае если во фразе нет пробелов, то возвращаем исходный текст:

Выделение первой части. Исправленный вариант

В этой статье

Вычитать числа можно несколькими способами, в том числе:

Разбить столбец по количеству символов

Можно получить первые N символов столбца, разбив его по количеству символов. Первые N останутся, а остальные будут перенесены в соседний справа. Можно разбить и на несколько — Excel не ставит ограничений на количество столбцов.

извлечение первых символов разбивкой текста по столбцамРазбиваем столбец на два, оставляя в нем первые 6 символов и переносим оставшиеся в соседний

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

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

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Данные

Цена продажи

Швеция

Формула

Описание

Результат

=ЛЕВСИМВ(A2;4)

Первые четыре символа первой строки

Продажа

=ЛЕВСИМВ(A3)

Первый символ второй строки

Ш

Нужна дополнительная помощь?

Извлечь первые символы с помощью регулярных выражений

Чтобы выбрать из строки символы по регулярным выражениям, в Google Spreadsheets доступна функция REGEXEXTRACT. Там она существует уже очень давно.

А вот Microsoft не столь серьезно озадачились вопросом — официально регулярные выражения в Excel существуют только как подключаемый модуль VBA. Поэтому доступны очень узкому кругу посвященных специалистов с навыками программирования на нем.

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

Подробно тут — регулярные выражения в Excel и примеры регулярных выражений. А ниже на картинке — примеры формул, которые позволят извлечь первые N символов, в том числе букв или цифр по отдельности, из любой ячейки.

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

Функции доступны абсолютно бесплатно и работают при подключенной надстройке !SEMTools для Excel даже в стартовой версии.

Пользовательская функция

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

Перейдем в редактор Visual Basic (быстрый переход комбинацией клавиш Alt + F11), создаём новый модуль (Insert -> Module) и вставляем туда код функции:

FunctionGetNWord(MyCell AsRange,NAsInteger)

    GetNWord=Replace(Right(Mid(” “&Replace(MyCell,” “,String(1000,” “)),1,1000*N),1000),” “,“”)

EndFunction

Теперь проверим работу новой функции GetNWord на том же примере, что и для уже разобранной выше формулы:

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

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...