КАК: Как извлечь текст с помощью функции LEFT / LEFTB в Excel — 2021

КАК: Как извлечь текст с помощью функции LEFT / LEFTB в Excel — 2021

Table of Contents:

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

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

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

Вытащить текст из ячейки excel

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

Извлечение первого слова из строки

Чтобы извлечь первое слово из строки, формула должна найти позицию первого символа пробела, а затем использовать эту информацию в качестве аргумента для функции ЛЕВСИМВ. Следующая формула делает это: =ЛЕВСИМВ(A1;НАЙТИ(" ";A1)-1) .

Эта формула возвращает весь текст до первого пробела в ячейке A1. Однако у нее есть небольшой недостаток: она возвращает ошибку, если текст в ячейке А1 не содержит пробелов, потому что состоит из одного слова. Несколько более сложная формула решает проблему с помощью новой функции ЕСЛИОШИБКА, отображая все содержимое ячейки, если произошла ошибка:
=ЕСЛИОШИБКА(ЛЕВСИМВ(A1;НАЙТИ(" ";A1)-1);A1) .

Если вам нужно, чтобы формула была совместима с более ранними версиями Excel, вы не можете использовать ЕСЛИОШИБКА. В таком случае придется обойтись функцией ЕСЛИ и функцией ЕОШ для проверки на ошибку:
=ЕСЛИ(ЕОШ(НАЙТИ(" ";A1));A1;ЛЕВСИМВ(A1;НАЙТИ(" ";A1)-1))

Извлечение последнего слова строки

Извлечение последнего слова строки — более сложная задача, поскольку функция НАЙТИ работает только слева направо. Таким образом, проблема состоит в поиске последнего символа пробела. Следующая формула, однако, решает эту проблему. Она возвращает последнее слово строки (весь текст, следующий за последним символом пробела):
=ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ("*";ПОДСТАВИТЬ(A1;" ";"*";ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;"";"")))))

Но у этой формулы есть такой же недостаток, как и у первой формулы из предыдущего раздела: она вернет ошибку, если строка не содержит по крайней мере один пробел. Решение заключается в использовании функции ЕСЛИОШИБКА и возврате всего содержимого ячейки А1, если возникает ошибка:
=ЕСЛИОШИБКА(ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ("*";ПОДСТАВИТЬ(A1;" ";"*";ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;" ";"")))));A1)

Следующая формула совместима со всеми версиями Excel:
=ЕСЛИ(ЕОШ(НАЙТИ(" ";A1));A1;ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ("*";ПОДСТАВИТЬ(A1;"";"*";ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;" ";""))))))

Извлечение всего, кроме первого слова строки

Следующая формула возвращает содержимое ячейки А1, за исключением первого слова:
=ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(" ":A1;1)) .
Если ячейка А1 содержит текст 2008 Operating Budget, то формула вернет Operating Budget.

Формула возвращает ошибку, если ячейка содержит только одно слово. Следующая версия формулы использует функцию ЕСЛИОШИБКА, чтобы можно было избежать ошибки; формула возвращает пустую строку, если ячейка не содержит более одного слова:
=ЕСЛИОШИБКА(ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(" ";A1;1));"")

А эта версия совместима со всеми версиями Excel:
=ЕСЛИ(ЕОШ(НАЙТИ(" ";A1));"";ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(" ";A1;1)))

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

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

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

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

Рассмотрим конкретную ситуацию. Вам нужно подготовить отчёт в разрезе, который нельзя получить в бухгалтерской программе. Вы выгрузили в Excel отчет по проводкам (оборотно-сальдовую ведомость, карточку счета и т.д. – не суть важно) и видите, что для нормальной фильтрации данных или создания сводной таблицы для анализа данных у вас не хватает одного признака (аналитики, разреза, субконто и т.д.).

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

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

Вся трудность, в том чтобы извлечь код из текстовой строки.

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

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

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

Количество_знаков — необязательный аргумент. Количество символов, извлекаемых функцией ЛЕВСИМВ (ПРАВСИМВ).

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

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

Сложнее если нужные нам символы находятся в середине текста.

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

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

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

Начальная_позиция – обязательный аргумент. Позиция первого знака, извлекаемого из текста. Первый знак в тексте имеет начальную позицию 1 и так далее.

Количество_знаков – обязательный аргумент. Указывает, сколько знаков должна вернуть функция ПСТР.

Самый простой случай – если код находиться на одном и том же месте от начала строки. Например, у нас наименование документа начинается всегда одинаково «Поступление товаров и услуг ХХ ….»

Наш признак «ХХ» — код филиала начинается с 29 знака и имеет 2 знака в своем составе.

В нашем случае формула будет иметь вид:

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

Первый символ – первая буква в наименовании филиала, второй символ – это буква Ф (филиал) и далее следует пять нулей «00000». Причем меняется только первый символ — первая буква наименования филиала.

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

