Функция СЖПРОБЕЛЫ (на английском TRIM) удаляет лишние пробелы: в начале и в конце ячейки, а двойные и тройные пробелы между словами превращает в один: =СЖПРОБЕЛЫ(A2). Неразрывные пробелы из выгрузок (символ 160) она не видит — их сначала заменяют обычными: =СЖПРОБЕЛЫ(ПОДСТАВИТЬ(A2;СИМВОЛ(160);" ")). Переносы строк внутри ячейки тоже остаются — для них =СЖПРОБЕЛЫ(ПОДСТАВИТЬ(A2;СИМВОЛ(10);" ")).

Что делает функция СЖПРОБЕЛЫ

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

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

Синтаксис функции СЖПРОБЕЛЫ

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

Примеры формул с функцией СЖПРОБЕЛЫ

См. примеры:
Функция ПОВТОР в Excel
Добавить символы перед/после каждого слова
Удалить последнее слово в Excel

Сжать пробелы без функции СЖПРОБЕЛЫ

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

Но если в вашем Excel подключена надстройка !SEMTools - то есть! Подробнее - в статье:

Удалить лишние пробелы в Excel

Почему СЖПРОБЕЛЫ не удаляет пробелы — частые ошибки

• Пробелы остались. Это неразрывные пробелы (код 160) — замените их ПОДСТАВИТЬ(A2;СИМВОЛ(160);" ").
• Исходный столбец не изменился. Функция выводит результат в другую ячейку — скопируйте его и вставьте как значения поверх исходника.
• Число стало текстом. СЖПРОБЕЛЫ возвращает текст: поставьте два минуса перед формулой, чтобы получить число.
• Нужно удалить вообще все пробелы. СЖПРОБЕЛЫ оставляет один между словами; все пробелы удаляет ПОДСТАВИТЬ(A2;" ";"").

Функция СЖПРОБЕЛЫ — частые вопросы

Как называется функция TRIM на русском?

СЖПРОБЕЛЫ. Синтаксис тот же: =СЖПРОБЕЛЫ(A2).

Как называется СЖПРОБЕЛЫ на английском?

TRIM.

Почему СЖПРОБЕЛЫ не удаляет пробелы?

Скорее всего, это неразрывные пробелы. Используйте =СЖПРОБЕЛЫ(ПОДСТАВИТЬ(A2;СИМВОЛ(160);" ")).

Как проверить, есть ли в ячейке лишние пробелы?

Формулой =ДЛСТР(A2)<>ДЛСТР(СЖПРОБЕЛЫ(A2)).

Как удалить все пробелы в ячейке?

Формулой =ПОДСТАВИТЬ(A2;" ";"").

Итоги: функция СЖПРОБЕЛЫ в Excel

Надеюсь, на этой странице вы нашли то, что искали. СЖПРОБЕЛЫ — первая функция для чистки текста; для данных из выгрузок дополняйте её заменой символа 160.

Функция ПСТР в Excel извлекает часть текста из середины строки: =ПСТР(A1;5;3) вернёт 3 символа начиная с 5-го. Ей нужно знать начальную позицию и количество символов; если позиция плавает, её находят функцией ПОИСК — так из ФИО извлекают имя, из артикула — код, а из строки — слово с нужным фрагментом. ПСТР всегда возвращает текст, поэтому извлечённые цифры превращают в число через ЗНАЧЕН. На английском функция называется MID.

Что делает функция ПСТР в Excel?

ПСТР выделяет фрагмент текста из середины текстовой записи.
Функция должна знать, с какой позиции начинается извлекаемый фрагмент и сколько символов выбрать.
Если количество выбираемых символов превышает доступное, будут выбраны только доступные символы.

Синтаксис ПСТР

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

• Текст — строка или ссылка на ячейку.
• НачальнаяПозиция — номер первого извлекаемого символа (с 1). Если он больше длины текста, результат — пустая строка.
• КоличествоСимволов — сколько символов взять. Если их меньше, ПСТР вернёт сколько есть, без ошибки.

ПСТР: формат результата и исходных данных

Аналогично ЛЕВСИМВ и ПРАВСИМВ.

Примеры использования ПСТР

Пример 1

В этом примере показано, как извлечь элемент переменной длины, который находится внутри текста.
В данном случае это имя внутри ФИО.
Извлекаемый текст имени всегда находится между первым и вторым пробелом.

=ПСТР(A2;ПОИСК(" ";A2)+1;ПОИСК(" ";A2;ПОИСК(" ";A2)+1)-ПОИСК(" ";A2)-1)

Пример 2

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

=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(A2;" ";ПОВТОР(" ";99));МАКС(1;ПОИСК(B2;ПОДСТАВИТЬ(A2;" ";ПОВТОР(" ";99)))-50);99))

ПСТР и числа: извлечь цифры и превратить в число

ПСТР возвращает текст, даже если вырезаны одни цифры: «20391» выровнено влево и не суммируется. Оберните результат в ЗНАЧЕН или поставьте двойной минус. Так же собирают дату из строки вида ГГГГММДД.

