Сцепить и левсимв в excel
В руководстве показано, как использовать функцию ЛЕВСИМВ (LEFT) в Excel, чтобы получить подстроку из начала текстовой строки, извлечь текст перед определенным символом, заставить формулу возвращать число и многое другое.
Среди множества различных функций, которые Microsoft Excel предоставляет для работы с текстовыми данными, ЛЕВСИМВ - одна из наиболее широко применяемых. Как следует из названия, она позволяет извлекать определенное количество знаков, начиная с левой стороны текста. Однако она способна на гораздо большее, чем такая простая операция. В этом руководстве вы найдете несколько базовых формул для понимания синтаксиса, а затем я покажу вам несколько способов, с помощью которых вы можете использовать ЛЕВСИМВ далеко за пределами ее базового применения.
Cинтаксис.
Функция ЛЕВСИМВ в Excel возвращает указанное количество символов (подстроку) от начала содержимого ячейки.
ЛЕВСИМВ (текст; [колич_знаков])
- Текст (обязательно) - это текст, из которого вы хотите извлечь подстроку. Обычно предоставляется как ссылка на ячейку, в которой он записан.
- Второй аргумент (необязательно) - количество знаков для извлечения, начиная слева.
- Если параметр опущен, то по умолчанию подразумевается 1, то есть возвращается 1 знак.
- Если введенное значение больше общей длины ячейки, формула вернет всё ее содержимое.
Например, чтобы извлечь первые 6 символов из A2, запишите такое выражение:
На следующем скриншоте показан результат:
Важное замечание! ЛЕВСИМВ относится к категории текстовых функций, поэтому результатом её всегда является текстовая строка, даже если исходное значение, из которого вы извлекаете цифры, является числом. Если вы работаете с числовым набором данных и хотите, чтобы было извлечено именно число, применяйте ее вместе с функцией ЗНАЧЕН, как показано в одном из примеров ниже.
Что еще можно делать, помимо извлечения текста слева? В следующих примерах показано, как можно применять её в сочетании с другими функциями Excel для решения более сложных задач.
Как извлечь подстроку перед определенным символом.
В некоторых случаях может потребоваться извлечь часть текста, который предшествует определенному знаку. Например, вы можете извлечь имена из столбца ФИО или получить коды стран из колонки с телефонными номерами. Проблема в том, что каждое имя и каждый код содержат разное количество символов, и поэтому вы не можете просто указать точное число сколько знаков отделить, как мы сделали в приведенном выше примере.
Если имя и фамилия разделены пробелом, проблема сводится к определению положения этого разделителя. Это можно легко сделать с помощью функции ПОИСК или НАЙТИ .
Предположим, что полное имя находится в ячейке A2, позиция пробела возвращается по этой простой формуле: ПОИСК(" "; A2)). А теперь вы вставляете это выражение в ЛЕВСИМВ:
Чтобы еще немного улучшить результат, избавьтесь от конечного пробела, вычтя 1 из результата поиска. Ведь невидимые конечные пробелы могут вызвать множество проблем, особенно если вы планируете использовать извлеченные имена в других вычислениях:
Таким же образом вы можете извлечь коды стран из столбца телефонных номеров. Единственное отличие состоит в том, что вам теперь нужно узнать позицию первого дефиса ("-"), а не пробела:
Думаю, вы поняли, что можете брать эту универсальную формулу, чтобы получить подстроку, которая предшествует любому другому знаку:
Как удалить последние N символов.
Вы уже знаете, отрезать кусочек от начала текстовой строки. Но иногда вы можете захотеть сделать что-то другое — удалить определенное количество символов из конца строки и перенести оставшееся в другую ячейку. Для этого можно также применять функцию ЛЕВСИМВ в сочетании с ДЛСТР , например:
ЛЕВСИМВ ( текст ; ДЛСТР( текст ) - число_символов_для_удаления )
Это работает с такой логикой: ДЛСТР получает общее количество символов в ячейке, затем вы вычитаете количество ненужных знаков из общей длины, а ЛЕВСИМВ возвращает оставшееся.
Например, чтобы удалить последние 7 знаков из текста в A2, запишите такое выражение:
Как показано на скриншоте, формула успешно отсекает слово «продукты» (8 букв, разделитель и 2 пробела) из текстовых значений в столбце A.
При этом взять на вооружение способ, рассмотренный нами в предыдущем примере, будет очень сложно, так как все разделители разные.
Как заставить ЛЕВСИМВ возвращать число.
Как вы уже знаете, ЛЕВСИМВ в Эксель всегда возвращает текст, даже если вы извлекаете несколько первых цифр из ячейки. Для вас это означает, что вы не сможете использовать эти результаты в вычислениях или в других функциях Excel, которые работают с числами.
Итак, как заставить ЛЕВСИМВ выводить числовое значение, а не текстовую строку, состоящую из цифр? Просто заключив его в функцию ЗНАЧЕН (VALUE), которая предназначена для преобразования текста, состоящего из цифр, в число.
Например, чтобы извлечь символы перед разделителем “-” из A2 и преобразовать результат в число, можно сделать так:
Результат будет выглядеть примерно так:
Как вы можете видеть на скриншоте выше, числа в столбце B, полученные с помощью преобразования, автоматически выровнены по правому краю в ячейках, в отличие от текста с выравниванием по левому краю в столбце A. Поскольку Эксель распознает итоговые данные как числа, вы можете суммировать и усреднять эти значения, находить минимальное и максимальное значение и выполнять любые другие вычисления с ними.
Это лишь некоторые из множества возможных вариантов использования ЛЕВСИМВ в Excel.
Дополнительные примеры формул ЛЕВСИМВ можно найти на следующих ресурсах:
Не работает ЛЕВСИМВ — причины и решения
Если ЛЕВСИМВ не работает на ваших листах должным образом, это, скорее всего, связано с одной из причин, которые мы перечислим ниже.
1. Аргумент «количество знаков» меньше нуля
Чтобы лучше проиллюстрировать эту мысль, возьмем формулу, которую мы записали в первом примере для извлечения телефонных кодов страны:
ЛЕВСИМВ(A2; ПОИСК("-"; A2)-1)
2. Начальные пробелы в исходном тексте
Если вы скопировали свои данные из Интернета или экспортировали из другого внешнего источника, довольно часто такие неприятные сюрпризы попадаются в самом начале текста. И вы вряд ли заметите, что они там есть, пока что-то не пойдет не так. Следующее изображение иллюстрирует проблему:
Чтобы избавиться от ведущих пробелов на листах, воспользуйтесь СЖПРОБЕЛЫ (TRIM).
3. ЛЕВСИМВ не работает с датами.
Если вы попытаетесь использовать ЛЕВСИМВ для получения отдельной части даты (например, дня, месяца или года), в большинстве случаев вы получите только первые несколько цифр числа, представляющего эту дату. Дело в том, что в Microsoft Excel все даты хранятся как числа, представляющие количество дней с 1 января 1900 года. То, что вы видите в ячейке, это просто визуальное представление даты. Ее отображение можно легко изменить, применив другой формат.
Например, если у вас есть дата 15 июля 2020 года в ячейке A1 и вы пытаетесь извлечь день с помощью выражения ЛЕВСИМВ(A1;2). Результатом будет 44, то есть первые 2 цифры числа 44027, которое представляет 15 июля 2020г. во внутренней системе Эксель.
Чтобы извлечь определенную часть даты, возьмите одну из следующих функций: ДЕНЬ(), МЕСЯЦ() или ГОД().
Если же ваши даты вводятся в виде текстовых строк, то ЛЕВСИМВ будет работать без проблем, как показано в правой части скриншота:
Вот как можно использовать функцию ЛЕВСИМВ в Excel.
Все описанные выше операции, а также многие другие действия с текстовыми значениями в Excel вы можете выполнить при помощи специального инструмента работы с текстом, включённого в надстройку Ultimate Suite. Вот только некоторые из этих возможностей: удалить лишние пробелы и ненужные символы, изменить регистр текста, подсчитать буквы и слова, добавить один и тот же текст в начало или конец всех ячеек в диапазоне, преобразовать текст в числа, разделить по ячейкам, извлечь отдельные слова, найти дубликаты слов.
Как быстро посчитать количество слов в Excel - В статье объясняется, как подсчитывать слова в Excel с помощью функции ДЛСТР в сочетании с другими функциями Excel, а также приводятся формулы для подсчета общего количества или конкретных слов в…
Как быстро извлечь число из текста в Excel - В этом кратком руководстве показано, как можно быстро извлекать число из различных текстовых выражений в Excel с помощью формул или специального инструмента «Извлечь». Проблема выделения числа из текста возникает достаточно…
Как удалить пробелы в ячейках Excel - Вы узнаете, как с помощью формул удалять начальные и конечные пробелы в ячейке, лишние интервалы между словами, избавляться от неразрывных пробелов и непечатаемых символов. В чем самая большая проблема с…
Функция СЖПРОБЕЛЫ — как пользоваться и примеры - Вы узнаете несколько быстрых и простых способов, чтобы удалить начальные, конечные и лишние пробелы между словами, а также почему функция Excel СЖПРОБЕЛЫ (TRIM в английской версии) не работает и как…
Функция ПРАВСИМВ в Excel — примеры и советы. - В последних нескольких статьях мы обсуждали различные текстовые функции. Сегодня наше внимание сосредоточено на ПРАВСИМВ (RIGHT в английской версии), которая предназначена для возврата указанного количества символов из крайней правой части…
Как извлечь текст из ячейки при помощи функции ПСТР и специальных инструментов - ПСТР - одна из текстовых функций, которые Microsoft Excel предоставляет для управления текстовыми строками. На самом базовом уровне она используется для извлечения подстроки из середины текста. В этом руководстве мы обсудим…
5 примеров с функцией ДЛСТР в Excel. - Вы ищете формулу Excel для подсчета символов в ячейке? Если да, то вы, безусловно, попали на нужную страницу. В этом коротком руководстве вы узнаете, как использовать функцию ДЛСТР (LEN в английской версии)…
Как быстро сосчитать количество символов в ячейке Excel - В руководстве объясняется, как считать символы в Excel. Вы изучите формулы, позволяющие получить общее количество символов в диапазоне и подсчитывать только определенные символы в одной или нескольких ячейках. В нашем предыдущем…
Как в Excel разделить текст из одной ячейки в несколько - В руководстве объясняется, как разделить ячейки в Excel с помощью формул и стандартных инструментов. Вы узнаете, как разделить текст запятой, пробелом или любым другим разделителем, а также как разбить строки на…
Как преобразовать текст в число в Excel — 10 способов. - В этом руководстве показано множество различных способов преобразования текста в число в Excel: опция проверки ошибок в числах, формулы, математические операции, специальная вставка и многое другое. Иногда значения в ваших…
Одна из самых важных задач при редактировании текстовых строк в Excel – это отделение части строки для получения фрагмента текста.
Примеры основных текстовых функций в Excel с описанием
С помощью текстовых функций: ЛЕВСИМВ, ПРАВСИМВ и ПСТР в процессе офисной работы можно быстро и легко решить такие задачи как:
- преобразование территориального кода в почтовый код;
- удаление кода страны или кода оператора сотовой связи в номерах телефонов
- выборка фрагмента кода сотрудника или должности из номера картотеки в базе данных кадрового отдела;
- и похожие другие задачи…
Умение быстро решать подобного рода базовые задачи в Excel пригодиться каждому офисному сотруднику.
На рисунке примеров показано, как легко с помощью текстовых функций ЛЕВСИМВ, ПРАВСИМВ и ПСТР реализуются решения выше описанных задач:
Функция ЛЕВСИМВ предоставляет возможность пользователю Excel отделить от значения ячейки фрагмент текста или числа с определенным количеством символов от начала исходной строки данных. Данная функция требует указать 2 аргумента:
- Текст – исходные данные. Поддерживает и другие типы значений кроме текстовых: число, логическое значение. Не поддерживает значение ошибок, а дату воспринимает как числовое значение.
- Количество_знаков – количество символов, взятых от начала, которые следует оставить в фрагменте текста при отделения его от строки. Другими словами, длина вырезанного фрагмента текста с левой стороны исходной строки.
В примере функция =ЛЕВСИМВ(A2 ;5) отделяет 5 первых чисел из текста в ячейке A2.
Текстовая функция ПРАВСИМВ предоставляет возможность отделения определенного количества символов с конца исходной текстовой строки. Фактически данная функция работает обратно пропорционально для ЛЕВСИМВ. И требует заполнить такие же аргументы. Главное отличие — это направление действия функции: с права на лево, то есть начиная отсчет символов с конца.
В данном примере с помощью текстовой функции ПРАВСИМВ отделяется правая часть номеров телефонов сотрудников фирмы. Это 9 последних знаков в конце: =ПРАВСИМВ(A6;9).
Следующая текстовая функция ПСТР более продвинутая. Она позволяет получить фрагмент текста из середины исходной строки. Функция ПСТР требует заполнить 3 аргумента:
- Текст – исходные данные (текстовое либо числовое значение).
- Начальная_позиция – порядковый номер символа от начала строки с которого следует начать отделение фрагмента текста.
- Количество_знаков – количество символов, взятых из середины текста в исходных данных.
В описанном примере функция =ПСТР(A10;4;1) – отделяет только одно числовое значение начинающиеся от 4-го символа с начала исходной строки.
Чтобы объединить значения (текстовые, цифровые) из разных ячеек в одну, используется функция СЦЕПИТЬ. Ее аналог – & (амперсанд). Знак справляется с простейшими задачами. Но для объединения множества строк не подходит.
Синтаксис функции СЦЕПИТЬ
Функция входит в группу «текстовые». Синтаксис: (текст 1; текст 2;…). Можно включить до 255 текстовых строк. Первый аргумент – «текст 1» – является обязательным. Любые аргументы можно указать в виде текста, цифр или ссылок на ячейки.
Общие правила использования функции СЦЕПИТЬ:
Как пользоваться функцией СЦЕПИТЬ в Excel
Начнем с простейших примеров.
Данные для объединения:
Ставим курсор в ячейку Е2 и вызываем мастер функций (кнопка fx). В категории «Текстовые» находим функцию СЦЕПИТЬ. Аргументы – ссылки на ячейки А2, В2, С2 и пробелы:
Результат объединения значений в ячейках:
Такой же результат получим с помощью амперсанда:
Один из аргументов – текст. Формула: =СЦЕПИТЬ("слесарь"; " ";A2;" ";B2;" ";C2). Результат:
И с более сложными задачами справится функция СЦЕПИТЬ в Excel. Примеры:
- Соединим текст с датой. Данные находятся в отдельных ячейках. Для столбца «Дата» установлен формат «Дата». Если использовать только функцию СЦЕПИТЬ, дата отобразится в числовом формате. Поэтому добавим функцию ТЕКСТ для форматирования чисел. Формула: Второй аргумент функции ТЕКСТ – формат представления даты. Результат:
- Соединим сразу много ячеек в строке. Если писать формулу с функцией СЦЕПИТЬ, получится долго и малоэффективно. Используем маленькую хитрость. Вот наша строка со значениями в отдельных ячейках. В ячейку Н1 ставим знак «равно». Выделяем диапазон со значениями А1:F1. Вводим амперсанд & и пробел “ “. Жмем F9. В строке формул появится формула массива. Ставим курсор в строку формул после знака «равно». Пишем название функции. Фигурные скобки заменяем на круглые и жмем ВВОД. Получаем нужный результат.
- Соединим значения через косую черточку («-», «&» и т.п.). Формула будет выглядеть так: = СЦЕПИТЬ (ссылка на ячейку 1; «/»; ссылка на ячейку 2).
- Соединим несколько значений в столбце. Функция подходит для значений в строках. Описанный выше способ с формулой массива тоже не сработает.
Преобразуем вертикальный диапазон в горизонтальный с помощью функции ТРАНСП. А затем воспользуемся формулой массива для быстрого соединения значений. В ячейку В1 ставим знак «равно». Пишем ТРАНСП. Указываем диапазон А1:А6. Вводим & и пробел (« »). Жмем F9.
Теперь преобразуем формулу массива, добавив функцию и заменив скобки.
Обратная функция СЦЕПИТЬ в Excel
Стандартной функции, которая разделяла бы значения одной ячейки, нет. Для выполнения ряда подобных задач можно использовать другие функции:
- ЛЕВСИМВ (отображает заданную часть символов с начала строки);
- ПРАВСИМВ (отображает заданную часть символов с конца строки);
- ПСТР (отображает указанную часть символов, начиная с заданной позиции) и другие.
- Извлечем первое слово из строки, применив функцию ЛЕВСИМВ. Аргументы – «текст» (ссылка на строку с искомым значением), «количество знаков» (число символов, которые нужно извлечь).
- Извлечем последнее слово из строки с помощью функции ПРАВСИМВ. Синтаксис похожий (как в предыдущем примере). Только количество знаков считается с конца.
- Извлечем из записи в строке фамилию (второе слово) с помощью функции ПСТР. Аргументы – «текст» (ссылка на строку), «начальная позиция» (номер символа, с которого программа начнет считать число знаков); «количество знаков». В примере: Е2 – «текст», с 9 знака функция вернет 8 символов.
Для выполнения более сложных задач используются более сложные формулы, составляются пользовательские функции.
Функция ЛЕВСИМВ в Excel используется для получения части текста и возвращает заданное количество символов текстовой строки, при этом отсчет символов начинается слева.
Как вырезать часть текста из строки функцией ЛЕВСИМВ в Excel?
Пример 1. В таблице Excel содержатся имена переменных вместе с текстом, определяющим их тип данных. Необходимо выделить только названия переменных и записать их в отдельный столбец.
Исходная таблица данных:
Для выделения первого символа, которым является искомое название переменной, используем формулу:
Второй равен 1 по умолчанию, поэтому функция возвращает первый символ. Аналогично выделим названия переменных из других строк. В результате получим:
Как видно на рисунке с помощью функции ЛЕВСИМВ получилось вытащить левую часть текста из каждой ячейки по одному (первому) символу.
Как вытащить часть текста из ячейки в Excel используя функцию ЛЕВСИМВ?
Пример 2. В таблицу Excel подтягиваются данные из другого программного продукта. Одно из полей данных получило ошибку. Для обработки ошибки необходимо выделить ее код и описание из строки.
Выделим код ошибки с помощью следующей формулы:
- ЛЕВСИМВ(B5;НАЙТИ("3";B5)+1) – функция, выделяющая часть текстовой строки слева, количество символов в которой соответствуют порядковому номеру символа «3» (функция НАЙТИ выполняет поиск порядкового номера этого символа и возвращает соответствующее значение). Единица (+1) добавлена для выделения всего кода ошибки.
- 2 – количество символов, которые вернет функция ПРАВСИМВ, работающая аналогично рассматриваемой ЛЕВСИМВ (отличие – возвращает символы справа, а не слева строки).
В результате получим:
Выделим часть текста ошибки:
В данном случае удобнее использовать ПРАВСИМВ, которая является, по сути, зеркальным отражением ЛЕВСИМВ. Для определения второго аргумента используется запись ДЛСТР(B5)-НАЙТИ(":";B5)-1, определяющая позицию символа «u» в слове «unknown» (единица вычтена для удаления пробела).
В результате получим:
Округление чисел текстовыми функциями в текстовой строке
Пример 3. Создать функцию для округления дробных чисел до ближайших целых значений с использованием формул для работы с текстом.
Форма для округления числовых значений имеет следующий вид:
В ячейке B3 введена следующая сложная формула:
=5,ЛЕВСИМВ(A2+1,НАЙТИ(".",A2)-1),ЛЕВСИМВ(A2,НАЙТИ(".",A2)-1)),"Число не введено")' >
- Первая функция ЕСЛИ проверяет с помощью формулы ЕПУСТО, заполнена ли ячейка, которая должна содержать значение, которое требуется округлить.
- Если число введено, запись ЧЗНАЧ(ПРАВСИМВ(ЛЕВСИМВ(B2;НАЙТИ(",";B2)+1)) возвращает числовое значение первого символа после запятой и сравнивает полученное значение с числом 5.
- Если полученное значение больше 5, будет возвращен результат выполнения функции ЛЕВСИМВ(B2+1;НАЙТИ(",";B2)-1), то есть целой части числа +1 по правилам округления.
- Если
Примечание: данный алгоритм нельзя назвать гибким и он имеет некоторые недостатки (например, не учтен вариант ввода сразу целого числа). Он приведен в качестве примера использования функции ЛЕВСИМВ. Для расчетов лучше использовать встроенную функцию ОКРГУГЛ и подобные ей.
Функция ЛЕВСИМВ в Excel и особенности ее использования
Функция ЛЕВСИМВ используется наряду с прочими функциями для работы с текстовыми строками в Excel (ПРАВСИМВ, СИМВОЛ, СЦЕПИТЬ, ДЛСТР и прочими) и имеет следующую синтаксическую запись:
- текст – ссылка на ячейку, содержащую текст, или текстовая строка, в которой требуется выделить требуемое количество символов слева. Аргумент обязателен для заполнения.
- [число_знаков] – необязательный для заполнения аргумент; числовое значение, характеризующее количество символов, которые требуется выделить из текстовой строки.
Примечание 2: в Excel также существует схожая по принципу работы функция ЛЕВБ, возвращающая определенное количество символов текстовой строки слева на основе определенного количества байтов. Если по умолчанию используется язык с поддержкой двухбайтовой кодировки, для получения первого символа строки требуется в качестве аргумента указать число 2, а для получения последующих второго, третьего и прочих символов необходимо указывать соответствующие числа, кратные двум. Для языков с однобайтовой кодировкой функции ЛЕВСИМВ и ЛЕВБ возвращают идентичные результаты.
У нас имеется ФИО, разбитое по столбцам, из которого необходимо получить классическую запись вида Фамилия И.О.
Формулы СЦЕПИТЬ и ЛЕВСИМВ помогут нам в решении данной задачи.Пример доступен для скачивания в конце статьи.
Для решения задачи, нам необходимо соединить значения из всех столбцов, образующих ФИО, причём из имени и отчества извлечь только первые буквы.
В ячейку D2 нашей таблицы запишем следующую формулу:- СЦЕПИТЬ() — функция, которая позволит нам соединить все аргументы из ячеек в одно целое;
- A2 — первый аргумент, который соответствует Фамилии;
- » « — второй аргумент, который создаёт пробел между Фамилией и И.О.;
- ЛЕВСИМВ(B2) — функция, извлекающая из ячейки B2 самую первую букву;
- «.» — разделительный аргумент, точка;
- ЛЕВСИМВ(C2) — функция, извлекающая из ячейки C2 самую первую букву;
В результате наша таблица примет такой вид:
В этом уроке мы разобрали две новые формулы СЦЕПИТЬ и ЛЕВСИМВ.
Задача, которую мы решали, весьма редкая и специфическая, но теперь такие проблемы не поставят Вас в тупик.По традиции, для самых ленивых доступен урок в видеоформате.
Так же рекомендую всем проделать эти действия на собственных примерах самостоятельно, чтобы закрепить знания.
Я сторонник того, чтобы все знания закреплять на практике, в противном случае — без применения они забываются.Больше полезных видеоуроков на нашем YouTube канале.
Если у Вас возникли вопросы или просто хотите поделиться мнением, напишите в комментариях к записи.Читайте также: