Excel функция строка

Вариант 2: меняем первую букву каждого слова на заглавную

В некоторых ситуациях требуется сделать заглавными первые буквы каждого слова в выбранных ячейках таблицы. На этот случай в Эксель предусмотрена специальная функция ПРОПНАЧ, использование которой значительно проще, нежели реализация метода, описанного выше.

Формула функции ПРОПНАЧ выглядит так:

Давайте посмотрим, как она работает на примере нашей таблицы.

1. Встаем в любую свободную ячейку листа и нажимаем кнопку “Вставить функцию” (fx), находящуюся слева от строки формул.
2. Откроется окно вставки функци, в котором выбираем оператор “ПРОПНАЧ” (категория “Текстовые”) и щелкаем OK.
3. Заполняем единственный аргумент функции – “Текст”. Сделать это можно вручную, прописав с помощью клавиш клавиатуры координаты ячейки, содержимое которой нужно обработать. Или находясь курсором в поле для ввода данных просто кликнуть по нужной ячейке в самой таблице. По готовности нажимаем OK.
4. В ячейке с функцией будет выведен требуемый результат.
5. Воспользовавшись маркером заполнения копируем формулу ровно на столько строк, сколько содержится в исходном столбце, в ячейках которого мы хотим произвести замену букв. Как это делается – описано в первом методе (шаг 3).
6. Выделяем и копируем полученный диапазон данных, например, нажав сочетание клавиш CTRL+C или воспользовавшись любым другим удобным способом (контекстное меню, кнопка на ленте программы).
7. Скопированные данные вставляем в соответствующее место исходной таблицы (только “Значения”) предварительно выделив нужный диапазон или встав в ячейку, которая станет самым верхним левым элементом, начиная с которого будут вставлены скопированные данные.
8. Промежуточные данные можно удалить, выбрав сначала в контекстном меню команду “Удалить”, затем в небольшом окошке, которое откроется следом – пункт “строку” или “со сдвигом влево” (если справа от выделенного диапазона будут пустые ячейки).
9. Вот и все, что требовалось сделать для того, чтобы заменить первую букву каждого слова на прописную.

Функция СТРОКА() в MS EXCEL

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

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

​ целых значений с​​ ее код и​

​ используется для получения​​Формула​Аргумент «нач_позиция» можно использовать,​ с которого нужно​

• ​ языков с двухбайтовой​.​ номеров строк. Для​Описание​СТРОКА​ и задаем необходимый​ начальную позицию найденной​​ фразу, длину которой​​ число 2, а​ ЛЕВСИМВ вернет код​

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

Использование функции в формулах массива

​Описание​ чтобы пропустить нужное​ начать поиск. Первый​ кодировкой. Заданный на​Записав в правиле Условного​ этого введите в​Результат​в Microsoft Excel.​ нам формат выбранных​ строки.​ необходимо определить, дальше​ для получения последующих​ ошибки #ЗНАЧ!.​ для работы с​​ работы с текстом.​​Исходные данные:​

​ возвращает заданное количество​Результат​ количество знаков. Предположим,​ знак в тексте​ компьютере язык по​ форматирования для диапазона​ Строке формул выражение​

​=СТРОКА()​Возвращает номер строки, определяемой​ полей. Ориентировочный вид​Полученные результаты проиллюстрированы на​​ выбираем функцию ДЛСТР.​​ второго, третьего и​Если число, переданное в​ текстовыми строками в​Форма для округления числовых​Выделим код ошибки с​​ символов текстовой строки,​​=НАЙТИ(«В»;A2)​

​ например, что для​ «просматриваемый_текст» имеет номер​ умолчанию влияет на​A5:A18​ =СТРОКА(F8:F18). Выделите формулу​Строка формулы​​ ссылкой.​​ после заполнения данного​ рисунке 2.​ В качестве текста​ прочих символов необходимо​

