Работа с текстовыми функциями в excel. Примеры текстовых функций в Excel

​ качестве аргумента указать​ ячейку, содержащую текст,​ – возвращает символы​ нужно найти;​ добавляем единицу (+1).​ формулу:​ все приведенные аргументы:​Старый текст.​

​Как пример можно привести​ (см. подробнее об​Автор: Антон Андронов​ указанной позиции. На​ форматировании.​Для объединения содержимого ячеек​60160​ПОИСКБ***​REPLACE​ число 2, а​ или текстовая строка,​ справа, а не​«просматриваемый текст» - где​ Получаем ту позицию,​​Если нужно вернуть прежние​ ​Необходимая строка представляет собой​Начальной позицией​​ следующую ситуацию. В​ ​ отображении чисел, дат​​Функция ТЕКСТ(), английская версия​ ​ рисунке ниже формула​Если регистр для Вас​

​ в Excel, наряду​Преобразования текстовых строк​SEARCHB​60167​ для получения последующих​ в которой требуется​ слева строки).​ искать;​ с которой будем​ числовые значения (без​Искомый текст.​указывается номер символа,​ ячейке А1 находится​

​ и времени). В​ TEXT(), преобразует число​ начинает просмотр с​




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

​ не играет большой​ с оператором конкатенации,​Делает все буквы в​-​Поиска и замены​ второго, третьего и​ выделить требуемое количество​В результате получим:​«начальная позиция» - с​ искать второй пробел.​ нулей), то используем​Данные, среди которых происходит​ с которого должна​

​ некоторый текст. Нужно​ файле примера приведен​ в текст и​ четвертого символа, т.е.​ роли (так бывает​

"СЦЕПИТЬ"

​ можно использовать текстовую​ тексте строчными​Работы с двухбайтовыми символами​Заменяет знаки в тексте​ прочих символов необходимо​ символов слева. Аргумент​Выделим часть текста ошибки:​ какой позиции начинать​Часть формулы – ПОИСК("​ оператор «--»:​ поиск первого аргумента,​ производиться замена (нумерация​ вернуть два последних​ наглядный пример: с​

  • ​ позволяет задать формат​
  • ​ c буквы «​ в большинстве случаев),​


​ функцию​СЦЕПИТЬ​Ищет вхождение одного текста​ЗАМЕНИТЬБ***​ указывать соответствующие числа,​

"ПСТР"

​ обязателен для заполнения.​В данном случае удобнее​ искать (по умолчанию​ ";A3;ПОИСК(" ";A3;1)+1) –​Обратите внимание, что значения​ являются​ идет слева на​ слова, общая длина​ форматированием и без​ отображения с помощью​

  • ​r​ то можно применить​СЦЕПИТЬ​CONCATENATE​
  • ​ в другой (без​ ​REPLACEB​ кратные двум. Для​[число_знаков] – необязательный для​
  • ​ использовать ПРАВСИМВ, которая​ ​ – 1).​ находит второй пробел.​ теперь отображаются в​Текстом для поиска.​ право).​


​ символов у них​ форматирования.​ специальных строк форматирования,​«. Но даже в​ формулу, просто проверяющую​. Она последовательно объединяет​60384​ учета регистра)​-​ языков с однобайтовой​ заполнения аргумент; числовое​ является, по сути,​

"ПОДСТАВИТЬ"