Разбить текст на символы с помощью ПСТР

Если номер позиции брать из СТОЛБЕЦ, одна формула, протянутая вправо, разложит строку по одному символу в ячейку. В Excel 365 весь массив символов даёт =ПСТР(A2;ПОСЛЕД(ДЛСТР(A2));1).

Почему ПСТР возвращает ошибку или не то — частые ошибки

• #ЗНАЧ!. Начальная позиция меньше 1 или количество символов отрицательное — чаще всего ПОИСК не нашёл пробел или фрагмент. Оберните в ЕСЛИОШИБКА.
• Результат пустой. Начальная позиция больше длины текста.
• Число не суммируется. ПСТР возвращает текст — используйте ЗНАЧЕН(ПСТР(…)) или --ПСТР(…).
• Захвачен лишний пробел или символ. Проверьте «+1» и «-1» вокруг ПОИСК и уберите пробелы СЖПРОБЕЛЫ.
• Формула не принимается. В русском Excel аргументы разделяются точкой с запятой: =ПСТР(A1;2;3).

Функция ПСТР — частые вопросы

Как работает ПСТР в Excel?

Возвращает заданное число символов начиная с указанной позиции: =ПСТР(A1;5;3).

Как из ячейки извлечь часть текста?

С начала — ЛЕВСИМВ, с конца — ПРАВСИМВ, из середины — ПСТР; если позиция плавает, вычислите её через ПОИСК.

Как извлечь текст между двумя символами?

Найдите оба символа ПОИСК и возьмите разницу позиций как длину, как в примере с именем из ФИО.

Как ПСТР называется на английском?

MID. Формула =ПСТР(A1;5;3) в английском Excel — =MID(A1,5,3).

Почему ПСТР возвращает текст вместо числа?

Все текстовые функции возвращают текст. Превратите его в число через ЗНАЧЕН или двойной минус.

Итоги: функция ПСТР в Excel

Надеюсь, на этой странице вы нашли то, что искали. ПСТР — основа извлечения текста из середины строки; в связке с ПОИСК она находит нужный фрагмент где угодно.

Функция ПРАВСИМВ в Excel возвращает последние N символов текста: =ПРАВСИМВ(A2;4) вернёт четыре последних символа, =ПРАВСИМВ("анализ данных";6) — слово «данных». Без второго аргумента — один последний символ. Вместе с ДЛСТР и НАЙТИ она вырезает фамилию после имени — =ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(" ";A2)), а последнее слово в Excel 365 проще взять =ТЕКСТПОСЛЕ(A2;" ";-1). На английском функция называется RIGHT.

Что делает ПРАВСИМВ?

Функция ПРАВСИМВ возвращает часть адресуемого значения справа, длиной в указанное количество символов. Иными словами, позволяет извлечь последние N символов ячейки или другого объекта, к которому применяется.

Функция будет полезна лишь тогда, когда количество символов, которые нужно взять справа в строке, фиксировано и известно заранее.

На практике это количество зачастую приходится вычислять, а для этого требуется поиск с конца строки справа налево в ячейке. Поскольку простой функции для этого нет (функции ПОИСК и НАЙТИ ищут слева направо), методам решения задачи посвящена отдельная статья.

Синтаксис ПРАВСИМВ

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

Второй аргумент опционален. Если его не опустить (не указывать), формула возвращает один последний символ.

Если второй аргумент равен или превышает длину строки, возвращается весь исходный текст ячейки.

Если он равен нулю — возвращается пустая строка.

Если указывается отрицательное число, возвращается ошибка #ЗНАЧ.

ПРАВСИМВ - форматирование

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

Поэтому для корректного текстового извлечения части символов может понадобиться функция ТЕКСТ (пример выше).

Примеры формул с функцией ПРАВСИМВ

Следующая таблица была использована для извлечения фамилии человека из ячейки с его именем и фамилией.

1. Функция НАЙТИ находит позицию пробела между именем и фамилией
2. Длина фамилии рассчитывается путем вычитания позиции пробела из общей длины текста ячейки, которая, в свою очередь, высчитывается функцией ДЛСТР
3. Функция ПРАВСИМВ после этого извлекает фамилию по указанной позиции

Другие примеры формул с ПРАВСИМВ

Функция крайне востребована в самых различных сценариях. Ниже список статей, в которых применяются формулы с функцией ПРАВСИМВ:

Удалить первую букву / первые N символов в начале или конце ячейки в Excel

Число и сумма прописью в Excel

Функция СУММПРОИЗВ

Функция ЕСЛИ в Excel

ЗНАЧЕН

Извлечь последние символы из ячеек в Excel

Почему ПРАВСИМВ возвращает не то — частые ошибки

• Лишний символ в результате. В конце ячейки невидимый пробел — очистите текст СЖПРОБЕЛЫ: =ПРАВСИМВ(СЖПРОБЕЛЫ(A2);4).
• Из даты получились странные цифры. ПРАВСИМВ работает с числом, которым хранится дата. Используйте ТЕКСТ или ГОД.
• #ЗНАЧ!. Вычисленная длина отрицательная — чаще всего НАЙТИ не нашёл пробел в ячейке из одного слова.
• Результат не считается. Он текстовый — добавьте два минуса перед формулой.

