Левсимв правсимв excel: Основные текстовые функции в Excel ПСТР, ЛЕВСИМВ и ПРАВСИМВ

12 текстовых функций Excel на каждый день

Василий

16:26:00

текст

,

формула

,

функция

,

Эксель

,

Excel

,

Excel для новичков

,

Excel для чайников

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

Для примера возьмем строку «ExcelGuide.ru – про Excel и не только» и ее будем использовать в наших функциях ниже.

ЛЕВСИМВ

Функция ЛЕВСИМВ возвращает указанное количество знаков с начала строки. В качестве аргументов на первом месте указываем ту строку, из которой хотим извлечь текст, а вторым аргументом количество символов, которое хотим получить.
Давайте из нашей строки получим текст «ExcelGuide.ru»:

=ЛЕВСИМВ(B1;13)

ПРАВСИМВ

Функция ПРАВСИМВ аналогична ЛЕВСИМВ, только возвращает указанное количество символов не с начала, а с конца строки. Первым аргументом указываем строку, откуда будем получать часть текста, а вторым аргументом – количество символов.
Из нашей строки извлечем текст «про Excel и не только»:

=ПРАВСИМВ(B1;21)

ПСТР

Функция ПСТР позволяет получить указанное количество символов начиная с определенной позиции. У этой функции 3 аргумента: Текст, из которого нам нужно получить часть; стартовая позиция, с которой нужно извлечь символы; количество символов, которое хотим получить.
В нашей строке есть слово Excel, давайте его получим:

=ПСТР(B1;21;5)

ДЛСТР

Функция ДЛСТР возвращает количество символов в строке.

=ДЛСТР(B1)

ПОИСК

Функция ПОИСК предназначена для нахождения первого вхождения указанного текста в исходную строку. Аргументы функции: сначала указываем тот текст, который хотим найти; далее строку, в которой ищем текст.
Давайте в нашем примере найдем текст «про Excel»:

=ПОИСК(«про Excel»;B1)

СЦЕПИТЬ

Функция СЦЕПИТЬ позволяет последовательно объединить несколько текстовых элементов в одну строку.
В качестве аргументов необходимо перечислить те текстовые элементы, которые вы хотите соединить.
 В качестве примера объединим наш пример и строку «. Пожалуй лучший сайт про Excel )))»:

=СЦЕПИТЬ(B1;». Пожалуй лучший сайт про Excel )))»)

СОВПАД

Функция СОВПАД проверяет идентичность двух строк и возвращает Истина, если строки совпадают и ЛОЖЬ, если строки не совпадают.
Сравним нашу строку с текстом «ExcelGuide.ru»:

=СОВПАД(B1;»ExcelGuide. ru»)

СЖПРОБЕЛЫ

Функция СЖПРОБЕЛЫ удаляет лишние дублирующие пробелы. В качестве аргумента указываем строку, у которой надо удалить лишние пробелы.

=СЖПРОБЕЛЫ(B1)

ЗНАЧЕН

Функция ЗНАЧЕН преобразует текст в число. Часто случается при экспорте из разных информационных систем мы получаем числовые значения в текстовом формате, в таких случаях нам и пригодится этот функционал.
В качестве примера преобразуем текст «1000» в число 1 000:

=ЗНАЧЕН(«1000»)

ПОДСТАВИТЬ

Функция ПОДСТАВИТЬ заменяет новым текстом старый текст в исходной текстовой строке. Аргументов у функции три: сначала указываем ту строку, в которой будем менять текст; далее указываем старый текст; а затем тот, которым мы хотим заменить.
В качестве примера в нашей строке заменим «про» на «о»:

=ПОДСТАВИТЬ(B1;»про»;»о»)

ПРОПИСН

Функция ПРОПИСН преобразует все буквы в прописные. У функции только один аргумент – та строка, которую надо преобразовать.

=ПРОПИСН(B1)

СТРОЧН

Функция СТРОЧН преобразует все буквы в строчные. У функции один аргумент – тот текст, который мы хотим модифицировать.

=СТРОЧН(B1)

Кстати, если вы хотите более подробно изучить Excel, научиться строить быстро сложные отчеты и графики, то рекомендую вам курс «Excel + Google Таблицы с нуля до PRO» от Skillbox.

Спасибо за внимание. Мы разобрали 12 основных текстовых функций в Excel, которые вам могут пригодиться в ежедневной работе.

Функции правсимв и левсимв

Функция
ПРАВСИМВ возвращает крайние правые
символы строки аргумента, в то время
как функция ЛЕВСИМВ возвращает первые
(левые) символы. Синтаксис:

=ПРАВСИМВ(текст;количество_символов)
=ЛЕВСИМВ(текст;количество_символов)