​Если нужно учитывать регистр,​ Это будет конечная​ числовом формате.​Начальная позиция​Новый текст​ 12. Символ, который​В файле примера приведено​ например, формула =ТЕКСТ(100;"0,00​ этом случае положение​ равенство двух ячеек.​ значения указанных ячеек​Манипулирования целыми строками​ПРАВБ***​Работы с двухбайтовыми символами​

​ кодировкой функции ЛЕВСИМВ​ значение, характеризующее количество​ зеркальным отражением ЛЕВСИМВ.​ используется функция НАЙТИ.​

​ позиция отчества.​

  • ​​не является обязательным​представляет собой строку,​ необходимо вернуть в​ также множество других​
  • ​ р.") вернет текстовую​ ​ символа считается относительно​Удаляет из текста все​ в одной строке.​Объединяет несколько текстовых элементов​
  • ​RIGHTB​ ​Заменяет знаки в тексте​ и ЛЕВБ возвращают​ символов, которые требуется​
  • ​ Для определения второго​ ​Функция ЛЕВСИМВ в Excel​Далее из общей длины​Отдельные текстовые функции и​ элементом. По умолчанию​ которой заменяется занесенная​ конкретной фразе, числится​ форматов.​ строку 100,00 р.,​ начала просматриваемого текста.​

"ЗАМЕНИТЬ"

​ лишние пробелы, кроме​Если в Excel необходимо​ в один​-​ЗНАЧЕН​ идентичные результаты.​ выделить из текстовой​ аргумента используется запись​ используется для получения​ строки отнимается количество​ их комбинации позволяют​



​ она имеет значение,​ ранее информация, которая​ под порядковым номером​В строки программы Microsoft​

  • ​ а формула =ТЕКСТ(1;"#%")​Текстовая функция​ одиночных между словами.​ сделать все буквы​​Т​
  • ​VALUE​Функции для работы​ строки.​ ДЛСТР(B5)-НАЙТИ(":";B5)-1, определяющая позицию​ части текста и​
  • ​ знаков с начала​ ​ распределить слова из​ равное единице.​ задавалась​ 12.​​ Office Exel можно​ ​ вернет текст (не​​ПОИСК​

​В случаях, когда наличие​ строчными, т.е. преобразовать​Т​Возвращает указанное количество символов​60081​ с текстом предназначены​Примечания 1:​ символа «u» в​ возвращает заданное количество​ строки до второго​ одной ячейки в​

​Автор: Ксения Медкова​Начальной позицией​Выполняет замену готового текста​ заносить любую информацию,​ число!) 100%. Эта​очень похожа на​ лишнего пробела в​ их в нижний​60178​ с конца текстовой​Преобразования типов данных​ для работы с​Если необязательный аргумента [число_знаков]​ слове «unknown» (единица​ символов текстовой строки,​ пробела. Результат –​ отдельные ячейки:​

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

"ДЛСТР"

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

"НАЙТИ"

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

​Преобразует аргумент в текст​ПРАВСИМВ​ число​ Кроме того функции​ по умолчанию будет​

​ пробела).​ символов начинается слева.​

  • ​ которые нужно вернуть.​​ – отображает заданное​
  • ​ существуют текстовые функции.​Теперь рассмотрим пример использования​ будет указано пользователем.​​ поставленных задач. Чтобы​
  • ​ в более удобном​ ​, основное их различие​ данная функция становится​СТРОЧН​ТЕКСТ​

Функции для работы с текстом в Excel

Примеры функции ТЕКСТ в Excel

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

​TEXT​60164​CODE​ работать и с​ строки. Например, функция​Пример 3. Создать функцию​ Excel содержатся имена​ по тем же​ начала ячейки;​ сразу сотен строк.​

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

​ что​ рисунке ниже видно,​ знаки, не являющиеся​

​60096​Извлечения символов из строк​60169​ числовыми значениями, то​ =ЛЕВСИМВ(«текст») вернет значение​ для округления дробных​ переменных вместе с​ принципам:​

​ПРАВСИМВ (текст; кол-во знаков)​ Рассмотрим некоторые из​

​ А1 содержится строка​ "Заменить". Но о​ с данными в​ с текстом или​ПОИСК​ что содержимое ячеек​ буквами.​Преобразования типов данных​Возвращает указанное количество символов​Преобразования типов данных​ есть обрабатывать числа​ «т».​

​ чисел до ближайших​ текстом, определяющим их​Для объединения значений из​ – возвращает заданное​ них на примерах.​ со словом "старый".​ ней подробности будут​ строках. В этом​

​ символами.​не чувствительна к​ А1 и B1​Текстовая функция​

​Форматирует число и преобразует​ с конца текстовой​Определяет числовой код первого​

​ как текст и​

Функция разделения текста в Excel

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

  • ​Преобразует числа в текст.​ Оно начинается с​ представлены позже. Главное​ материале будут рассмотрены​
  • ​Синтаксис​ регистру.​ абсолютно одинаково, но​ПРОПИСН​
  • ​ его в текст​ строки​ знака в текстовой​ текст как числа.​ качестве необязательного аргумента,​ использованием формул для​ выделить только названия​

​ одну строку используется​ конца ячейки;​ Синтаксис: значение (числовое​ 19-го символа и​ отличие данного варианта​

​ примеры текстовых функций​ТЕКСТ(значение; формат)​Заменяет определенный текст или​ это не так.​



​делает все буквы​ФИКСИРОВАННЫЙ​ПРОПИСН​ строке​Список текстовых функций:​ должно быть взято​ работы с текстом.​ переменных и записать​ оператор амперсанд (&)​ПОИСК (искомый текст; диапазон​ или ссылка на​ имеет длительность из​

​ в том, что​



​ в Excel.​Значение.​ символ на требуемое​ В ячейке А1​ прописными, т.е. преобразует​FIXED​UPPER​ЛЕВБ***​Функция​

​ из диапазона неотрицательных​Форма для округления числовых​ их в отдельный​ или функция СЦЕПИТЬ.​

​ для поиска; начальная​ ячейку с формулой,​ шести идентичных единиц.​ он автоматически вычисляет​Чтобы найти их в​ Численное значение или​ значение. В Excel​ мы намеренно поставили​ их в верхний​60062​60161​LEFTB​Function​ чисел, то есть​

​ значений имеет следующий​ столбец.​Например, значения расположены в​ позиция) – показывает​ дающей в результате​

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

​Преобразования типов данных​Преобразования текстовых строк​-​



Функция объединения текста в Excel

​id​ должно быть равным​ вид:​Исходная таблица данных:​ разных столбцах (ячейках):​

​ позицию первого появления​ число); формат (для​

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

​Ставим курсор в ячейку,​ искомого знака или​

​ отображения числа в​

​Стоит отметить, что данная​Выглядит конкретная функция следующим​ "Формулы", откройте список​Формат.​применяют, когда заранее​

Функция ПОИСК текста в Excel

​Excel​СТРОЧН​ его в текст​ тексте прописными​

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

​ строки при просмотре​ виде текста).​ ситуация достаточно двоякая.​ образом: текст; старый​ и выберите "Текстовые".​ Текстовая строка определенного​ известно какой текст​. В итоге функция​

​, не заменяет знаки,​ с заданным числом​ПРОПНАЧ​

​ от начала текстовой​ASC***​ случае будет возвращен​

​=5,ЛЕВСИМВ(A2+1,НАЙТИ(".",A2)-1),ЛЕВСИМВ(A2,НАЙТИ(".",A2)-1)),"Число не введено")"​

  • ​ название переменной, используем​ объединенные три значения.​
  • ​ слева направо​Самая полезная возможность функции​
  • ​ С одной стороны,​ текст; новый текст;​Данная функция дает пользователю​ вида (например, "Д.М.ГГГГ"​

​ необходимо заменить, а​СОВПАД​

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

​ не являющиеся буквами.​ десятичных знаков​PROPER​ строки​ASC​ код ошибки #ЗНАЧ!.​ class="formula">​

Как вырезать часть текста из строки функцией ЛЕВСИМВ в Excel?

​ формулу:​ Вводим равно. Выбираем​При разделении текста в​ ТЕКСТ – форматирование​ если пользователь будет​ номер вхождения.​ возможность объединять между​ для вывода числа​ не его местоположение.​

​возвратила нам значение​

​Текстовая функция​Ф.ТЕКСТ​60162​ЛЕВСИМВ​



​-​Если в качестве аргумента​Логика работы:​Второй равен 1 по​ первую ячейку с​ строке учитывается положение​ числовых данных для​

​ работать с небольшим​Разберем каждый из аргументов:​ собой несколько строк.​ в формате даты​Приведенная ниже формула заменяет​ ЛОЖЬ.​ПРОПНАЧ​

​FORMULATEXT​

Как вытащить часть текста из ячейки в Excel используя функцию ЛЕВСИМВ?

​Преобразования текстовых строк​LEFT​Работы с двухбайтовыми символами​ [число_знаков] будет переданы​Первая функция ЕСЛИ проверяет​ умолчанию, поэтому функция​ текстом и нажимаем​ каждого знака. Пробелы​ объединения с текстовыми​

​ количеством ячеек, то​



​ или "# ##0,00"​

  • ​ все вхождения слова​Применив функцию​делает прописной первую​Возвращает текст формулы​Делает прописной первую букву​60163​Преобразует полуширинные (однобайтовые) английские​ текстовые данные, функция​ с помощью формулы​ возвращает первый символ.​ на клавиатуре &.​ показывают начало или​
  • ​ данными. Без использования​ данный вариант отлично​ на определенную ячейку​ соединения количество достигает​ для вывода в​ «Excel» на «Word»:​СЖПРОБЕЛЫ​

​ букву каждого слова,​



​** - недокументированная функция​

​ в каждом слове​Извлечения символов из строк​ буквы в полноширинные​ ЛЕВСИМВ вернет код​ ЕПУСТО, заполнена ли​ Аналогично выделим названия​ Затем – знак​ конец искомого имени.​ функции Excel «не​ сработает. Но как​ или строка, которая​

​ 255. Обратите внимание​



Округление чисел текстовыми функциями в текстовой строке

​ числовом формате с​Заменяет только первое вхождение​к значению ячейки​ а все остальные​ Excel​ текста​

​Возвращает указанное количество символов​ (двухбайтовые)​ ошибки #ЗНАЧ!.​



​ ячейка, которая должна​ переменных из других​

​ пробела, заключенный в​Распределим с помощью функций​

​ понимает», как показывать​

  1. ​ быть в ситуации,​ содержит эту самую​ на то, что​ разделителями разрядов и​ слова «Excel»:​ А1, мы удалим​
  2. ​ преобразует в строчные.​*** - Функции подкатегории​ПСТР​ от начала текстовой​JIS***​Если число, переданное в​
  3. ​ содержать значение, которое​ строк. В результате​ кавычки (“ “).​ имя, фамилию и​ числа, и преобразует​ когда предстоит выполнить​ информацию.​
  4. ​ проставлять пробелы здесь​

​ двумя знаками после​Удаляет все пробелы из​ из него все​Каждая первая буква, которая​работы с двухбайтовыми символами​MID​ строки​-​ качестве необязательного аргумента,​ требуется округлить.​ получим:​ Снова - &.​

Функция ЛЕВСИМВ в Excel и особенности ее использования

​ отчество в разные​ их в базовый​ замену среди нескольких​Старый текст​ необходимо самостоятельно. В​ запятой. Правила форматирования​ текстовой строки:​ лишние пробелы и​

​ следует за знаком,​

​(за исключением​

  • ​60079​НАЙТИ​-​ превышает количество знаков​Если число введено, запись​Как видно на рисунке​ И так последовательно​
  • ​ столбцы.​ формат.​ тысяч строк? В​- исходный вариант​ теле "СЦЕПИТЬ" должен​ см. ниже.​

​Заменяет символы, расположенные в​

  1. ​ получим корректный результат:​ отличным от буквы,​ASC​Извлечения символов из строк​FIND​Работы с двухбайтовыми символами​ в текстовой строке,​
  2. ​ ЧЗНАЧ(ПРАВСИМВ(ЛЕВСИМВ(B2;НАЙТИ(",";B2)+1)) возвращает числовое​ с помощью функции​ соединяем ячейки с​В первой строке есть​Покажем на примере. Допустим,​ таком случае стоит​ данных, хранящихся в​ содержаться минимум один​Форматов для отображения чисел​ заранее известном месте​
  3. ​Функцию​ также преобразуется в​,​Извлекает определенное число знаков​60172​
  4. ​Преобразует однобайтовые английские буквы​ рассматриваемая функция вернет​ значение первого символа​ ЛЕВСИМВ получилось вытащить​ текстом и пробелы.​ только имя и​
  5. ​ нужно объединить текст​ обратиться к двум​ ячейке, которые должны​ аргумент. Рассмотрим пример​ в MS EXCEL​ строки, на требуемое​СЖПРОБЕЛЫ​
  • ​ верхний регистр.​JIS​ из строки текста,​
  • ​Поиска и замены​ или знаки катакана​ весь текст целиком.​ после запятой и​
  • ​ левую часть текста​Получаем в одной ячейке​ фамилия, разделенные пробелом.​ в строках и​ другим текстовым функциям​
  • ​ быть заменены.​ ее записи и​

​ много (например, см.​ значение. В Excel​полезно применять к​В Excel Вы можете​и​ начиная с указанной​Ищет вхождение одного текста​ (японское письмо) в​Excel выполняет автоматическое преобразование​ сравнивает полученное значение​ из каждой ячейки​ объединенные значения:​ Формула для извлечения​ числовые значения:​ Excel, которые будут​Новый текст​ действия:​ здесь), также имеются​ текстовую функцию​ данным, которые импортируются​ подсчитать количество знаков,​PHONETIC​ позиции​ в другой (с​

Функции текстовые.

​ двухбайтовые​ типов данных там,​ с числом 5.​ по одному (первому)​Использование функции СЦЕПИТЬ:​ имени: =ЛЕВСИМВ(A2;ПОИСК(" ";A2;1)).​Использование амперсанда без функции​ рассмотрены чуть позже:​является строкой, необходимой​=СЦЕПИТЬ("Аргумент1";"";"Аргумент2");​ форматы для отображения​
​ЗАМЕНИТЬ​
​ в рабочие листы​ ​ содержащихся в текстовой​ ​) являются полными аналогами​ ​ПСТРБ***​ ​ учетом регистра)​
​PHONETIC***​
 ​ где это возможно.​ ​Если полученное значение больше​ ​ символу.​
 ​С помощью кавычек в​ Для определения второго​ ТЕКСТ дает «неадекватный»​
​Начальную позицию​
 ​ для выполнения смены​ ​В итоге получится следующая​ ​ дат (например, см.​ ​применяют, когда известно​ Excel из внешних​ строке, для этого​ соответствующих функций для​
​MIDB​
 ​НАЙТИБ***​
 ​-​ ​ Например, рассмотрим некоторые​ ​ 5, будет возвращен​​ формуле можно добавить​
​ аргумента функции ЛЕВСИМВ​
 ​ результат:​
 ​будем заменять на​ ​ старого варианта данных.​ ​ строка: "Аргумент1 Аргумент2".​ здесь). Также приведено​ где располагается текст,​
​ источников. Такие данные​
 ​ воспользуйтесь функцией​
 ​ работы с однобайтовыми​ ​-​ ​FINDB​-​
​ возможные варианты записи​
 ​ результат выполнения функции​ ​Пример 2. В таблицу​ ​ в конечное выражение​ ​ – количества знаков​Excel вернул порядковый номер​
​ "НАЙТИ".​
 ​Номер вхождения​
 ​Альтернативой данной функции для​
 ​ много форматов.​ ​ при этом сам​ очень часто содержат​
​ДЛСТР​
 ​ символами. Функции этой​ ​Работы с двухбайтовыми символами​
 ​-​ ​Работы с двухбайтовыми символами​
​ функции ЛЕВСИМВ и​ ​ ЛЕВСИМВ(B2+1;НАЙТИ(",";B2)-1), то есть​ ​ Excel подтягиваются данные​
 ​ любой знак или​ ​ – используется функция​
​ для даты и​
 ​Количество знаков​
 ​представляет собой необязательный​ ​ работы с текстом​ ​Функция ТЕКСТ() преобразует число​ он не важен.​
​ лишние пробелы и​
 ​. Пробелы учитываются.​
 ​ подкатегории отсутствуют в​
 ​Извлекает определенное число знаков​ ​Работы с двухбайтовыми символами​Преобразует графическое японское письмо​ результаты ее выполнения:​
​ целой части числа​
 ​ из другого программного​
 ​ текст.​
 ​ ПОИСК. Она находит​ ​ общий формат вместо​изменится на "ДЛСТР".​ аргумент в данной​
​ является символ &,​
 ​ в форматированный текст​
 ​Формула в примере ниже​ ​ различные непечатаемые символы.​ ​Текстовые функции​ мастере функций и​ из строки текста,​
​Ищет вхождение одного текста​
 ​ (катакана) в фонетические​
 ​ЛЕВСИМВ(«текст»;200%) вернет значение «те»​ ​ +1 по правилам​
 ​ продукта. Одно из​Функция ПОИСК возвращает начальную​ пробел в ячейке​
​ денежного. Чтобы избежать​
 ​Данная текстовая функция в​
 ​ текстовой функции в​
 ​ который также отвечает​ ​ и результат больше​ заменяет 4 символа,​ Чтобы удалить все​
​ЛЕВСИМВ​
 ​ вводятся только вручную.​
 ​ начиная с указанной​
 ​ в другой (с​
 ​ знаки (фуригана)​ (преобразование процентов в​
​ округления.​
 ​ полей данных получило​
 ​ позицию искомого текста​
 ​ А2, начиная слева.​ ​ подобного результата, применяется​ Excel помогает пользователю​
​ Excel. При его​
 ​ за объединение строк.​
 ​ не может быть​
 ​ расположенные, начиная с​ ​ непечатаемые символы из​и​
​ Кроме того, для​
 ​ позиции​
 ​ учетом регистра)​
 ​БАТТЕКСТ​ ​ числовое значение);​Если​ ошибку. Для обработки​
​ (без учета регистра).​
 ​Формула для извлечения фамилии:​ ​ функция ТЕКСТ. Она​
 ​ определять длину строки.​ ​ указании будут заменены​Она отвечает за возвращение​ использован в вычислениях​
​ седьмой позиции, на​
 ​ текста, необходимо воспользоваться​
 ​ПРАВСИМВ​ ​ их работы необходимы​ ​РУБЛЬ​ПЕЧСИМВ​-​
​при аргументах: («текст»;ИСТИНА) вернет​
 ​Примечание: данный алгоритм нельзя​ ​ ошибки необходимо выделить​ ​ Например:​
 ​С помощью функции ПОИСК​ форматирует значения по​ Результатом ее работы​
​ только совпадающие вхождения.​
 ​ из требуемой строки​
 ​ в качестве числа.​ ​ значение «2013». Применительно​ ​ функцией​возвращают заданное количество​
​ соответствующие языковые настройки​
 ​DOLLAR​
 ​CLEAN​ ​-​ ​ «т» (логическое ИСТИНА​ назвать гибким и​ ее код и​
​Функция ПОИСК вернула позицию​
 ​ Excel определяет количество​
 ​ заданию пользователя.​ ​ будет возврат целого​ ​ В случае его​ куска текста в​ Чтобы отформатировать число,​ к нашему примеру,​
​ПЕЧСИМВ​
 ​ символов, начиная с​
 ​ системы. В зависимости​ ​60061​
​ описание из строки.​
 ​ 10, т.к. слово​
 ​ знаков для функции​ ​Формула «для даты» теперь​ ​ числа, демонстрирующего объем​ отсутствия смена произойдет​
​ указанном количестве символов.​
 ​ но при этом​
 ​ формула заменит «2010»​ ​.​ ​ начала или с​ от языковых параметров,​
​Преобразования типов данных​
 ​Преобразования текстовых строк​
 ​Преобразует чисто в текст​ ​ 1);​ ​ недостатки (например, не​Исходные данные:​
​ «Захар» начинается с​
 ​ ПРАВСИМВ. Функция ДЛСТР​
 ​ выглядит так:​ ​ символов в тексте.​ ​ везде.​
​ Строка начинается с​
 ​ оставить его числом​
 ​ на «2013».​ ​Функция​ ​ конца строки. Пробел​ функция​
​Преобразует число в текст,​
 ​Удаляет все непечатаемые знаки​
 ​ (бат) на тайском​ ​при аргументах: («текст»;ЛОЖЬ) вернет​ ​ учтен вариант ввода​Выделим код ошибки с​
​ десятого символа в​ ​ «считает» общую длину​ ​Второй аргумент функции –​ ​ Выглядит она так:​ ​Выполняет функцию замены части​
​ отмеченного символа. Ключевые​
 ​ (с которым можно​ ​Заменяет первые пять символов​ ​ПОВТОР​ ​ считается за символ.​JIS​
​ используя денежный формат​
 ​ из текста​
 ​ языке​ ​ пустое значение «»,​ ​ сразу целого числа).​ помощью следующей формулы:​ строке. Где это​ текста. Затем отнимается​
​ формат. Где брать​
 ​ =ДЛСТР(текст).​

​ строки в конкретном​

​ позиции (текст, начальная​ выполнять арифметические действия),​
​ текстовой строки, т.е.​​повторяет текстовую строку​ ​Текстовая функция​​может иметь другое​ ​СЖПРОБЕЛЫ​​ПОВТОР​ ​ДЛИНБ***​​ поскольку логическое ЛОЖЬ​ ​ Он приведен в​Описание:​ может пригодиться?​ количество знаков до​ строку формата? Щелкаем​При работе с текстом​ количестве символов. Процесс​ позиция, количество знаков).​ щелкните ячейку правой​ слово «Excel», на​ указанное количество раз.​ПСТР​ название.​ ​TRIM​ ​REPT​LENB​

Программа Excel предлагает своим пользователям целых 3 функции для работы с большими и маленькими буквами в тексте (верхний и нижний регистр). Эти текстовые функции делают буквы большими и маленькими или же изменяют только первую букву в слове на большую.

Формулы с текстовыми функциями Excel

Сначала рассмотрим на примере 3 текстовых функции Excel:

  1. ПРОПИСН – данная текстовая функция изменяет все буквы в слове на прописные, большие.
  2. СТРОЧН – эта функция преобразует все символы текста в строчные, маленькие буквы.
  3. ПРОПНАЧ – функция изменяет только первую букву в каждом слове на заглавную, большую.

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

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

Чтобы решить эту популярную задачу нужно в формуле использовать дополнительные текстовые функции Excel: ЛЕВСИМВ, ПРАВСИМВ и ДЛСТР.



Принцип действия формулы для замены первой буквы в предложении

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

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


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

  1. Текст – ссылка на ячейку с исходным текстом.
  2. Количесвто_знаков – число возвращаемых символов с левой стороны (с начала) исходного текста.

В данном примере необходимо получить только 1 первый символ из исходной текстовой строки в ячейке A1. Далее полученный символ преобразуется в прописную большую букву верхнего регистра.

Правая часть формулы после оператора & очень похожа по принципу действия на левую часть, только она решает другую задачу. Ее задача – преобразовать все символы текста в маленькие буквы. Но сделать это нужно так чтобы не изменять первую большую букву, за которую отвечает левая часть формулы. В место функции ЛЕВСИМВ в правой части формулы применяется функция ПРАВСИМВ:


Текстовая функция ПРАВСИМВ работает обратно пропорционально функции ЛЕВСИМВ. Так же требует запыления двух аргументов: исходный текст и количество знаков. Но возвращает она определенное число букв, полученных с правой стороны исходного текста. Однако в данном случаи мы в качестве второго аргумента не можем указать фиксированное значение. Ведь нам заранее неизвестно количество символов в исходном тексте. Кроме того, длина разных исходных текстовых строк может отличаться. Поэтому нам необходимо предварительно подсчитать длину строки текста и от полученного числового значения отнять -1, чтобы не изменять первую большую букву в строке. Ведь первая буква обрабатывается левой частью формулы и уже преобразована под требования пользователя. Поэтом на нее недолжна влиять ни одна функция из правой части формулы.

Для автоматического подсчета длины исходного текста используется текстовая функция Excel – ДЛСТР (расшифроваться как длина строки). Данная функция требует для заполнения всего лишь одного аргумента – ссылку на исходный текст. В результате вычисления она возвращает числовое значение, попетому после функции =ДЛСТР(A1) отнимаем -1. Что дает нам возможность не затрагивать первую большую букву правой частью формулы. В результате функция ПРАВСИМВ возвращает текстовую строку без одного первого символа для функции СТРОЧН, которая заменяет все символы текста в маленькие строчные буквы.


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

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

Чтобы найти их в программе, проследуйте во вкладку под названием "Формулы", откройте список и выберите "Текстовые".

"СЦЕПИТЬ"

Данная функция дает пользователю возможность объединять между собой несколько строк. Максимально допустимое для соединения количество достигает 255. Обратите внимание на то, что проставлять пробелы здесь необходимо самостоятельно. В теле "СЦЕПИТЬ" должен содержаться минимум один аргумент. Рассмотрим пример ее записи и действия:

  • =СЦЕПИТЬ("Аргумент1";"";"Аргумент2");
  • В итоге получится следующая строка: "Аргумент1 Аргумент2".

Альтернативой данной функции для работы с текстом является символ &, который также отвечает за объединение строк.

"ПСТР"

Она отвечает за возвращение из требуемой строки куска текста в указанном количестве символов. Строка начинается с отмеченного символа. Ключевые позиции (текст, начальная позиция, количество знаков). Каждый из них определяется следующим образом:

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

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

"ПОДСТАВИТЬ"

Выполняет замену готового текста на необходимый в том месте, которое будет указано пользователем. Чем-то похожа на функцию под названием "Заменить". Но о ней подробности будут представлены позже. Главное отличие данного варианта в том, что он автоматически вычисляет вхождения требуемой строки и выполняет замену.

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

Разберем каждый из аргументов:

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

"ЗАМЕНИТЬ"

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

Рассмотрим по отдельности каждый из аргументов данной текстовой функции в Excel:

  • Ссылка на конкретную ячейку или строка, содержащая занесенную ранее информацию, называется Старый текст.
  • Начальной позицией указывается номер символа, с которого должна производиться замена (нумерация идет слева на право).
  • Новый текст представляет собой строку, которой заменяется занесенная ранее информация, которая задавалась Начальной позицией и Количеством знаков.

Теперь рассмотрим пример использования текстовой функции в Excel. В ячейке А1 содержится строка со словом "старый". Оно начинается с 19-го символа и имеет длительность из шести идентичных единиц. Замена происходит на слово "новый".

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

  • Начальную позицию будем заменять на "НАЙТИ".
  • Количество знаков изменится на "ДЛСТР".

"ДЛСТР"

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

"НАЙТИ"

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

Вот ее форма в документе. Но без конкретных аргументов: =НАЙТИ(искомый текст; текст для поиска; нач. позиция).

Теперь рассмотрим по отдельности все приведенные аргументы:

  • Необходимая строка представляет собой Искомый текст.
  • Данные, среди которых происходит поиск первого аргумента, являются Текстом для поиска.
  • Начальная позиция не является обязательным элементом. По умолчанию она имеет значение, равное единице.

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

Описание функции ТЕКСТ

Функция ТЕКСТ преобразует численное значение в текст и позволяет задать формат отображения с помощью специальных строк форматирования. Эта функция полезна, если числа требуется отобразить в более удобном формате или если требуется объединить числа с текстом или символами.

Например, чтобы отформатировать число 23,5 как сумму в рублях, можно использовать указанную ниже формулу:

ТЕКСТ(A1,"0,00р.")

Результат: 23,50р.

Числа также можно форматировать с помощью команд в группе Число, которая находится на вкладке ленты Главная. Однако эти команды работают лишь в том случае, если ячейка содержит только число. Если требуется отформатировать число и объединить его с другим текстом, лучше всего использовать функцию ТЕКСТ. Например, к предыдущей формуле можно добавить следующий текст:

ТЕКСТ(A1,"0,00р.")& " в час"

Результат: 23,50р. в час .

Синтаксис

=ТЕКСТ(значение; формат)

Аргументы

значение формат

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

Обязательный аргумент. Численный формат в виде текстовой строки в кавычках (например, «Д.М.ГГГГ» или «# ##0,00». Правила форматирования представлены ниже.

Форматы чисел

Заполнитель Описание
0 (ноль) Отображает незначащие нули, если число имеет меньше разрядов, чем количество нулей в строке форматирования. Например, формат #,00 позволяет отобразить значение 8,9 как 8,90.
# Работает так же, как 0 (ноль). Но в этом случае функция Text не возвращает дополнительные нули, если число имеет слева или справа от десятичного разделителя меньше цифр, чем количество символов # в строке форматирования. Например, число 8,9 при использовании формата #,## будет отображаться как 8,9.
? Действует по тем же правилам, что и 0 (нуль). Однако, до и после запятой вместо незначащих нулей отображаются пробелы, чтобы десятичные разделители были выровнены в столбце. Например, формат 0,0? обеспечивает выравнивание запятых для чисел 8,9 и 88,99. (Правда, для чистоты эксперимента, следует выбрать )
, (запятая) Отображает символ десятичного разделителя. Поведение этого символа зависит от языка пользовательского формата.

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

Разделитель групп разрядов

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

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

Формат Описание
М Отображение месяца в виде числа без начального нуля.
ММ Отображение месяца в виде числа с начальным нулем, если он необходим.
МММ Отображение сокращенного названия месяца (Янв–Дек).
ММММ Отображение полного названия месяца (Январь–Декабрь).
МММММ Отображение месяца в виде одной буквы (Я–Д).
Д Отображение дня в виде числа без начального нуля.
ДД Отображение дня в виде числа с начальным нулем, если он необходим.
ДДД Отображение сокращенного названия дня недели (пн–вс).
ДДДД Отображение полного названия дня недели (понедельник–воскресенье).
ГГ Отображение года в виде двузначного числа.
ГГГГ Отображение года в виде четырехзначного числа.

Часы, минуты и секунды

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

Формат Описание
ч Отображение часов в виде числа без начального нуля.
[ч] Отображение истекшего времени в часах. Если формула возвращает значение времени, в котором число часов превышает 24, используйте числовой формат наподобие следующего: [ч]:мм:сс.
чч Отображение часов в виде числа с начальным нулем, если он необходим. Если формат содержит пометку AM или PM, часы отображаются на основе 12-часового цикла. В противном случае используется 24-часовой цикл.
м Отображение минут в виде числа без начального нуля.

Примечание.

[м] Отображение истекшего времени в минутах. Если формула возвращает значение времени, в котором число минут превышает 60, используйте числовой формат наподобие следующего: [мм]:сс.
мм Отображение минут в виде числа с начальным нулем, если он необходим.

Примечание. Код м или мм должен быть указан непосредственно после кода ч или чч или непосредственно перед кодом сс, иначе вместо минут отображается месяц.
с Отображение секунд в виде числа без начального нуля.
[с] Отображение истекшего времени в секундах. Если формула возвращает значение времени, в котором число секунд превышает 60, используйте числовой формат наподобие следующего: [сс].
сс Отображение секунд в виде числа с начальным нулем, если он необходим. Для отображения долей секунды используйте числовой формат наподобие следующего: ч:мм:сс,00.
AM/PM, am/pm, A/P, a/p Отображение часов на основе 12-часового цикла. Коды AM, am, A и a означают время суток от полуночи до полудня, а PM, pm, P и p - время от полудня до полуночи.

Форматы денежных сумм, процентов и экспоненциального представления

Символы валют. Чтобы указать перед числом знак доллара ($), введите его в начале аргумента формат (например, «$# ##0,00»). Чтобы включить в числовой формат один из указанных ниже символов валют, нажмите клавишу NUM LOCK и введите с цифровой клавиатуры код ANSI соответствующего символа.

  • Примечание . Необходимо использовать цифровую панель клавиатуры. При использовании клавиши ALT с цифрами верхнего ряда клавиш коды ANSI не генерируются.
  • Процентные значения. Чтобы отображать числа в виде процентов - например, 0,08 как 8% или 2,8 как 280%, - включите в аргумент формат знак процента (%).
  • Экспоненциальное представление. Для отображения чисел в экспоненциальном формате включите в аргумент формат код из числа указанных ниже.

Включение текста и добавление пробелов

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

Замечания

  • Аргумент формат не может содержать звездочку (*).
  • Функция ТЕКСТ преобразует число в форматированный текст, и результат больше не может быть использован в вычислениях в качестве числа. Чтобы отформатировать число и при этом оставить его числом, щелкните ячейку правой кнопкой мыши, выберите команду Формат ячеек и в диалоговом окне Формат ячеек на вкладке Число настройте нужные параметры форматирования. Для получения дополнительных сведений об использовании диалогового окна Формат ячеек нажмите кнопку справки (?) в верхнем правом углу окна.

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

Аргумент – это значение, которое предоставляет исходную информацию для функции.

Список текстовых функций

По любой из перечисленных функций можно получить справочную информацию с описанием, синтаксисом и примером, если в мастере функций выбрать интересующую функцию и кликнуть ссылку «Справка по этой функции».

Быстрое использование функций

"БАТТЕКСТ"

В справке к функции "БАТТЕКСТ" написано, что в панели управления, в разделе «Язык и региональные стандарты» можно изменить формат «бат» на другой стиль, но сделать это, как правило, никому не удается. В связи с этим, аналог этой функции на русском языке написан на встроенном в приложения Office языке программирования VisualBasicforApplications и распространяется в виде надстройки.

«ЛЕВСИМВ» и «ПРАВСИМВ»

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

"ЗНАЧЕН"

Число в Excel может быть отформатировано, как текст. С такими числами нельзя совершить никаких математических действий, они воспринимаются приложением Excel как обычный текст. Число, отформатированное как текст, можно получить, например, поставив апостроф перед числовым значением или установить в ячейке текстовый формат, а затем внести в него числовое значение. Для преобразования таких чисел в настоящие числа как раз и используется функция «ЗНАЧЕН». Кроме того, преобразовать текст в число можно без использования формулы, при помощи надстройки, которая в заданном диапазоне находит числа, отформатированные как текст и трансформирует их в настоящие числа .

«ПЕЧСИМВ», «ПРОПИСН», «ПРОПНАЧ», «СЖПРОБЕЛЫ», «СТРОЧН»

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