​ качестве необязательного аргумента,​ Excel (ПРАВСИМВ, СИМВОЛ,​ значений имеет следующий​ помощью следующей формулы:​​ при этом отсчет​​Позиция первой «В» в​

Использование функции для чрезстрочного выделения

​ поиска строки «МДС0093.МесячныеПродажи»​ 1. Если номер​​ возвращаемое значение указанным​​формулу =НЕЧЁТ(СТРОКА())=СТРОКА() получим​ и нажмите​2​СТРОКА()​

Формирование списка без повторов

​ окна показан выше​Рисунок 2 – Результат​​ выбираем ячейку с​​ указывать соответствующие числа,​ превышает количество знаков​ СЦЕПИТЬ, ДЛСТР и​ вид:​​Описание:​​ символов начинается слева.​ ячейке A2​ используется функция НАЙТИ.​

excel2.ru>

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

Функция ПРАВСИМВ (на английском RIGHT) является кладезем для всех сотрудников, которые работают с массивами и выборками текстовых данных.

Для функции достаточно указать два аргумента:

1. Текст – исходная ячейка с данными.
2. Количество знаков — длина вырезанного фрагмента текста с правой стороны.

Рассмотрим на примере работу функции ПРАВСИМВ в Excel. Главный бухгалтер выгрузил из базы 1С Оборотно-сальдовую ведомость, представленную ниже:

Задача: для отчета необходимо из столбца А с помощью функции ПРАВСИМВ вытащить только номера договоров.

1. В столбец H вынесем номера договоров. Щелкнем в ячейку Н3 и нажмем кнопку мастер функций fx или комбинацию горячих клавиш SHIFT+F3. В окне мастера функций выберем категорию Текстовые, найдем функцию ПРАВСИМВ и нажмем кнопку ОК.
2. На следующем шаге откроется окно аргументов функции ПРАВСИМВ. В поле текст укажем ячейку А3 с которой надо начинать действие, в поле количество знаков укажем 14, именно из такого количества состоит номер договора. Нажмем кнопку ОК.
3. В результате в ячейке Н3 появится «№2016/07-29/1» без «Договор субподряда». Для того чтобы формула применилась к остальным ячейкам протянем ее за уголочек в низ.

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

Пример 1: Проверка пароля

Представьте, что Вы ввели секретный пароль в одну из ячеек своей книги Excel и назвали эту ячейку pwd.

На рисунке ниже секретный пароль находится в ячейке C2 с именем pwd. Она располагается на листе AdminData, который может быть скрыт от пользователей.

На другом листе пользователи будут вводить пароль, а Вы проверять его, используя функцию EXACT (СОВПАД).

• На листе Ex01 пользователь будет вводить пароль в ячейку C3.

• В ячейке C5 с помощью оператора равенства (=) сравниваем значения в ячейках C3 и pwd:

• В ячейке C6 функция EXACT (СОВПАД) сравнивает ячейки C3 и pwd с учётом регистра:

Если содержимое двух ячеек совпадает, включая регистр, то результатом в обоих случаях будет TRUE (ИСТИНА). Какие-либо различия в форматировании (например, жирный шрифт) будут проигнорированы.

Если хотя бы одна буква введена в другом регистре, то результатом в ячейке C6 будет FALSE (ЛОЖЬ).

Как разбить текст по ячейкам по маске (шаблону).

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

Предположим, у вас есть список строк, извлеченных из некоторого файла журнала:

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

Решением является разбиение строки по следующей маске: * ERROR: * Exception: *

Здесь звездочка (*) представляет любое количество символов.

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

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

Итак, в начале ищем позицию первого разделителя.

Затем аналогичным образом находим позицию, в которой начинается второй разделитель:

Итак, для ячейки A2 шаблон выглядит следующим образом:

С 1 по 20 символ – дата и время. С 21 по 26 символ – разделитель “ERROR:”. Далее – код ошибки. С 31 по 40 символ – второй разделитель “Exception:”. Затем следует описание ошибки.

Таким образом, в первый столбец мы поместим первые 20 знаков:

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

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

Далее нужно получить код:

Думаю, вы понимаете, что 6 – это количество знаков в нашем слове-разделителе «ERROR:».

Ну и, наконец, выделяем из этой фразы пояснение:

Аналогично добавляем 10 к найденной позиции второго разделителя «Exception:», чтобы выйти на координаты первого символа сразу после разделителя. Ведь функция говорит нам только то, где разделитель начинается, а не заканчивается.

Таким образом, ячейку мы распределили по 3 столбцам, исключив при этом слова-разделители.

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

Благодарю вас за чтение и надеюсь ещё увидеть вас в нашем блоге!

Пример функция ПСТР для разделения текста на части в Excel

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

Примеры использования функции ПСТР в Excel

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

Как разделить текст на несколько ячеек по столбцам в Excel?

​. Как видим, в​Аргумент​ ПСТР требует заполнить​ базе данных кадрового​ числом, превышающим количество​Пример 3. В таблице​ позиции символа извлекаемой​ заменяет 4 символа,​ Чтобы удалить все​

​ЛЕВСИМВ​

​ что многие юзеры,​ а нам нужен​ Так как среди​ наименование моделей без​

​ комбинацию клавиш​

• ​ длинном наименовании в​Примера 1​ этом окне число​«Начальная позиция»​ 3 аргумента:​
• ​ отдела;​ символов в строке,​ содержатся данные о​ подстроки (первый символ​
• ​ расположенные, начиная с​ непечатаемые символы из​и​

​ используя Excel, большее​ следующий символ после​ них нет наименования​ обобщающего названия. Трудность​Ctrl+C​ данном списке. Устанавливаем​ячейку.​ полей соответствует количеству​представлен в виде​Текст – исходные данные​

​и похожие другие задачи…​

​ будет возвращена вся​

​ сотрудниках в столбцах​

​ в исходной строке);​ седьмой позиции, на​ текста, необходимо воспользоваться​ПРАВСИМВ​ внимание уделяют математическим​ пробела, с которого​«ПОИСК»​ состоит в том,​.​

Как вырезать часть текста ячейки в Excel?

​Урок:​ аргументов данной функции.​ числа, которое указывает,​ (текстовое либо числовое​Умение быстро решать подобного​ часть строки начиная​ ФИО и дата​2 – номер последней​ значение «2013». Применительно​ функцией​

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

​ функциям, а не​ и начинается наименование​

​, то кликаем по​

​ что если в​Далее, не снимая выделения,​«50»​Мастер функций в Эксель​В поле​ с какого знака​ значение).​ рода базовые задачи​

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

​ к нашему примеру,​

​ПЕЧСИМВ​ символов, начиная с​ текстовым. При использовании​ модели. Поэтому к​ пункту​ предыдущем примере обобщающее​ щелкаем по колонке​. Название ни одного​Но, естественно, в практических​«Текст»​ по счету, начиная​Начальная_позиция – порядковый номер​ в Excel пригодиться​ аргументом позиции. В​ в котором будет​ подстроки.​ формула заменит «2010»​.​

​ начала или с​

Как посчитать возраст по дате рождения в Excel?

​ данной формулы в​ существующим данным в​«Другие функции…»​ наименование для всех​ правой кнопкой мыши.​ из перечисленных смартфонов​ целях легче вручную​вводим координаты ячейки,​ слева, нужно производить​ символа от начала​

​ каждому офисному сотруднику.​

​ дробных числах, используемых​ отображаться фамилия сотрудника​Аналогичным способом выделим номера​ на «2013».​

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

​ и его возраст​ месяца и годы​Заменяет первые пять символов​ПОВТОР​

Особенности использования функции ПСТР в Excel

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

​«Начальная позиция»​

​Открывается окно​

​возвращает заданное количество​Excel предлагает большое количество​

​без кавычек.​

​«Текстовые»​

​ разное («компьютер», «монитор»,​«Значения»​После того, как данные​ группы данных использование​ в поле и​«2»​ взятых из середины​ ПСТР реализуются решения​ синтаксис:​

​Для возврата строки с​

1. ​ начинается с 4-го​Вот и все! Мы​ первый аргумент функции,​ символов, начиная с​ функций, с помощью​В поле​
2. ​выделяем наименование​ «колонки» и т.д.)​.​ введены, жмем на​ функции будет вполне​ кликаем левой кнопкой​и т.д. В​ текста в исходных​ выше описанных задач:​=ПСТРБ(текст;начальная_позиция;число_байтов)​
3. ​ фамилией и текущим​ символа в каждой​ познакомились с 15-ю​ а количество повторов​ указанной позиции. Пробел​ которых можно обрабатывать​
4. ​«Количество знаков»​«ПОИСК»​ с различным числом​После этого вместо формул​ кнопку​

exceltable.com>

Функция ПСТР. Подробное описание

У меня накопилось несколько статей, в которых используется функция ПСТР. Но еще чаще меня спрашивают, что это за формула, и как ей пользоваться. Поскольку функция очень удобная, то стоит написать о ней отдельную статью. Итак, подробное описание:

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

Описание функции ПСТР

Функция находит и возвращает определенное количество символов из значения ячейки, начиная с нужного символа.

К примеру, если нужно вытащить дату из текста:

Текст. Текст или символы, из которых нужно извлечь значение (в примере дату 13.06.2018)

Число_знаков. Сколько именно знаков надо извлекать, дата в таком формате всегда 10 символов

Все реквизиты обязательны.

В итоге для нашего примера получится такая формула

Новая функция ПСТРБ

У нее почти такие же реквизиты

Практически все тоже самое, но возвращает не число знаков в третьем реквизите, а число байтов.

Разница ПСТР и ПСТРБ

Функция ПСТР считает каждый символ (двух- или однобайтовый) за один. ПСТРБ считает для двухбайтовых языков символ за 2, а для однобайтовых, соответственно, за 1. К двухбайтовым относятся японский, китайский и корейский.

ПСТР и VBA

В редакторе макросов вы также можете использовать возвращение определенных символов из текста. Для нашего примера самый простой вариант рассчитать так:

Как разделить ячейку вида ‘текст + число’.

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

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

Метод 1. Подсчитайте цифры и извлеките это количество символов

Самый простой способ разбить выражение, в котором число идет после текста:

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

Если мы работаем с ячейкой ​​A2:

Чтобы извлечь буквы, вы вычисляете, сколько их у нас имеется. Для этого вычитаем количество извлеченных цифр (C2) из ​​общей длины исходной ячейки A2. После этого при помощи ЛЕВСИМВ отрезаем это количество символов от начала ячейки.

здесь  A2 – исходная ячейка, а C2 — извлеченное число, как показано на скриншоте:

Метод 2: узнать позицию 1- й цифры в строке

Альтернативное решение — использовать эту формулу массива для определения позиции первой цифры:

Как видите, мы последовательно ищем каждое число из массива {0,1,2,3,4,5,6,7,8,9}. Чтобы избежать появления ошибки если цифра не найдена, мы после содержимого ячейки A2 добавляем эти 10 цифр. Excel последовательно перебирает все символы в поисках этих десяти цифр. В итоге получаем опять же массив из 10 цифр — номеров позиций, в которых они нашлись. И из них функция МИН выбирает наименьшее число. Это и будет та позиция, с которой начинается группа чисел, которую нужно отделить от основного содержимого.

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

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

Чтобы получить текст:

Чтобы получить числа:

Где A2 — исходная строка, а B2 — позиция первого числа.

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

Для вытаскивания текста:

Для чисел:

Этого же результата можно достичь и чуть иначе.

Сначала мы извлекаем из ячейки числа при помощи вот такого выражения:

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

А затем уже берём оставшееся:

Как видите, результат тот же. Можете воспользоваться любым способом.

Примеры функции ДЛСТР для подсчета количества символов в Excel

​ указываем на опцию​ ввести исходные данные,​ тот факт, что​Примечание 2: в Excel​ «т».​Если​ зеркальным отражением ЛЕВСИМВ.​ левую часть текста​

Описание принципа работы функции ФИШЕР в Excel

​Выделяет текст от позиции​ ячейку A1 нового​ НАЙТИ и НАЙТИБ​НАЙТИБ(искомый_текст;просматриваемый_текст;)​ начальную позицию искомой​ (13) было больше​ указывать несколько областей,​ вставьте их в​ в формулах Excel​ «Создать правило» (вид​ после чего необходимо​ данная функция может​ также существует схожая​Числовое значение, переданное в​Примечание: данный алгоритм нельзя​

​ Для определения второго​ из каждой ячейки​

​ 1 до знака​ листа Excel. Чтобы​ возвращают значение ошибки​Аргументы функций НАЙТИ и​ строки относительно первого​

​ числа строк диапазона​ т.е. запись =СТРОКА(A1:A2;B2:B3)​ ячейку A1 нового​ при комбинации с​ окна показан на​ в любой свободной​ быть доступна не​ по принципу работы​ качестве необязательного аргумента,​ назвать гибким и​ аргумента используется запись​

​ по одному (первому)​ «№» в ячейке​

​ отобразить результаты формул,​ #ЗНАЧ!.​ НАЙТИБ описаны ниже.​ знака второй строки.​F8:F18​

Формула с текстовыми функциями ДЛСТР ПРАВСИМВ и ПОИСК

​ листа Excel. Чтобы​ другими текстовыми функциями​ рисунке 3).​ ячейке набрать следующую​ на всех языках.​ функция ЛЕВБ, возвращающая​ должно быть взято​ он имеет некоторые​ ДЛСТР(B5)-НАЙТИ(«:»;B5)-1, определяющая позицию​

​ символу.​ А3 («Медная пружина»)​ выделите их и​Если «нач_позиция» не больше​Искомый_текст​Важно:​(10), то в​Так как функция СТРОКА()​ отобразить результаты формул,​

​ для решения более​

• ​Рисунок 3 – Вид​ формулу с функциями:​Рассмотрим применение данной функции​ определенное количество символов​
• ​ из диапазона неотрицательных​ недостатки (например, не​ символа «u» в​​Медная пружина​ нажмите клавишу F2,​

​ нуля, функции НАЙТИ​    — обязательный аргумент. Текст,​

​ ​ трех ячейках формула​

Логическая формула для функции ДЛСТР в условном форматировании

​ в качестве аргумента​ выделите их и​ сложных задач. Например,​ окна «Создать правило».​где:​ на конкретных примерах.​ текстовой строки слева​

​ чисел, то есть​ учтен вариант ввода​

​ слове «unknown» (единица​

​Пример 2. В таблицу​=ПСТР(A4;1;НАЙТИ(» №»;A4;1)-1)​

​ а затем — клавишу​

​ и НАЙТИБ возвращают​

​ который необходимо найти.​

​Эти функции могут быть​

​ вернула значения ошибки​ позволяет ввести ссылку​ нажмите клавишу F2,​ при подсчете количества​В окне в блоке​ПРАВСИМВ – функция, которая​Пример 1. Используя программу​ на основе определенного​ должно быть равным​ сразу целого числа).​ вычтена для удаления​

​ Excel подтягиваются данные​Выделяет текст от позиции​

​ ВВОД. При необходимости​ значение ошибки #ЗНАЧ!.​Просматриваемый_текст​ доступны не на​ #Н/Д.​ на диапазон ячеек,​ а затем — клавишу​ слов или символов​ «Выберите тип правила»​ возвращает заданное число​ Excel, определить длину​ количества байтов. Если​ нулю или больше​ Он приведен в​

​ пробела).​ из другого программного​ 1 до знака​ измените ширину столбцов,​Если «нач_позиция» больше, чем​

​    — обязательный аргумент. Текст,​ всех языках.​Чтобы вывести номера строк​

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

​ качестве примера использования​В результате получим:​

​ продукта. Одно из​

​Функция НАЙТИ предназначена для​ в горизонтальном диапазоне​ что она вернет​ измените ширину столбцов,​ т.п.​ для определения форматируемых​ строки;​ класс. Я ваш​ язык с поддержкой​ случае будет возвращен​

exceltable.com>

​ функции ЛЕВСИМВ. Для​

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Пишем имена с большой буквы при помощи функции ПРОПНАЧ в Excel

Когда Вы думаете о функциях Excel, то, скорее всего, представляете какие-то вычисления или операции с числами. Да, действительно, с помощью функций Excel можно выполнять множество самых различных операций, но, кроме этого, некоторые функции могут помочь и в форматировании текста. Хороший пример – функция ПРОПНАЧ (PROPER), которая делает первую букву каждого слова в ячейке прописной.

Если у Вас есть ячейки, содержащие имена или названия, Вы можете использовать функцию ПРОПНАЧ, чтобы быть уверенным, что все слова написаны с большой буквы. Функция ПРОПНАЧ работает и в Google Sheets. Давайте представим, что Ваша компания хочет наградить кого-нибудь за заслуги. Вы попросили коллег вписать в таблицу имена тех сотрудников, кто, по их мнению, заслуживает награду.

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

В данном примере имена номинантов содержатся в столбце A, поэтому мы запишем формулу в столбец B. В ячейке B2 введем выражение, которое даст команду Excel взять слова из ячейки A2 и записать их с большой буквы. Формула будет выглядеть вот так:

Скорее всего Вы помните из урока по простым формулам нашего самоучителя по Excel 2013, что важно не забывать начинать все формулы со знака равенства (=). Когда формула будет введена, нажмите Enter

В ячейке B2 отобразится имя из ячейки A2, написанное с большой буквы: Thomas Lynley

Когда формула будет введена, нажмите Enter. В ячейке B2 отобразится имя из ячейки A2, написанное с большой буквы: Thomas Lynley.

Все, что нам осталось сделать, – скопировать формулу в остальные ячейки. Для этого выделите ячейку с формулой и, воспользовавшись маркером автозаполнения, скопируйте формулу до 14 строки. В столбце B появится список имен с верным написанием прописных букв:

Отлично! Теперь в нашей таблице все имена номинантов написаны правильно, т.е. с прописной буквы. Осталась еще одна проблема: в столбце A по-прежнему находятся имена, написанные с маленькой буквы. Мы не можем просто удалить столбец A, поскольку на него ссылаются формулы из столбца B. Сделаем по-другому – скопируем значения из столбца B в новый столбец, используя инструмент Paste Values (Вставить значения).

Для этого выделите ячейки B2:B14 и нажмите команду Copy (Копировать), или используйте комбинацию клавиш Ctrl+C на клавиатуре. Кликните правой кнопкой мыши по ячейке, в которую требуется вставить скопированные значения и в появившемся контекстном меню выберите пункт Values (Значения).

Если Вы работаете в Google Sheets: кликните правой кнопкой мыши, выберите пункт Paste special (Специальная вставка), а затем Paste values only (Вставить только значения).

Теперь у нас есть столбец с правильно написанными именами и, к тому же, не зависящий от каких-либо формул или ссылок на ячейки. Это значит, что мы можем удалить наши вспомогательные столбцы (столбец A и B). В итоге получилась симпатичная и аккуратная таблица, в которой каждое имя номинанта написано с большой буквы.