Функция ПРАВСИМВ — частые вопросы

Как использовать ПРАВСИМВ в Excel?

Укажите текст и число символов с конца: =ПРАВСИМВ(A2;4).

Как извлечь фамилию или второе слово?

Формулой =ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(" ";A2)).

Как взять последнее слово из ячейки?

В Excel 365 — =ТЕКСТПОСЛЕ(A2;" ";-1), в старых версиях — формулой с ПОДСТАВИТЬ из примера выше.

Как получить последние цифры числом?

Формулой =--ПРАВСИМВ(A2;4).

Как называется ПРАВСИМВ на английском?

RIGHT.

Итоги: функция ПРАВСИМВ в Excel

Надеюсь, на этой странице вы нашли то, что искали. ПРАВСИМВ — пара к ЛЕВСИМВ: всё, что нужно вырезать с конца строки.

Функция ЛЕВСИМВ в Excel возвращает первые N символов текста слева: =ЛЕВСИМВ(A2;3) вернёт первые три символа, =ЛЕВСИМВ("анализ данных";6) — слово «анализ». Без второго аргумента функция берёт один символ. Вместе с ПОИСК она вырезает первое слово — =ЛЕВСИМВ(A2;ПОИСК(" ";A2&" ")-1), а с ДЛСТР отрезает символы с конца — =ЛЕВСИМВ(A2;ДЛСТР(A2)-1). На английском функция называется LEFT.

Что делает ЛЕВСИМВ?

Возвращает первые N символов текста слева.

Синтаксис ЛЕВСИМВ

=ЛЕВСИМВ(Текст;Количество Символов)

Второй аргумент, число символов для извлечения, опционален и по умолчанию (если его не указывать) равен 1, т.е. результатом формулы будет первый символ.

Если второй аргумент равен или превышает длину строки, возвращается весь исходный текст ячейки.

Если он равен нулю - возвращается пустая строка.

Если указывается отрицательное число, возвращается ошибка #ЗНАЧ.

ЛЕВСИМВ - форматирование

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

Поэтому для корректного текстового извлечения части символов из дат в числовом формате может понадобиться функция ТЕКСТ (см. пример на картинке выше).

Примеры формул с функцией ЛЕВСИМВ

Рассмотрим примеры использования функции ЛЕВСИМВ на практике.

Пример 1 - извлечь первое слово

В этом простейшем примере извлекаем первое слово в ячейке с помощью комбинации функций ЛЕВСИМВ и НАЙТИ.

=ЛЕВСИМВ(A1;НАЙТИ(" ";A1)-1)

Таблица выше была использована для извлечения имени из строки с именем и фамилией. Функция НАЙТИ используется для определения положения пробела между именем и фамилией. Следовательно, длина имени - это позиция пробела минус один символ.
Функция ЛЕВСИМВ извлекает имя на основе его длины.

А как извлечь фамилию (второе слово)? Ответ на этот вопрос смотрите в описании функции ПРАВСИМВ.

Пример 2 - извлечь кроме последнего символа

В комбинации ЛЕВСИМВ с функцией ДЛСТР извлекаем из строк переменной длины все, кроме последнего знака.

Пример 3 - извлечь текст до первой цифры (формула массива)

В каталоге освещения название коллекции всегда представлено текстовым обозначением. Артикул конкретного товара при этом всегда начинается с цифр. Задача - извлечь из названия конкретного товара его коллекцию без артикула.

Получается, задача состоит в том, чтобы извлечь символы до любой первой цифры. Сделаем это с помощью комбинации ЛЕВСИМВ с функциями МИН и ПОИСК.

Формула заимствует механику из первого примера, но является формулой массива и выглядит следующим образом:

Так будет выглядеть формула для ячейки A1.

=ЛЕВСИМВ(A1;МИН(ЕСЛИОШИБКА(ПОИСК({0:9:8:7:6:5:4:3:2:1};A1);1000))-2)

Формула вводится сочетанием клавиш Ctrl + Shift + Enter, как любая формула массива. Но если у вас Office 365 или Excel 2021 (и позже) - то можно просто клавишей Enter.

Как работает формула:

1. Функция ПОИСК ищет одновременно 10 цифр, перечисленных в массиве, и возвращает массив позиций
2. Поскольку часть цифр при поиске возвращают ошибку, используется функция ЕСЛИОШИБКА, чтобы для таких значений вернуть заведомо наибольшее число (в данном случае 1000)
3. Функция МИН возвращает наименьшее из чисел - это и будет позиция первой цифры в строке
4. Поскольку перед цифрами также всегда присутствует пробел, вычитается 2 символа, а не 1. Можно перестраховаться на случай отсутствия пробелов - оставить 1 и убрать пробелы функцией СЖПРОБЕЛЫ.
5. Функция ЛЕВСИМВ возвращает текст до вычисленной таким образом позиции последнего знака, идущего перед первой цифрой и пробелом перед ней.

Все примеры с функцией ЛЕВСИМВ на сайте

Заменить первую букву ячейки и сделать её заглавной
Извлечь первые N символов ячейки Excel
Первые слова ячеек Excel
Посчитать количество дней в месяце
Разделить текст и цифры в Excel
Создать алфавит в Excel
Удалить последние N слов в ячейках Excel
Удалить последний символ / N символов в конце ячеек Excel
Удалить первую букву / первые N символов в начале ячейки в Excel
Число и сумма прописью в Excel

Почему ЛЕВСИМВ возвращает не то — частые ошибки

• #ЗНАЧ!. Второй аргумент отрицательный. Чаще всего это ПОИСК(" ";A2)-1 в ячейке без пробела — допишите пробел: ПОИСК(" ";A2&" ").
• Из даты получились цифры вроде 46. ЛЕВСИМВ работает с числом, которым Excel хранит дату. Используйте ТЕКСТ или ДЕНЬ, МЕСЯЦ, ГОД.
• Результат не считается числом. ЛЕВСИМВ всегда возвращает текст: =ЛЕВСИМВ("123";2) — это «12» текстом. Поставьте перед формулой два минуса: =--ЛЕВСИМВ(A2;2).
• В начале ячейки невидимые пробелы. Сначала очистите текст: =ЛЕВСИМВ(СЖПРОБЕЛЫ(A2);3).

Функция ЛЕВСИМВ — частые вопросы

Как использовать ЛЕВСИМВ в Excel?

Укажите текст и количество символов: =ЛЕВСИМВ(A2;3) вернёт первые три символа ячейки A2.

Какое значение вернёт ЛЕВСИМВ("анализ данных";6)?

«анализ» — первые шесть символов строки.

Как извлечь первое слово?

Формулой =ЛЕВСИМВ(A2;ПОИСК(" ";A2&" ")-1).

Где находится функция ЛЕВСИМВ?

На вкладке «Формулы» → «Текстовые» или через Мастер функций (Shift+F3).

Как называется ЛЕВСИМВ на английском?

LEFT. Парные функции — ПРАВСИМВ (RIGHT) и ПСТР (MID).

Итоги: функция ЛЕВСИМВ в Excel

Надеюсь, на этой странице вы нашли то, что искали. ЛЕВСИМВ — основа большинства формул, которые вырезают начало текста.

Функция ЗНАЧЕН в Excel превращает текст, похожий на число, в настоящее число: =ЗНАЧЕН("1 250,5") вернёт 1250,5, =ЗНАЧЕН("15%") — 0,15, а =ЗНАЧЕН("12:30") — время 0,52. Чаще всего её применяют вместе с ПСТР, ЛЕВСИМВ и ПРАВСИМВ: те вырезают цифры из строки, а ЗНАЧЕН делает их числом, с которым можно считать. На английском функция называется VALUE — в английском Excel это =VALUE(A1). Если текст не похож на число, результат — ошибка #ЗНАЧ!.

Что делает функция ЗНАЧЕН

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

Если число в середине длинного фрагмента текста, его нужно будет извлечь, используя другие текстовые функции, такие как ПОИСК, ПСТР, НАЙТИ, ЗАМЕНИТЬ, ЛЕВСИМВ, ПРАВСИМВ.

Синтаксис функции ЗНАЧЕН

=ЗНАЧЕН(Текст)

ЗНАЧЕН: формат результата и исходных данных

Специального форматирования не требуется.

Результат будет показан в виде числового значения на основе исходного текста.

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

Если исходный текстовый формат отображается как время чч:мм, результатом будет время.

То же самое применимо для других форматов.

Пример формулы с функцией ЗНАЧЕН

Извлечь количество процентов из текста сложно, не зная заранее, сколько в нем знаков. Это может быть от 1 цифры (5%) до 4 цифр с запятой (12,25%).

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

Основная проблема заключается в расчете длины числа для его извлечения.

Если при извлечении предположить максимальную длину из четырех цифр и знака %, когда процент только одна цифра, при обычном извлечении по маске "?????%" в выражение попадут буквы.

Чтобы обойти проблему, можно использовать функцию ПОДСТАВИТЬ, чтобы увеличить количество пробелов между словами в тексте. Теперь при извлечении по маске

?????%

любые лишние символы будут пробелами, которые функция ЗНАЧЕН проигнорирует.

Формула ниже аналогична формуле на картинке и извлечет из ячейки A1 проценты длиной от 1 до 5 знаков, включая запятую:

=ЗНАЧЕН(ПСТР(ПОВТОР(" ";5)&ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";5));ПОИСК("?????%";ПОВТОР(" ";5)&ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";5)));6))

Как функция ЗНАЧЕН помогает работать с числами в тексте

Функция ЗНАЧЕН в Excel преобразует текст в число, если это возможно. Это особенно полезно, когда данные представлены как текст (например, “123”, “005”, “1 000”), но вам нужно выполнить арифметические действия — сложение, умножение, процент и т.д. Без предварительного преобразования Excel не будет воспринимать такие значения как числа, а результат формулы окажется неверным или вернёт ошибку.

Часто ЗНАЧЕН комбинируется с текстовыми функциями ПРАВСИМВ, ЛЕВСИМВ, ПСТР и ПОИСК. Это позволяет «вырезать» нужную часть строки и затем применять к ней математические операции. Такой подход особенно актуален при обработке кодов, артикулов, цен, идентификаторов, которые хранятся в виде текста.

Альтернативы ЗНАЧЕН: двойное отрицание и ЧЗНАЧ

Тот же результат дают двойной минус =--A2 и умножение на единицу =A2*1 — их часто используют внутри СУММПРОИЗВ. Если в данных чужие разделители (точка для тысяч, запятая для дроби или наоборот), подойдёт ЧЗНАЧ, где разделители указываются явно.

Полезные формулы с функцией ЗНАЧЕН

1. Округлить проценты внутри текстовой строки:

=СЖПРОБЕЛЫ(ЗАМЕНИТЬ(ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";10));МИН(ЕСЛИОШИБКА(ПОИСК(СИМВОЛ(СТРОКА(48:57));ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";10)));""));6;ТЕКСТ(ОКРУГЛ(ЗНАЧЕН(ПСТР(ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";10));МИН(ЕСЛИОШИБКА(ПОИСК(СИМВОЛ(СТРОКА(48:57));ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";10)));""));6));2);"0%")))

Например, строка "Путину доверяют 81,87% россиян" станет "Путину доверяют 82% россиян".

2. Извлечь первое число и посчитать НДС 20%:

="НДС: "&ТЕКСТ(ЗНАЧЕН(ПСТР(ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";10));МИН(ЕСЛИОШИБКА(ПОИСК(СИМВОЛ(СТРОКА(48:57));ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";10)));""));9)/5;"0")&" руб."

Если A1 = "Стоимость товара - 225500 руб.", результат: НДС: 45100 руб.

3. Прибавить 5 к числу в начале текста

Для значений типа "30 см.".

=СЖПРОБЕЛЫ(ЗАМЕНИТЬ(ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";2));1;3;ТЕКСТ(ЗНАЧЕН(ЛЕВСИМВ(ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";2));3))+5;"0")&" "))

Если A1 = "30 см", результат: 35 см

4. Увеличить число в конце текста на 15%:

=ЗАМЕНИТЬ(A1;ДЛСТР(A1)-2;3;ТЕКСТ(ЗНАЧЕН(ПРАВСИМВ(A1;3))*1,15;"0"))

Если A1 = "Сумма045", результат: Сумма52

Почему ЗНАЧЕН возвращает #ЗНАЧ! — частые ошибки

• Не тот десятичный разделитель. «1.5» в русском Excel — не число. Замените точку ПОДСТАВИТЬ(A1;".";",") или используйте ЧЗНАЧ.
• Неразрывные пробелы. Данные из 1С и с сайтов содержат СИМВОЛ(160), который ЗНАЧЕН не считает пробелом: =ЗНАЧЕН(ПОДСТАВИТЬ(A1;СИМВОЛ(160);"")).
• В тексте есть буквы или символы валюты. Сначала вырежьте цифры ПСТР/ЛЕВСИМВ/ПРАВСИМВ, а уже потом применяйте ЗНАЧЕН.
• Пустая ячейка даёт 0, а не ошибку. Если это мешает, проверьте ячейку заранее: =ЕСЛИ(A1="";"";ЗНАЧЕН(A1)).
• Результат выглядит странно (46082 или 0,52). Это дата или время в виде числа — задайте ячейке нужный формат.

Функция ЗНАЧЕН — частые вопросы

Что делает функция ЗНАЧЕН в Excel?

Превращает текст, который выглядит как число, процент, дата или время, в числовое значение: =ЗНАЧЕН(A1).

Как функция ЗНАЧЕН называется на английском?

VALUE. Формула =ЗНАЧЕН(A1) в английском Excel выглядит как =VALUE(A1).

Чем ЗНАЧЕН отличается от ЧЗНАЧ?

ЗНАЧЕН понимает только разделители текущей локали, а в ЧЗНАЧ (NUMBERVALUE) десятичный разделитель и разделитель групп задаются аргументами.

Как превратить текст в число без формулы?

Выделите столбец и выполните «Данные → Текст по столбцам → Готово» или вставьте 1 через «Специальная вставка → Умножить».

Как извлечь число из текста?

Вырежьте цифры ПСТР/ПРАВСИМВ/ЛЕВСИМВ и оберните в ЗНАЧЕН — примеры выше; в Excel 365 удобна и РЕГИЗВЛЕЧЬ: =--РЕГИЗВЛЕЧЬ(A1;"\d+").

Итоги: функция ЗНАЧЕН в Excel

Надеюсь, на этой странице вы нашли то, что искали. ЗНАЧЕН — связующее звено между текстовыми функциями и вычислениями: вырезали цифры — превратили в число — посчитали.

Функция ПОВТОР в Excel повторяет текст заданное число раз: =ПОВТОР("-";20) нарисует линию из 20 дефисов, =ПОВТОР("★";A2) — рейтинг звёздочками. С её помощью строят гистограммы прямо в ячейках, добавляют ведущие нули (=ПОВТОР("0";6-ДЛСТР(A2))&A2) и создают «буфер» из пробелов в формулах извлечения слов. Если нужно просто, чтобы текст из одной ячейки повторялся в другой, ПОВТОР не нужен — достаточно ссылки =A2. На английском функция называется REPT.

Что делает функция ПОВТОР

Эта функция повторяет фрагмент текста указанное количество раз.

Необходимо указать текст для повторения и сколько раз повторить его.

В последних версиях Excel функция выдаст ошибку #ЗНАЧ!, если возвращаемое значение превышает 32767 символов.

Синтаксис функции ПОВТОР

=ПОВТОР(текст;число_повторений)

Примеры формул с функцией ПОВТОР

Пример 1

Таблица выше использовалась для создания простой гистограммы показателей продаж.

Функция  ПОВТОР  использует значение продаж, поделенное на максимальное значение из этой же таблицы, чтобы количество повторений не превышало 100.

Чтобы между символами "l" не отображались отступы, использован шрифт Playbill.

Пример наиболее полезен для совместимости с Excel версий до 2010, т.к. в них еще не было инструмента "Спарклайны".

Пример 2

Функция ПОВТОР используется в сложной формуле для извлечения слов, содержащих определенный символ.

Функция используется для того, чтобы создать достаточно большое количество пробелов между словами (99 в данном случае).

=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(A2;" ";ПОВТОР(" ";99));МАКС(1;ПОИСК("@";ПОДСТАВИТЬ(A2;" ";ПОВТОР(" ";99)))-50);99))

Использование функции ПОВТОР делает формулу более компактной.

Ведущие нули и заполнитель до нужной длины

ПОВТОР легко дополняет текст до фиксированной длины: недостающее количество символов — это нужная длина минус ДЛСТР. Так делают коды с ведущими нулями и строки оглавления с точками.

Примеры такого применения смотрите в статьях:

Функция ПСТР

Функция ЗНАЧЕН

Частые ошибки с ПОВТОР

• #ЗНАЧ!. Число повторений отрицательное или результат длиннее 32 767 символов.
• Текст без кавычек. Повторяемый символ берётся в кавычки: ПОВТОР("*";5).
• Гистограмма «рваная». Между символами видны промежутки — используйте █ или шрифт без межбуквенных отступов.
• Нужно повторить значение в другой ячейке. ПОВТОР не копирует ячейки — используйте ссылку =A2 или $A$2 для закреплённой ячейки.

Функция ПОВТОР — частые вопросы

Что делает ПОВТОР в Excel?

Повторяет текст заданное число раз: =ПОВТОР("ab";3) вернёт «ababab».

Как ПОВТОР называется на английском?

REPT.

Как сделать, чтобы текст повторялся в другой ячейке?

Поставьте ссылку =A2; чтобы при копировании формулы ссылка не сдвигалась — =$A$2.

Как добавить ведущие нули?

Формулой =ПОВТОР("0";6-ДЛСТР(A2))&A2 или =ТЕКСТ(A2;"000000") для чисел.

Как заполнить повторяющиеся данные в ячейках?

Протяните маркер заполнения или выделите диапазон, введите значение и нажмите Ctrl+Enter — оно появится во всех выделенных ячейках.

Итоги: функция ПОВТОР в Excel

Надеюсь, на этой странице вы нашли то, что искали. ПОВТОР — простая функция, которая выручает в визуализации и сложных текстовых формулах.

Функция ТЕКСТ в Excel превращает число или дату в текст в нужном формате: =ТЕКСТ(A2;"ДД.ММ.ГГГГ") вернёт дату вида 21.10.2026, =ТЕКСТ(A2;"# ##0,00") — число с разделителями разрядов, =ТЕКСТ(A2;"0%") — проценты, =ТЕКСТ(A2;"00000") — номер с ведущими нулями. Чаще всего ТЕКСТ нужна, чтобы склеить число или дату с текстом и не потерять формат: ="Оплатить до "&ТЕКСТ(A2;"ДД.ММ.ГГГГ"). Коды форматов в русском Excel пишутся русскими буквами: Д, М, Г, Ч.

Что делает функция ТЕКСТ

Функция ТЕКСТ конвертирует число в текст в заданном числовом формате.

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

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

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

И далее выбрать нужный тип в списке числовых форматов.

Синтаксис функции ТЕКСТ

=ТЕКСТ(Число;Формат)

ТЕКСТ: формат результата и исходных данных

Специального форматирования не требуется.

Логические значения не конвертируются в их численные эквиваленты, но становятся текстом.

Числа, отформатированные как текст, воспринимаются как числа.

Коды форматов функции ТЕКСТ