Аргумент
количество_символов
задает число символов, извлекаемых из
аргумента текст.
Эти функции учитывают пробелы и поэтому,
если аргумент текст
содержит пробелы в начале или конце
строки, в аргументах функций следует
использовать функцию СЖПРОБЕЛЫ.

Аргумент
количестов_символов
должен быть больше или равен нулю. Если
этот аргумент опускается, Excel считает
его равным 1. Если количество_символов
больше числа символов в аргументе текст,
то возвращается весь аргумент.

Функции сжпробелы и печсимв

Часто
начальные и конечные пробелы не позволяют
правильно отсортировать значения в
рабочем листе или базе данных. Если
используются текстовые функции для
работы с текстами рабочего листа, лишние
пробелы могут мешать правильной работе
формул. Функция СЖПРОБЕЛЫ удаляет
начальные и конечные пробелы из строки,
оставляя только по одному пробелу между
словами. Синтаксис:

=СЖПРОБЕЛЫ(текст)

Функция
ПЕЧСИМВ аналогична функции СЖПРОБЕЛЫ
за исключением того, что она удаляет
все непечатаемые символы. Функция
ПЕЧСИМВ особенно полезна при импорте
данных из других программ, поскольку
некоторые импортированные значения
могут содержать непечатаемые символы.
Эти символы могут проявляться на рабочих
листах в виде небольших квадратов или
вертикальных черточек. Функция ПЕЧСИМВ
позволяет удалить непечатаемые символы
из таких данных. Синтаксис:

=ПЕЧСИМВ(текст)

Функция пстр

Функция
ПСТР возвращает заданное число символов
из строки текста, начиная с указанной
позиции. Эта функция имеет следующий
синтаксис:

=ПСТР(текст;нач_позиция;количество_символов)

Аргумент
текст
— это текстовая строка, содержащая
извлекаемые символы, нач_позиция
— это позиция первого символа, извлекаемого
из текста (относительно начала строки),
а количество_символов
— это число извлекаемых символов.

Функции заменить и подставить

Эти
две функции заменяют символы в тексте.
Функция ЗАМЕНИТЬ замещает часть текстовой
строки другой текстовой строкой и имеет
синтаксис:

=ЗАМЕНИТЬ(старый_текст;нач_позиция;количество_символов;

новый_текст)

Аргумент
старый_текст
— это текстовая строка, в которой надо
заменить символы. Следующие два аргумента
задают символы, которые нужно заменить
(относительно начала строки). Аргумент
новый_текст
задает вставляемую текстовую строку.

Например,
ячейка А2 содержит текст «Вася Иванов».
Чтобы поместить этот же текст в ячейку
А3, заменив имя, надо в ячейку А3 вставить
следующую функцию:

=ЗАМЕНИТЬ(А2;1;5;»Петя»)

В
функции ПОДСТАВИТЬ начальная позиция
и число заменяемых символов не задаются,
а явно указывается замещаемый текст.
Функция ПОДСТАВИТЬ имеет следующий
синтаксис:

=ПОДСТАВИТЬ(текст;старый_текст;новый_текст;номер_вхождения)

Аргумент
номер_вхождения
является необязательным. Он предписывает
Excel заменить только заданное вхождение
строки старый_текст.

Например,
ячейка А1 содержит текст «Ноль меньше
восьми». Надо заменить слово «ноль»
на «нуль».

=ПОДСТАВИТЬ(А1;»о»;»у»;1)

Число
1 в этой формуле указывает, что надо
изменить только первое «о» в строке
ячейки А1. Если аргумент номер_вхождения
опущен, Excel заменяет все вхождения строки
старый_текст
на строку новый_текст.

LEFT, RIGHT, MID, LEN и FIND — данные для рыбной ловли

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

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

Используемые строковые функции Excel	Описание операции
ЛЕВЫЙ	Получить символы с левой стороны строки
ПРАВЫЙ	Получить символы с правой стороны строки
СРЕДНИЙ	Получить символы из середины строки
ВЛЕВО, НАЙТИ	Получить все символы перед символом
ВЛЕВО, НАЙТИ	Получить все символы до пробела
ПРАВО, ДЛИН. , НАЙТИ	Получить все символы после символа
СРЕДНЯЯ, НАЙТИ	Получить все символы между двумя символами

Строковые функции Excel: ВЛЕВО, ВПРАВО, СРЕДН, ДЛСТР и НАЙТИ

Для начала предположим, что вы сохранили разные строки в Excel. Эти строки могут содержать следующие символы:

• Буквы
• Цифры
• Символы (например, тире «-»)
• Пробелы

Теперь предположим, что ваша цель состоит в том, чтобы изолировать/получить только цифры в этих строках.