=ПОИСК(искомый_текст; текст_для_поиска; [нач_позиция])

Искомый_текст – обязательный аргумент. Текст, который требуется найти.

Просматриваемый_текст – обязательный аргумент. Текст, в котором нужно найти значение аргумента искомый_текст.

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

Функция ПОИСК не учитывает регистр. Если требуется учитывать регистр, используйте функцию НАЙТИ.

В аргументе искомый_текст можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому знаку, звездочка — любой последовательности знаков. Если требуется найти вопросительный знак или звездочку, введите перед ним тильду (

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

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

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

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

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

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

2. ДЛСТР – это функция позволяет узнать количество символов строки.

3. НАЙТИ – находит в тексте положение определенного элемента или слова.

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

Пример №1.

Перед нами таблица, в которой указаны ссылка на разные статьи, нам необходимо убрать из каждой ссылки буквы: .html

Чтобы решить данную задачу, мы будем использовать две функции ДЛСТР и ЛЕВСИМВ. С помощью функции ДЛСТР будем определять длину строки, так как строки имеют у нас разную длину. А функцией ЛЕВСИМВ, будем оставлять нужное количество символов. Также заметим, что строчка .html состоит из 5 символов, т.е. в независимости какая длина строки, всегда нужно убирать пять элементов. И так в ячейке «С2» напишем формулу: =ЛЕВСИМВ(B2;ДЛСТР(B2)-5). После её скопируем в оставшиеся ячейки.

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

В этом примере в отличие от первого, мы заранее не знаем сколько нужно отрезать символов, поэтому просто посчитать их количество, нам ничего не даст. Но мы знаем, что они все отделены запятой, поэтому будем использовать функцию «НАЙТИ», чтобы понимать, где запятая. Поэтому в ячейке «С2» пишем формулу: =ЛЕВСИМВ(B2;НАЙТИ(",";B2)-1) И копируем её на оставшиеся ячейки.

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

Как отрезать (удалить) лишний текст слева или справа в ячейке «Эксель».: 21 комментарий

Отличная статья. Помогла отнять лишние знаки из диапазона значений с различной длинной текста.
Например: у меня были значения «100.00» «95.00» 15.00″ «5.00» и т.д.
Решил вопрос формулой: =ЛЕВСИМВ(A1;(ДЛСТР(A1)-3))

готовая формула только подставить строки и кол-во символов)
спасибо!

Нужна помощь. Как удалить в каждой строчке все символы ПОСЛЕ указанного знака/знаков?

Добрый день. По аналогии с удалением слов описанном в статье «Копировать, убрать/удалить первое слово или несколько первых слов в ячейке Excel » только вместо пробелов подставить знак(и), о котором вы писали. ссылка — http://ruexcel.ru/slova/

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

Здравствуйте!
Есть колонка в Екселе с текстовыми значениями типа 00001, 00001А, 00624/1, 00682А, 01082А, 00240-1 и т.п. Требуется во всех значениях убрать впереди все нули. Но только впереди. Не подскажете, как это сделать? Спасибо.

Добрый день!
Большая таблица, в одном из столбцов указаны даты: 1995, 2006г., 1915, 1934г., 1946г., 2017, 2015 и т.д. Как убрать после цифр года символ «г.» во всех ячейках столбца, чтобы остались одни цифры года автоматически?
Благодарю заранее.

Добрый день.
Выделите ячейки, в которых нужно убрать лишние символы.
Нажмите ctrl+H
В поле «Найти:» введите «г.», в поле «Заменить…» не вводите ничего.
Нажмите «заменить все»

Здравствуйте!
Есть колонка в Excel с текстовыми значениями типа 38/18, 35/35, 32/25 и т.п. Требуется во всех значениях убрать впереди цифры и дробь. Точно также, только убрать сзади. Не подскажете, как это сделать? Спасибо.

Добрый день.
Если текст в ячейке H3, то:
=ЛЕВСИМВ(H3; (ПОИСК(«/»;H3)-1))
или
=ПРАВСИМВ(H3;(ДЛСТР(H3)-ПОИСК(«/»;H3)))
зависит от того, какую часть текста (левую или правую) нужно оставить в ячейке.

Здравствуйте. Подскажите, как из строки формата:
Вмк. У240кв. 20-70. 30м3. #39720. 15.40
поместить в отдельную ячейку цифру после символа #.

