Excel mid функция

VBA Excel. Функции Left, Mid, Right (вырезать часть строки)

Извлечение (вырезание) части строки с помощью кода VBA Excel из значения ячейки или переменной. Функции Left, Mid и Right, их синтаксис и аргументы. Пример.

Функция Left

Эта функция извлекает левую часть строки с заданным количеством символов.

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

Left(строка, длина)

  • строка — обязательный аргумент: строковое выражение, из значения которого вырезается левая часть;
  • длина — обязательный аргумент: числовое выражение, указывающее количество извлекаемых символов.

Если аргумент «длина» равен нулю, возвращается пустая строка. Если аргумент «длина» равен или больше длины строки, возвращается строка полностью.

Функция Mid

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

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

Mid(строка, начало, [длина])

  • строка — обязательный аргумент: строковое выражение, из значения которого вырезается часть строки;
  • начало — обязательный аргумент: числовое выражение, указывающее положение символа в строке, с которого начинается извлекаемая часть;
  • длина — необязательный аргумент: числовое выражение, указывающее количество вырезаемых символов.

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

Функция Right

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

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

Right(строка, длина)

  • строка — обязательный аргумент: строковое выражение, из значения которого вырезается правая часть;
  • длина — обязательный аргумент: числовое выражение, указывающее количество извлекаемых символов.

Если аргумент «длина» равен нулю, возвращается пустая строка. Если аргумент «длина» равен или больше длины строки, возвращается строка полностью.

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

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

Функция Mid

Возвращает значение типа Variant (String), содержащее указанное число символов строки.

Функция Mid имеет следующие аргументы:

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

Обязательный аргумент. Типа. Позиции знака в строке , с которой начинается начало части. Если значение » Начало » больше, чем число знаков в строке, функция ПСТР возвращает пустую строку («»).

Необязательный аргумент. Variant (Long). Число возвращаемых знаков. Если аргумент опущен или в тексте меньше, чем Длина (включая символ в начале),возвращаются все символы из начальной позиции до конца строки.

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

Примечание: Используйте функцию MidB для работы с содержащимися в строке байтами, например в языках с двухбайтовыми кодировками (DBCS). Вместо количества знаков в аргументе этой функции указывается количество байтов. Образец кода с использованием функции MidB приведен во втором примере.

Примеры запросов

ВЫБЕРИТЕ ProductID, ПСТР (ProductID; 5) как Выражение1 из Продуктсалес;

Возвращает значение ProductID и часть ProductID, начиная с позиции 1 знака 5, и отображает результаты в столбце Выражение1.

ВЫБЕРИТЕ ProductID, ПСТР (ProductID; 5; 4) как Тестмид из Продуктсалес;

Возвращает слово ProductID и часть ProductID, начиная с позиции 5, содержащей 4 символа, и отображает результаты в столбце Тестмид.

Примеры VBA

Примечание: В примерах ниже показано, как использовать эту функцию в модуле Visual Basic для приложений (VBA). Чтобы получить дополнительные сведения о работе с VBA, выберите Справочник разработчика в раскрывающемся списке рядом с полем Поиск и введите одно или несколько слов в поле поиска.

Читать еще:  Как в excel использовать функцию впр примеры

В первом примере с помощью функции Mid возвращается указанное количество знаков строки.

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

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Функция ПСТР в Excel с примером и формулой

В этой статье я бы хотел рассмотреть очень полезную функцию в Excel для работы с текстом – функция ПСТР (в английской версии MID)

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

Синтаксис функции ПСТР выглядит следующим образом

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

Давайте сразу же рассмотрим на примере с использованием формулы ПСТР. Допустим у нас есть вот такой вот список с наименованием товаров и нам необходимо отразить только наименования без слова «Товара» и номера артикула.

Если бы нам надо было бы отразить без слова «Товар», то мы могли бы просто сделать замену слова «Товара» на пусто и получили бы нужный нам результат, но в данном случае у нас это сделать не получиться, так как нам так же нужно убрать артикул.

В данном случае очень хорошо можно использовать функцию ПСТР со следующей формулой. В ячейке «C2» пропишем