Как бы вы тогда достигли этой цели, используя строковые функции Excel?

Давайте рассмотрим несколько примеров, чтобы увидеть, как вы можете достичь этой цели.

Получить определенное количество символов с

левой стороны строки

В следующем примере вы увидите три строки. Каждая из этих строк будет содержать в общей сложности 9 символов:

• Пять цифр, начиная с левой стороны строки
• Один символ тире («-»)
• Три буквы в конце строки

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

Как бы вы сделали это в Excel?

Вот шаги:

(1) Сначала введите/вставьте приведенную ниже таблицу в Excel в пределах диапазона ячеек от A1 до B4 (чтобы не усложнять все последующие примеры, таблицы, которые нужно напечатать/вставить в Excel, следует хранить в диапазоне ячеек от A1 до B4):

Поскольку цель состоит в том, чтобы получить первые 5 цифр слева, вам нужно будет использовать формулу ЛЕВЫЙ, которая имеет следующую структуру:

 =ЛЕВЫЙ(Ячейка, где находится строка, необходимое количество символов слева)
 

(2) Затем введите следующую формулу в ячейку B2:

 =ЛЕВО(A2,5)
 

(3) Наконец, перетащите ЛЕВУЮ формулу из ячейки B2 в ячейку B4, чтобы получить результаты по трем записям.

Вот как будет выглядеть ваша таблица в Excel после применения ЛЕВОЙ формулы:

Получить определенное количество символов с

правой стороны строки

Минуточку! что, если ваши цифры расположены с правой стороны строки?

Давайте рассмотрим противоположный случай, когда ваши цифры находятся в правой части строки.

Вот шаги, которые вам необходимо выполнить, чтобы получить эти цифры:

(1) Введите/вставьте следующую таблицу в ячейки с A1 по B4:

Здесь вам нужно использовать ПРАВИЛЬНУЮ формулу, имеющую следующую структуру:

 =ПРАВО(Ячейка, в которой находится строка, количество символов, необходимое справа)
 

(2) Затем введите следующую формулу в ячейку B2:

 =ВПРАВО(A2,5)
 

(3) Наконец, перетащите ПРАВИЛЬНУЮ формулу из ячейки B2 в ячейку B4.

Вот как будет выглядеть таблица после применения ПРАВОЙ формулы:

Получить определенное количество символов из

середина строки

До сих пор вы встречались со случаями, когда цифры располагались либо с левой, либо с правой стороны строки.

Но что, если цифры расположены в середине строки, и вы хотите получить только эти цифры?

Вот шаги, которые вы можете применить:

(1) Введите/вставьте следующую таблицу в ячейки с A1 по B4:

Здесь вам нужно использовать формулу MID со следующей структурой:

 =MID(Ячейка строки, Требуется начальная позиция первого символа, Необходимое количество символов)
 

(2) Теперь введите следующую формулу в ячейку B2:

 =MID(A2,4,5)
 

(3) Наконец, перетащите формулу MID из ячейки B2 в ячейку B4.

Вот как будет выглядеть таблица:

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

Гелевые все символы перед символом (для строки переменной длины)

Готовы к большему?

Допустим, у вас есть нужные цифры в левой части строки, НО количество цифр в левой части строки продолжает меняться.

В следующем примере вы увидите, как получить все нужные цифры перед символом (например, символом тире «-») для строки переменной длины.

Для этого вам нужно использовать функцию НАЙТИ, чтобы найти свой символ.

Вот структура функции НАЙТИ:

 =НАЙТИ(символ в кавычках, который нужно найти, ячейка строки)
 

Теперь давайте рассмотрим шаги, необходимые для получения всех ваших символов перед символом тире:

(1) Сначала введите/вставьте следующую таблицу в ячейки с A1 по B4:

(2) Затем введите следующую формулу в ячейку B2:

 =ЛЕВЫЙ(A2,НАЙТИ("-",A2)-1)
 

Обратите внимание, что «-1» в конце формулы просто удаляет символ тире из ваших результатов (поскольку нас интересует только сохранение цифр слева без символа тире).

(3) Как и прежде, перетащите формулу из ячейки B2 в ячейку B4. Вот результаты:

Хотя в приведенном выше примере вы использовали символ тире, приведенная выше формула также будет работать для других символов, таких как $, % и т. д.

Склеить все символы перед пробелом (для строки переменной длины)

Но что, если у вас есть пробел (а не символ), и вы хотите получить только все символы до этого пробела?

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

В частности, вместо того, чтобы помещать символ тире в функцию НАЙТИ, просто оставьте пустое место в кавычках:

 НАЙТИ(" ",A2)
 

Давайте рассмотрим все шаги:

(1) Для начала введите/вставьте следующую таблицу в ячейки с A1 по B4:

(2) Затем введите следующую формулу в ячейку B2:

 =ЛЕВЫЙ(A2,НАЙТИ(" ",A2)-1)
 

(3) Наконец, перетащите формулу из ячейки B2 в ячейку B4:

Получить все символы после символа (для строки переменной длины)

В некоторых случаях может потребоваться получить все нужные символы после символа (для строки переменной длины).

Для этого вы можете использовать функцию ДЛСТР, которая может предоставить вам общее количество символов в строке. Вот структура функции ДЛСТР:

 =ДЛСТР(Ячейка, в которой находится строка)
 

Давайте теперь рассмотрим шаги, чтобы получить все цифры после символа «-» для строк различной длины:

(1) Сначала введите/вставьте следующую таблицу в ячейки с A1 по B4:

(2) Во-вторых, введите следующую формулу в ячейку B2:

 =ВПРАВО(A2,ДЛСТР(A2)-НАЙТИ("-",A2)) 

(3) Наконец, перетащите формулу из ячейки B2 в ячейку B4:

Получить все символы между двумя символами (для строки переменной длины)

И последний, но не менее важный сценарий, в котором вам может понадобиться получить все нужные символы между двумя символами (для строки переменной длины). ).

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

Давайте теперь рассмотрим шаги для получения только цифр между двумя символами тире «-»:

(1) Сначала введите/вставьте следующую таблицу в ячейки с A1 по B4:

(2) Затем введите следующую формулу в ячейку B2:

 =СРЕДН(A2,НАЙТИ("-",A2)+1,НАЙТИ("-",A2,НАЙТИ("-",A2) )+1)-НАЙТИ("-",A2)-1)
 

(3) И, наконец, перетащите формулу из ячейки B2 в ячейку B4:

Строковые функции Excel — сводка

Строковые функции Excel можно использовать для извлечения определенных символов из строки.

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

excel — Суммировать числа в диапазоне, если ячейка содержит определенный символ в конце числа

спросил
4 года, 4 месяца назад

Изменено
4 года, 4 месяца назад

Просмотрено
312 раз

У меня есть диапазон A2:A6. Предположим, что A2=3 A3=4k A4=h A5=6k и A6 пуст.
Мне нужно суммировать все числа в этом диапазоне, оканчивающемся на «k».
Я придумал формулу:

 =СУММПРОИЗВ(НЕ(ПУСТО(--ЛЕВО(A2:A6;ПОИСК("k";A2:A6;1)-1))))
 

Но я не могу заставить (NOT(ISBLANK часть) работать. Приведенное выше возвращает 0.
Упрощенная версия этой формулы работает, если диапазон содержит только числа, заканчивающиеся на «k», но мне нужно, чтобы он пропускал пробелы.
Я не нашел идей рабочего решения, чтобы заставить его работать.
Правильный результат для вышеуказанного диапазона должен возвращать 10.

• excel
• excel-formula

1

Согласен с замечанием Брэда Диксона о том, что вспомогательный столбец значительно упрощает работу. Хотя мне нравится решать проблемы, поэтому я попробовал и придумал то, что, по моему мнению, должно быть немного более надежным?

 =СУММПРОИЗВ(ЕСЛИОШИБКА((ПРАВО(A2:A6)="k")*ПОДСТАВИТЬ(A2:A6,"k","",1),0))
 

Используется как формула массива (с Ctrl + Shift + Enter . Формула состоит из 2 основных частей:

• (RIGHT(A2:A6)="k") проверяет, заканчивается ли значение на к и
• ПОДСТАВИТЬ(A2:A6,"k","",1) удаляет этот k , чтобы получить фактическое значение.

Ваша формула (если бы она работала) включала бы любое число перед k , даже если бы это k не было в конце (например, оно использовало бы 6 в 6k2 , если бы оно присутствовало), а выше нет.

Также пробелы считаются равными 0 в Excel, поэтому добавление пробелов не изменило бы результат.

2

Попробуйте формулу массива.

Я сделал этот пример данных:

Как вы можете видеть на изображении выше, я хочу суммировать только значения в ячейках A2 и A4 (те, которые заканчиваются символом K). Результат равен 6. Я использовал следующую формулу:

=СУММ(ЕСЛИ(ПРАВО($A$1:$A$4;1)="k";ПОДСТАВИТЬ($A$1:$A$4;" к";"")+0))

Чтобы эта формула работала, ее необходимо ввести как формулу массива, т.е.
означает, что вы набираете его как обычно, но вместо нажатия Введите
для подтверждения необходимо нажать
CTRL + SHIFT + ENTER или это не сработает.