=ПРАВСИМВ(B2073;(ДЛСТР(B2073)-ПОИСК(«#»;B2073))), где B2073 — ячейка содержащая «Вмк. У240кв. 20-70. 30м3. #39720. 15.40»

Добрый день! Подскажите как текст в одной ячейке: Иванов Иван Иванович разбить на три ячейки: 1. Иванов 2. Иван 3. Иванович

Добрый день, подскажите, пожалуйста, как сократить количество текста в ячейках столбца на 15% с сохранением логического смысла?

Здравствуйте! У меня в столбике, в каждой строке, к примеру, по 15 символов (к примеру, 20.01.2020 17.45) а нужно сократить до 10 (чтобы осталась только дата) , но не в соседний столбец перенеся, как подразумевает функция ЛЕВСИМВ, а просто сократить в том же, не подскажете, как это сделать?

Здравствуйте!
Есть столбец в Екселе со значениями типа 000010531, 000401325, 002000045, 000104573, т.п. Количество нулей разное. Требуется во всех значениях убрать спереди все нули, но только впереди. Не подскажете, как это сделать? Спасибо.

Извлечь число из строки Excel

Разделение значений одной ячейки на несколько ячеек, объединение значений нескольких ячеек в одну является частью обработки данных. С помощью текстовой функции в Excel «Left, MID и Right» мы можем извлечь часть выбранного текстового значения или строкового значения. Чтобы сделать формулу динамической, мы можем использовать другие вспомогательные функции, такие как «Найти и LEN». Однако извлечение только чисел с комбинацией буквенно-цифровых значений требует продвинутого уровня знания формул. В этой статье мы покажем вам 3 способа извлечения чисел из строки в Excel.

• # 1 — Извлечь число из строки в конце строки
• # 2 — Извлечение чисел с правой стороны, но без специальных символов
• # 3 — Извлечение чисел из любой позиции строки

Ниже мы объяснили различные способы извлечения чисел из строк в Excel. Прочтите всю статью, чтобы узнать об этой технике.

# 1 — Как извлечь число из строки в конце строки?

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

Например, город с его пин-кодом ниже является примером того же.

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

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

Какой текст нам нужно найти? Найти текст аргумент? В этом примере нам нужно найти позицию подчеркивания, поэтому введите подчеркивание в двойных кавычках.

Внутри текста в каком тексте нам нужно найти упомянутый текст, поэтому выберите ссылку на ячейку.

Последний аргумент не требуется, поэтому оставьте его сейчас.

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

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

Теперь примените функцию ВПРАВО в ячейке E2.

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

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

# 2 — Извлечение чисел с правой стороны, но без специальных символов

Предположим, у нас есть те же данные, но на этот раз у нас нет специального символа перед числовым значением.

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

Не выключайте компьютер, глядя на формулу; Я расшифрую это для вас.

Для функции ПОИСК в Excel мы предоставили все возможные начальные числа чисел, поэтому формула ищет положение числового значения. Поскольку мы предоставили в массив все возможные числа, результирующие массивы также должны содержать те же числа. Затем функция MIN в excel возвращает наименьшее число из двух, поэтому формула читается ниже.

Итак, теперь у нас есть числовая позиция, теперь давайте найдем общее количество символов в ячейке.

Это вернет общее количество символов в предоставленном значении ячейки. Теперь LEN — Position of the Numerical value будет возвращать количество символов, требуемых с правой стороны, поэтому примените формулу, чтобы получить количество символов.

Теперь примените функцию ВПРАВО в excel, чтобы получить из строки только числовую часть.

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

# 3 — Извлечение числа из любой позиции в Excel

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

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

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

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

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

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

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

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

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

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

Представим, что у нас есть такая информация, располагающаяся в ячейке: «Было доставлено кусков мыла 763шт». Нам необходимо сделать так, чтобы осталось только значение 763 для проведения разнообразных математических операций. Хорошо, если нужно избавиться от текстовых данных только в одной ячейке, тогда можно реализовать удаление ручным способом, но этот вариант не подходит, когда в табличке находится слишком много разной информации. Здесь необходимо применять различные специальные функции табличного редактора.

Первый метод: использование специальной формулы

Для реализации этой процедуры можно применять специальную массивную формулу. Примерная формула выглядит так:

Разберем основные моменты:

1. Специальную формулу необходимо вбивать в поле при помощи комбинации кнопок «Ctrl+Shift+Enter».
2. Стоит заметить, что в таком виде массивная формула может использоваться только с текстовой информацией, в которой число знаков не больше 99. Для увеличения диапазона нужно, к примеру, заменить параметр «СТРОКА($1:$99)» на «СТРОКА($1:$200)». Иными словами, мы вместо показателя 99 вводим число знаков с запасом. Если ввести слишком большой диапазон, то обработка формулы может занять длительное время.
3. Если в текстовых данных числовые значения разбросаны по всему тексту, то формула не сможет правильно обработать информацию.

Детально рассмотрим специальную массивную формулу на таком примере: «Было доставлено кусков мыла 763шт., а заказывали 780»

1. В поле А1 располагается сама текстовая информация, из которой мы будем извлекать числовые данные.
2. Фрагмент: МИН(ЕСЛИ(ЕЧИСЛО(-ПСТР(А1;СТРОКА($1:$99);1));СТРОКА($1:$99))) позволяет определить позицию 1-го значения в поле. Получаем значение 29.
3. Фрагмент: ПРОСМОТР(2;1/ЕЧИСЛО(-ПСТР(А1;СТРОКА($1:$99);1));СТРОКА($1:$99)) позволяет определить позицию последнего значения в поле. Получаем значение 31.
4. Мы получаем такую формулу: =ПСТР(А1;29;31-29+1). Оператор ПСТР позволяет извлечь из текстовой информации, указанной 1-м аргументом, начиная с заданной позиции (29) с числом знаков, заданным 3-м аргументом.
5. В результате мы получаем:
   =ПСТР(А1;29;31-29+1)
   • =ПСТР(А1;29;2+1)
   • =ПСТР(А1;29;3)
   • 763

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

Нам необходимо сохранить только текстовую информацию.

Специальная формула применяется по аналогичному алгоритму, что и вышерассмотренная. Она выглядит так: =ПСТР(А1;ПОИСК(«-«;А1)+1;ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(—ПСТР(ПСТР(А1;ПОИСК(«-«;А1)+1;999);СТРОКА($1:$99);1));0)-1)

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

Второй метод: использование специального макроса

Эту процедуру можно реализовать при помощи специального макроса, созданного в табличном редакторе Эксель. К примеру, у нас есть табличка, в которой существует колонка с текстовой информацией и числовыми данными. Нам нужно оставить только числовые данные, убрав при этом текст.

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

Public Function GetNumbers(TargetCell As Range) As String

Dim LenStr As Long

For LenStr = 1 To Len(TargetCell)

Select Case Asc(Mid(TargetCell, LenStr, 1))

Case 48 To 57

GetNumbers = GetNumbers & Mid(TargetCell, LenStr, 1)

End Select

Next

End Function

Подробная инструкция по созданию пользовательского макроса выглядит так:

1. Используя специальную комбинацию клавиш «Alt+F11», производим открытие редактора VBA. Альтернативный вариант – нажать ПКМ по рабочему листу и выбрать элемент «Исходный текст».
2. Реализуем создание нового модуля. Для осуществления этой процедуры жмем левой клавишей мышки на элемент, имеющий наименование «Insert», а затем выбираем объект «Module».
3. Производим копирование кода, который располагается выше, и вставляем его в созданный модуль. Копирование реализуем при помощи сочетания клавиш «Ctrl+C», а вставку – «Ctrl+V».
4. Теперь в необходимой ячейке, в которой мы планируем вывести только числовую информацию, вбиваем такую формулу: =GetNumbers(А1).
5. Нам нужно растянуть формулу вниз на все ячейки колонки. Для этого наводим указатель на нижний правый уголок ячейки. Курсор принял форму небольшого плюсика темного цвета. Зажимаем левую клавишу мышки и протягиваем формулу вниз до окончания таблички.

1. Готово! Мы реализовали извлечение числовой информации при помощи специального макроса.

Извлечение текста комментариев в ячейку

Добрый день, товарищи! Сегодня поговорим о том, как работать с комментариями в ячейке.

Все мы знаем, что примечание добавляется очень просто — щелчок на ячейке правой кнопкой мыши и выбор пункта «Добавить примечание».

Как быть в случае если текст уже есть (на листе, в файле) и его нужно добавить как комментарий или наоборот — извлечь текст комментариев и поместить их в ячейку на листе?

Добавление комментариев по условию мы рассматривали в этой статье.

Добавление комментария в ячейку с помощью Vba.

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

Sub add_comment()
With Worksheets(1).Range(«a2»).AddComment
.Visible = False
.Text «просмотрел » & Date
End With
End Sub

Что происходит в этом случае — на листе 1 в диапазоне ячеек A2 добавляется комментарий, далее зовутся свойства Visible (комментарий будет скрыт до наведения), Text собственно сам текст примечания, параметр Date проставит текущую дату.

После выполнения макроса получится вот это.

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

Sub add_comments()
For xNum = 1 To 10
Range(«A» & xNum).AddComment
Range(«A» & xNum).Comment.Text Text:=»Комментарий » & xNum
Next
End Sub

Этот код добавит примечание «Комментарий» ко всем ячейкам от А1 до А10.

Извлечение текста комментария в ячейку

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

Function GetComment(rCell As Range) As String
GetComment = rCell.Comment.Text
End Function

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

Текст ячейки в комментарий

Sub CellTextToComment()
Dim c As Range
If TypeName(Selection) <> «Range» Then Exit Sub
For Each c In Selection
c.Comment.Text Text:=c.Text
Next c
End Sub

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

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