=ПСТР( A2 ; 13 ; 50 )

A2 — текст, их которого нам необходимо взять определенное количество знаков. В нашем пример это текст «Товар 91823 Naomy из коллекции Naomy»

13 — начальная позиция с которой нам необходимо брать знаки. В этом пример я посчитал самостоятельно количество знаков «Товар 91823 «. Товар 5 знаков + 1 знак пробела + 5 знаков артикула + 1 знак пробела после артикула, итого 12 знаков. Следовательно, нам необходимо брать знаки с 13-й позиции.

50 — число знаков, которое нам необходимо взять, так как я не знаю максимальное количество знаков строки, то на глаз я взял побольше, зная, что нет строк больше, чем 50-ю символами.

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

Если статья помогла вам, будем благодарны, если вы нажмете на социальные кнопки +1 и «Мне нравится» под статьей. Так же вступайте в нашу группу ВКонтакте

Пояснить результат работы функции Mid

Неправильный результат работы функции Round
Здравстыуйте. Почему-то функция VBA Round(prm, 2) где prm, 10.505, 10.525, 10.545, 10.565, 10.585.

Функции Left, Right, Mid выдают ошибку
Всем привет. Подскажите пожалуйста. Я пишу макросы в Excel-е, так вот функция Left у меня работает.

VBA не понимает свои же функции: ROUND, LEFT, MID
На одних компах всё работает нормально, а на других компилятор ругается на такие функции, как.

Заменить функцию InStrRev функциями Right, Mid или другими функции
Всем привет. Скажите пожалуйста возможно ли в следующим задание заменить функцию "InStrRev".

Пояснить результат работы кода
Добрый вечер. кто нибудь может объяснить в чем здесь подвох? http://rextester.com/BKATS43592 .

Ципихович Эндрю, «9090690978, 4565665465, 8548375535» вот выход после Left-Right. 8ки нету.

Читать еще:  Формула в excel двссыл

Слева берем все символы до нее справа все после нее. складываем — получаем строку без 8-ки? Чего надо то еще? Работает не оставляя пробела как в первом варианте
а в чем соль обработки между запятыми поясни плиз. изначально речь шла как удалить 8.
ты там точно сам себе жизнь не усложняешь умышленно?

тоже самое что и пробел

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

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

То что ты пишешь — не знаю что и сказать.

Есть много функций работы со строками.
2 в пример я привел. Лично я сделал бы через Left-Right
Попробуй Replace.

спасибо, говорю как сделать — знаю, знаю, что есть много способов, просто очень удивился что это работает не как я думал
_______________________________
Функция Mid
Mid(String,Start,[Length])
Mid$(String,Start,[Length])
MidB(String,Start,[Length])
MidB$(String,Start,[Length])
Функция Mid(Middle) используется для считывания заданного числа символов или байт подряд от заданной позиции в строке слева направо. Нумерация символов в строке всегда начинается с единицы. Для определения числа символов в строке следует использовать функцию Len

Возвращаемое значение
Возвращает значение типа Variant (String), содержащее указанное число символов строки

Параметры
Функция содержит именованные аргументы
String
Обязательный аргумент — строка, из которой извлекаются символы. Если аргумент имеет значение Null, возвращается Null
Start
Обязательный аргумент — значение типа Long. Позиция символа в строке String, с которого начинается нужная подстрока. Если Start больше числа символов в строке string, функция Mid возвращает пустую строку («»)
Примечание Если аргумент имеет отрицательное значение или равен 0, то генерируется ошибка времени исполнения
Length
Необязательный аргумент — значение типа Variant (Long). Число возвращаемых символов. Если этот аргумент опущен или превышает число символов, расположенных справа от позиции Start, то возвращаются все символы от позиции Start до конца строки
Примечание Если аргумент имеет отрицательное значение, то генерируется ошибка времени исполнения
Пример

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

Функция ПСТР в Excel предназначена для выделения подстроки из строки текста, переданной в качестве первого аргумента, и возвращает требуемое количество символов начиная с заданной позиции.

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

Один символ в языках с однобайтовой кодировкой соответствует 1 байту. При работе с такими языками результаты функций ПСТР и ПСТРБ (возвращает подстроку из строки на основе количества заданных байт) не отличаются. Если на компьютере используется двухбайтовый язык, каждый символ при использовании ПСТРБ будет считаться за два. Двухбайтовыми языками являются корейский, японский и китайский.

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

Пример 1. В столбце таблицы содержатся даты, записанные в виде текстовых строк. Записать отдельно в соседних столбцах номер дня, месяца и год, выделенные из представленных дат.

Вид исходной таблицы данных:

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

  • A2:A10 – диапазон ячеек с текстовым представлением дат, из которых будут выделены номера дней;
  • 1 – номер начальной позиции символа извлекаемой подстроки (первый символ в исходной строке);
  • 2 – номер последней позиции символа извлекаемой подстроки.

Аналогичным способом выделим номера месяца и годы для заполнения соответствующих столбцов с учетом, что номер месяца начинается с 4-го символа в каждой строке, а год – с 7-го. Используем следующие формулы:

Вид заполненной таблицы данных:

Таким образом нам удалось разрезать на части текст в ячейках столбца A. Удалось отдельно каждую дату разделить на несколько ячеек по столбцам: день, месяц и год.

Читать еще:  Как в excel сделать формулу сложения

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

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

Вид таблицы данных:

Для заполнения столбца «Наименование» используем следующую формулу:

Функция НАЙТИ возвращает номер позиции символа пробела « » в просматриваемой строке, который принимается в качестве аргумента число_знаков функции ПСТР. В результате расчетов получим:

Для заполнения столбца «Марка» используем следующую формулу массива:

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

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

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

Пример 3. В таблице содержатся данные о сотрудниках в столбцах ФИО и дата рождения. Создать столбец, в котором будет отображаться фамилия сотрудника и его возраст в формате «Иванов – 27».

Вид исходной таблицы:

Для возврата строки с фамилией и текущим возрастом используем следующую формулу:

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

Для соединения (конкатенации) полученных строк используются символы «&». В результате вычислений получим:

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

Функция имеет следующую синтаксическую запись:

  • текст – обязательный для заполнения аргумент, принимающий ссылку на ячейку с текстом или текстовую строку, заключенную в кавычки, из которой будет извлечена подстрока определенной длины начиная с указанной позиции первого символа;
  • начальная_позиция – обязательный аргумент, принимающий целые числа из диапазона от 1 до N, где N – длина строки, из которой требуется извлечь подстроку заданного размера. Начальная позиция символа в строке соответствует числу 1. Если данный аргумент принимает дробное число из диапазона допустимых значений, дробная часть будет усечена;
  • число_знаков – обязательный аргумент, принимающий значение из диапазона неотрицательных чисел, которое характеризует длину в символах возвращаемой подстроки. Если в качестве этого аргумента передано число 0 (нуль), функция ПСТР вернет пустую строку. Если аргумент задан числом, превышающим количество символов в строке, будет возвращена вся часть строки начиная с указанной вторым аргументом позиции. В дробных числах, используемых в качестве данного аргумента, дробная часть усекается.

Функция ПСТРБ имеет схожий синтаксис:

Она отличается единственным аргументом:

  • число_байтов – обязательный аргумент, принимающий целые числа из диапазона от 1 до N, где N – число байтов в исходной строке, характеризующий количество байт в возвращаемой подстроке.
  1. Функция ПСТР вернет пустую строку, если в качестве аргумента начальная_позиция было передано число, превышающее количество символов в исходной строке.
  2. Если в качестве аргумента начальная_позиция было передано значение 1, а аргумент число_знаков определен числом, которое равно или больше общему числу знаков в исходной строке, функция ПСТР вернет всю строку целиком.
  3. Если аргумент начальная_позиция был указан числом из диапазона отрицательных чисел или 0 (нулем), функция ПСТР вернет код ошибки #ЗНАЧ!.
  4. Если аргумент число_знаков задан отрицательным числом, результатом выполнения функции ПСТР будет код ошибки #ЗНАЧ!.

Похожие статьи

Ссылка на основную публикацию
Похожие публикации
Adblock
detector