Второй аргумент — это тот же код, что в окне «Формат ячеек → (все форматы)», только в кавычках. Самые нужные коды:

Дата в нужном формате

Для дат используются буквы Д (день), М (месяц) и Г (год). Один символ — без ведущего нуля, два — с нулём, три-четыре — сокращённое и полное название.

Числа и проценты

0 — обязательная цифра, # — необязательная, пробел между ними — разделитель разрядов. Знак % умножает число на 100.

Склеить число или дату с текстом

Амперсанд склеивает значение ячейки, а не то, что видно на экране, поэтому дата превращается в число. ТЕКСТ сохраняет формат внутри фразы.

Примеры формул с функцией ТЕКСТ

Пример 1

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

Специалист использовал формулу ТЕКСТ с пользовательским форматом.

Пример 2

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

Почему функция ТЕКСТ не работает — частые ошибки

• Формула вернула «ГГГГ» или «DD.MM.YYYY» вместо даты. Коды формата зависят от языка Excel: в русском — Д, М, Г, в английском — D, M, Y. Формулы, скопированные из англоязычных инструкций, нужно переписать.
• Результат не суммируется. ТЕКСТ всегда возвращает текст. Для расчётов используйте исходное число, а ТЕКСТ — только для вывода.
• Вместо даты получилось число. Значит, в ячейке не дата, а текст, похожий на дату, — переведите его в дату функцией ДАТАЗНАЧ.
• Пропали ведущие нули после вставки. Ячейку с результатом не переводите обратно в число — нули исчезнут.

Функция ТЕКСТ — частые вопросы

Как работает функция ТЕКСТ в Excel?

Она берёт число или дату и возвращает текст в формате из второго аргумента: =ТЕКСТ(A2;"ДД.ММ.ГГГГ").

Какие форматы есть у функции ТЕКСТ?

Любые числовые форматы Excel: даты (ДД.ММ.ГГГГ, ММММ, ДДДД), время (ЧЧ:ММ), числа (0,00, # ##0), проценты (0%) и собственные, например "+0 (000) 000-00-00".

Как добавить к числу ведущие нули?

Формулой =ТЕКСТ(A2;"00000") — число нулей задаёт длину результата.

Как вставить дату в текст, чтобы она не превратилась в число?

Оберните дату в ТЕКСТ: ="Срок: "&ТЕКСТ(A2;"ДД.ММ.ГГГГ").

Как называется функция ТЕКСТ на английском?

TEXT. Коды формата в английском Excel пишутся латиницей: =TEXT(A2,"DD.MM.YYYY").

Итоги: функция ТЕКСТ в Excel

Надеюсь, на этой странице вы нашли то, что искали. ТЕКСТ нужна там, где важен вид числа: в подписях, письмах, склейках. Для расчётов оставляйте исходные числа.

Функция НЕ в Excel меняет логическое значение на противоположное: =НЕ(ИСТИНА) вернёт ЛОЖЬ, =НЕ(A2>100) — ИСТИНА для чисел до 100. Чаще всего НЕ используют, чтобы перевернуть проверку: =НЕ(ЕПУСТО(A2)) — «ячейка заполнена», =НЕ(ЕЧИСЛО(ПОИСК("аренда";A2))) — «текст не содержит слова». Для простого «не равно» функция не нужна — есть оператор =A2<>"Москва".

Что делает функция НЕ

Эта функция производит отрицание входящего логического значения.

Можно сказать и иначе - функция проверяет истинность значения или вычисления. Если входящее значение или вычисление ложно, результат функции - ИСТИНА, и наоборот

Синтаксис функции НЕ

=НЕ(Выражение)

НЕ: формат результата и исходных данных

Требования ко входящим значениям аналогичны функциям И, ИЛИ.

Отличие может быть в формате результата - И и ИЛИ возвращают единственное значение. Отрицание функцией НЕ производится с каждым его элементом и возвращается аналогичный массив.

Любые числа, отличные от нуля, интерпретируются как ИСТИНА, ноль - как ЛОЖЬ.

Любые текстовые значения, кроме "ЛОЖЬ", "ИСТИНА" в текстовом формате, отдают ошибку #ЗНАЧ.

Другие логические функции

ЕСЛИ, И, ИЛИ

Частые ошибки с функцией НЕ

• #ЗНАЧ!. В НЕ передан текст. Сравните текст с чем-нибудь: =НЕ(A2="да").
• Двойное отрицание. =НЕ(НЕ(…)) ничего не меняет; часто вместо НЕ(ИЛИ(…)) понятнее записать И с обратными условиями.
• Пустая ячейка считается ЛОЖЬ. =НЕ(A2) для пустой A2 вернёт ИСТИНА — используйте ЕПУСТО.

Функция НЕ — частые вопросы

Что делает функция НЕ в Excel?

Возвращает противоположное логическое значение: ИСТИНА становится ЛОЖЬ и наоборот.

Как в Excel записать «не равно»?

Оператором <>: =A2<>"Москва". То же самое — =НЕ(A2="Москва").

Как проверить, что ячейка не пустая?

Формулой =НЕ(ЕПУСТО(A2)) или =A2<>"".

Как проверить, что текст не содержит слово?

Формулой =НЕ(ЕЧИСЛО(ПОИСК("слово";A2))).

Как называется НЕ на английском?

NOT.

Итоги: функция НЕ в Excel

Надеюсь, на этой странице вы нашли то, что искали. НЕ — короткий способ превратить любую проверку в обратную.

Функция И в Excel возвращает ИСТИНА, только если выполнены все условия: =И(B2>=80;C2="да") — балл не ниже 80 и есть допуск, =И(A2>=10;A2<=20) — число в интервале от 10 до 20. Обычно И вкладывают в ЕСЛИ, чтобы вывести своё значение: =ЕСЛИ(И(B2>=80;C2="да");"зачёт";"незачёт"). Условий может быть до 255. Если достаточно выполнения хотя бы одного условия, используйте ИЛИ.

Что делает функция И

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

Её можно использовать для проверки того, что ряд чисел удовлетворяет определенным условиям.

Её можно использовать для проверки того, что число или дата попадают между верхним и нижним пределами.

Как и функция ИЛИ, обычно используется в паре другими логическими функциями, наиболее часто с функцией ЕСЛИ, и с формулами массива

Синтаксис функции И

=И(Выражение1;Выражение2…)

=И(ДиапазонЗначений)

И: формат результата и исходных данных

Функция корректно обработает:

• Числа и вычисления, возвращающие их
• Логические выражения (ИСТИНА, ЛОЖЬ) и вычисления, возвращающие их
• Ячейки, содержащие их
• Диапазоны ячеек, если хотя бы одна ячейка содержит числа, ИСТИНА или ЛОЖЬ
• Массивы значений или вычислений

Если на вход подается диапазон, в значениях которого присутствуют текстовые значения, они не учитываются функцией, как и пустые ячейки.

Функция выдаст ошибку, если:

• на вход получены только текстовые или пустые ячейки (одна или несколько)
• среди значений есть ошибки (#ЗНАЧ!, #Н/Д, #ИМЯ?, #ПУСТО!, #ДЕЛ/0!, #ССЫЛКА!, #ЧИСЛО!)

Примеры формул с функцией И

Пример 1

В следующем примере показан список результатов теста.

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

Функция И использовалась для проверки того, что балл по каждому предмету выше среднего.

Результат ИСТИНА показан для учеников, которые набрали выше среднего во всех трех экзаменах.

=И(B2>=СРЗНАЧ($B$2:$B$11);C2>=СРЗНАЧ($C$2:$C$11);D2>=СРЗНАЧ($D$2:$D$11))

Пример 2

Родители выбирают имя для девочки, хотят, чтобы в имени присутствовали мягкие звуки (все три).

Excel не позволяет фильтровать строки по более чем двум критериям.

Но для этой задачи можно воспользоваться формулой массива с функцией И в сочетании с функцией ПОИСК и ЕЧИСЛО.

Обратите внимание, что для того, чтобы функция проверяла вхождения всех букв, это должна быть именно формула массива. Ее нужно вводить без фигурных скобок, но ввод в ячейку осуществлять с помощью сочетания клавиш Ctrl + Shift + Enter.

{=И(ЕЧИСЛО(ПОИСК(C$2:C$4;$A11)))}

Вот она точь-в-точь, выделена на скриншоте. Определяет, присутствуют ли в имени все три буквы:

В столбце H нам пригодилась функция ИЛИ - она пропускает имена, если они содержат ИСТИНА хотя бы по одному из наборов букв.

Другие логические функции

ЕСЛИ, ИЛИ, НЕ

Почему функция И работает не так — частые ошибки

• И внутри ФИЛЬТР или СУММПРОИЗВ даёт неверный результат. И сворачивает весь массив в одно значение. Для построчной проверки нескольких условий перемножайте их: (A2:A10>5)*(B2:B10="да").
• #ЗНАЧ!. В аргументах только текст или пустые ячейки. Проверьте, что сравниваете числа или логические значения.
• Текст в условии не срабатывает. Текст в формуле пишется в кавычках: B2="да", а не B2=да.
• Нужно «хотя бы одно» условие. Тогда это не И, а ИЛИ.

Функция И — частые вопросы

Как работает функция И в Excel?

Проверяет все условия и возвращает ИСТИНА, только если каждое выполнено: =И(A2>0;B2>0).

Как использовать И вместе с ЕСЛИ?

Вложите И в первый аргумент ЕСЛИ: =ЕСЛИ(И(A2>=80;B2="да");"зачёт";"незачёт").

Как проверить, что число находится между двумя значениями?

Формулой =И(A2>=10;A2<=20).

Чем И отличается от ИЛИ?

И требует выполнения всех условий, ИЛИ — хотя бы одного.

Как называется функция И на английском?

AND.

Итоги: функция И в Excel

Надеюсь, на этой странице вы нашли то, что искали. И — основной способ объединить условия в ЕСЛИ и условном форматировании.