Как расшифровать строку в excel

Обновлено: 02.07.2024

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

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

Метод A: извлечь подстроку слева, посередине или справа с помощью формулы

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

Извлечь первые n символов

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

B3 - это ячейка, из которой вы извлекаете символы, 3 - это количество символов, которые вы хотите извлечь.

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

Извлечь последние n символов

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

B9 - это ячейка, из которой вы извлекаете символы, 6 - это количество символов, которые вы хотите извлечь.

Извлечь n символов из середины

Если вы хотите извлечь 3 символа, начинающиеся с 4-го символа строки, вы можете использовать следующую формулу:

B15 - это ячейка, из которой вы извлекаете символы, 4 представляет собой извлекаемые символы из 4-го символа (счетчик слева), 3 - это количество символов, которые вы хотите извлечь.

Примечание:

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

Метод B: извлечь подстроку слева, посередине или справа с помощью Kutools for Excel

1. Выберите ячейки, из которых нужно извлечь подстроки, щелкните Kutools > Текст > Извлечь текст.

2. Во всплывающем Извлечь текст диалог под Извлечь по местоположению tab, первые три параметра помогут вам извлечь подстроку слева, посередине или справа.

Первый символ N: извлечь подстроку слева. Например, извлеките первые 2 символа, установите этот флажок и введите 2 в текстовое поле.

Последний символ N: извлечь подстроку справа от строки. Например, извлеките последние 2 символа, установите этот флажок и введите 2 в текстовое поле.

Начальные и конечные символы: извлечь определенное количество символов из середины строки. Например, извлеките с 4-го символа по 9-й, отметьте эту опцию и введите 4 и 9 в текстовые поля отдельно.

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

3. Указав нужное местоположение, нажмите Ok, появится диалоговое окно для выбора ячейки для размещения извлеченной подстроки. Щелкните ОК.

Теперь подстрока извлечена.

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

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

Метод A: извлечь подстроку после или до определенного символа с помощью формулы

Предположим, вы хотите извлечь символы после символа « - »Из списка строк, используйте эту формулу:

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

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

Пример результата показан ниже:

Внимание

Данные могут быть потеряны или изменены при копировании и вставке результатов формулы в другое место. Чтобы предотвратить возникновение этой проблемы, вы можете скопировать и вставить результаты формулы как значение после применения формулы. Или вы можете попробовать Способ B.

Метод B: извлечь подстроку после или до определенного символа Kutools for Excel

Для прямого извлечения подстроки после или до указанного символа вы можете использовать Извлечь текст полезности Kutools for Excel, который может помочь вам извлечь все символы после или до символа, а также может извлечь определенную длину символов до или после символа.

1. Выберите ячейки, в которые нужно извлечь символы, нажмите Kutools > Текст > Извлечь текст.

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

Перед текстом: извлекать подстроки перед введенными символами. Например, введите - в текстовое поле, все символы перед - будут извлечены.

После текста: извлечь подстроки после введенного символа (ов). Например, введите - в текстовое поле, все символы после - будут извлечены.

3. Нажмите Ok, появится диалоговое окно для выбора ячейки для размещения извлеченной подстроки. Нажмите OK.

Теперь строка до или после определенного символа (ов) была извлечена.

13 текстовых инструментов, которые вы должны иметь в Excel, которые повысят вашу эффективность на 90%

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

▲ Кроме инструментов, отображаемых на картинке, в Kutools for Excel есть еще 300 расширенных инструментов, которые могут решить ваши 82% головоломки Excel.

▲ Станьте экспертом по Excel за 5 минут, получите признание и продвижение по службе.

▲ 110000+ высокоэффективных сотрудников и 300+ всемирно известных компаний.

30-дневная бесплатная пробная версия, кредитная карта не требуется Читать далее Загрузить сейчас

Извлечь подстроку между двумя символами

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

Метод А: извлечение по формуле

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

В формуле B3 - это ячейка, из которой вы хотите извлечь строку, ( и ) - это два символа, между которыми вы хотите извлечь строку.

Внимание

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

Метод B: извлечение с помощью Kutools for Excel

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

1. Выберите ячейку, которая использовалась для размещения извлеченной подстроки, щелкните Kutools > Формула Помощник > Текст > Извлечь строки между указанным текстом.

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

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

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

Наконечник:

Если вы хотите извлечь строки между двумя символами (включая два символа), Извлечь текст полезности Kutools for Excel также может оказать вам услугу в этой операции.

1. Выберите ячейки, в которые нужно извлечь подстроку между символами, нажмите Kutools > Текст > Извлечь текст.

2. во всплывающем Извлечь текст диалога под Извлечь по правилу вкладка, перейдите в Текст раздел, введите символы, между которыми вы хотите извлечь строку, и строка может быть заменена подстановочным знаком * . Если вы хотите извлечь строку фиксированной длины, подстановочный знак ? можно использовать, один? указать один символ.

Затем нажмите Добавить добавить правило к Описание правила .

3.Click Ok, появится диалоговое окно для выбора ячейки для размещения извлеченной подстроки. Нажмите OK.

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

Извлечь адрес электронной почты из строки

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

1. Выберите ячейки, в которых будет извлечен адрес электронной почты, затем нажмите Kutools > Текст > Извлечь адрес электронной почты.

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

3. Нажмите OK, адреса электронной почты в каждой ячейке были извлечены.

Извлекать числовые или буквенные символы из строки

Если есть список данных, смешанных с числовыми, алфавитными и специальными символами, вы просто хотите извлечь числа или буквенные значения, вы можете попробовать Kutools for Excel's Удалить символы.

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

2. Затем выберите эту копию данных, нажмите Kutools > Текст > Удалить символы.

3. в Удалить символы диалог, проверьте Нечисловой , нажмите Ok.

Теперь остались только числовые символы.

Чтобы извлечь только алфавитные значения, установите флажок Не альфа вариант в Удалить символы Диалог.

Другие операции (статьи), связанные с преобразованием файлов

Извлечь время из строки даты и времени
Предоставляет трюки для извлечения времени (чч: мм: сс) или часов / минут / секунд только из строки даты и времени (мм / дд / гггг чч: мм: сс)

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

Извлечь n-й символ из строки
Здесь будут представлены методы извлечения n-го символа из строки, например, извлечение 3-го символа из строки a1b2c3, результат - b.

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

Excel СТРОКА функция возвращает номер строки ссылки.

Синтаксис

=ROW ([ reference ])

аргументы

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

Если параметр ссылки опущен, предполагается, что ссылка - это адрес ячейки, в которой в настоящее время отображается функция ROW.
Если ссылка представляет собой диапазон ячеек, введенных как вертикальный массив (говорит =ROW(F5:F10) ), он вернет номер строки первой ячейки в диапазоне (результат будет 5).
Ссылка не может включать несколько ссылок или адресов.

Возвращаемое значение

Функция ROW вернет число, представляющее строку ссылки.

Примеры

Функция СТРОКА очень проста и удобна в повседневной работе с Excel. Этот раздел покажет вам несколько примеров функции ROW, которые помогут вам легко понять и использовать ее в будущем.

Пример 1. Базовое использование функции СТРОКА в Excel

Выберите ячейку и введите формулу =ROW() в него немедленно получит номер строки этой ячейки.

Как показано на скриншоте ниже, скопируйте приведенную ниже формулу в ячейку E3, и результат вернется под номером 3.
=ROW()

Если указать ссылку на ячейку в функции СТРОКА, например =ROW(G103) , он вернет строку номер 103, как показано на скриншоте ниже.

Пример 2: Автоматическая нумерация строк в Excel с помощью функции СТРОКА

Функция СТРОКА может помочь автоматически нумеровать строки, и созданные серийные номера будут автоматически обновляться при добавлении или удалении строк из диапазона. См. Демонстрацию ниже:

1. Предположим, вы хотите начать свои серийные номера с 1 в ячейке A2, выберите ячейку, скопируйте в нее формулу ниже и нажмите клавишу Enter.
=ROW()-1

Внимание: Если первая ячейка - A5, примените эту формулу =ROW()-4 . Вычтите номер строки над текущей ячейкой, из которой вы начинаете серийный номер.

2. Продолжая выбирать ячейку A2, перетащите маркер заливки по строкам, чтобы создать нужную вам серию. Смотрите скриншот:

Пример 3: выделите каждую вторую строку (альтернативные строки) в Excel с помощью функции СТРОКА

В этом примере мы объясняем, как затенять каждую вторую строку (альтернативные строки) в Excel с помощью РЯД функция. Пожалуйста, сделайте следующее.

1. Выберите диапазон, в котором вы хотите применить цвет к альтернативным строкам, щелкните Условное форматирование > Новое правило под Главная таб. Смотрите скриншот:

2. в Новое правило форматирования диалоговое окно, вам необходимо:

2.1) Выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать. вариант в Выберите тип правила коробка;
2.2) Введите формулу =MOD(ROW(),2)=0 в Формат значений, где эта формула истинна коробка;
2.3) Нажмите Формат кнопку, чтобы указать Заполнять цвет;
2.4) Нажмите OK кнопка. Смотрите скриншот:

Внимание: Формула =MOD(ROW(),2)=0 означает, что все четные строки в выбранном диапазоне будут выделены определенным цветом заливки. Если вы хотите заштриховать все нечетные строки в выбранном диапазоне, измените значение от 0 до 1 в формуле.

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

Лучшие инструменты для работы в офисе

Kutools for Excel - поможет вам выделиться из толпы

Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и так далее . ) и экономия 80% времени для вас.

Парольная защита листов в Microsoft Excel давно стала притчей во языцех. В том плане, что ее, по-сути, нет. С регулярностью примерно раз в месяц я получаю вопросы по почте на тему "как мне защитить мои данные на листе Excel от просмотра/изменения?" и каждый раз не знаю что ответить. Можно, конечно, дать ссылочку на статью с подробным описанием всех способов защиты ячеек и листов в Excel, но такая защита остановит только начинающего. В сети можно найти кучу платных и бесплатных программ для взлома такой защиты тупым перебором за считанные минуты.

В какой-то момент мне это надоело и я стал искать способы более надежной защиты данных в Excel собственными силами. Самым простым и удобным оказался шифр Виженера.

Принцип шифра Виженера

Символы в конце алфавита (Э, Ю, Я), соответственно, будут превращаться его начало (А, Б, В).

Допустим, мы хотим зашифровать фразу "КЛАД ЗАРЫТ В САДУ" используя слово ЗИМА в качестве ключа. Запишем это слово подряд несколько раз под исходной фразой:

Для удобства шифрования используем так называемый "квадрат Виженера" - таблицу, где в каждой строке алфавит сдвигается на одну позицию вправо:

Реализация формулами по квадрату Виженера

Если использовать готовый квадрат Виженера как в примере выше, то реализовать шифрование можно одной формулой с помощью функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) , как это было описано в статье про двумерный поиск в таблице. Выглядеть это может примерно так:

Логика этой формулы следующая:

Реализация формулами по кодам символов

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

Внутри компьютера и операционной системы каждый символ имеет свой числовой код от 0 до 255 (его еще называют ASCII-кодом). Microsoft Excel имеет в своем стандартном наборе две функции, которые умеют с ними работать:

Функция КОДСИМВ (CODE) - выдает числовой код символа, указанного в качестве аргумента. Например КОДСИМВ("Ж") выдаст 198.
Функция СИМВОЛ (CHAR) - выдает символ, соответствующий указанному в аргументе коду, т.е. наоборот СИМВОЛ(198) даст нам букву Ж.

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

Теперь сложим коды символов ключа и исходного текста, добавив функцию ОСТАТ (MOD) , чтобы при превышении максимально допустимого количества символов (256) остаться в пределах 0-255:

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

Расшифровка производится совершенно аналогично, только знак "плюс" в формуле меняется на "минус":

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

Макросы для шифрования-дешифрования

Ну, а теперь самое интересное. Чтобы применить шифр Виженера в реальной жизни лучше будет воспользоваться простым макросом, который проводит все описанные в предыдущем пункте операции с каждой ячейкой текущего листа автоматически. Откройте редактор Visual Basic с помощью сочетания клавиш Alt+F11 или кнопкой Visual Basic на вкладке Разработчик (Developer) . Вставьте новый модуль с помощью команды меню Insert - Module и скопируйте туда текст наших макросов:

Первый макрос запрашивает у пользователя ключ и шифрует все ячейки текущего листа. Второй макрос производит обратную операцию дешифрования. Запустить получившиеся макросы можно с помощью сочетания клавиш Alt+F8 или кнопки Макросы (Macros) на вкладке Разработчик (Developer) . Выглядеть все это может примерно так:

ПСТР - одна из текстовых функций, которые Microsoft Excel предоставляет для управления текстовыми строками. На самом базовом уровне она используется для извлечения подстроки из середины текста.

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

Cинтаксис.

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

Функция Excel ПСТР имеет следующие аргументы:

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

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

Все 3 аргумента обязательны.

Например, чтобы извлечь 6 знаков из A2, начиная с 17-го, используйте эту формулу:

Результат может выглядеть примерно так:

5 вещей, которые вы должны знать о функции Excel ПСТР

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

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

Как извлечь имя и фамилию.

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

Получаем имя.

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

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

Получаем фамилию.

Чтобы извлечь фамилию из A2, используйте эту формулу:

Опять же, вы используете ПОИСК, чтобы определить начальную позицию (пробел). Нам не нужно точно рассчитывать конечную позицию (как вы помните, если вместе взятые начальная позиция и количество символов больше, чем общая длина текста, возвращаются просто все оставшиеся). Итак, в аргументе количество символов вы просто указываете общую первоначальную длину , возвращаемую функцией ДЛСТР . Впрочем, вместо этого вы можете просто ввести число, представляющее самую длинную фамилию, которую вы ожидаете найти, например 100. Наконец, СЖПРОБЕЛЫ удаляет лишние интервалы, и вы получаете следующий результат:

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

Продолжим предыдущий пример. А если, помимо имени и фамилии, ячейка A2 также содержит отчество, то как его извлечь?

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

Как и в предыдущем примере, используйте ПОИСК, чтобы определить позицию первого (" "), к которому вы добавляете 1, потому что вы хотите начать с символа, следующего за ним. Таким образом, вы получаете адрес начальной позиции: ПОИСК (" "; A2) +1
Затем вычислите позицию 2- го интервала, используя вложенные функции поиска, которые предписывают Excel начать поиск именно со 2-го: ПОИСК (" "; A2, ПОИСК (" "; A2) +1)

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

ПОИСК(" "; A2; ПОИСК(" "; A2) +1) - ПОИСК(" "; A2)

Соединив все аргументы, мы получаем формулу для извлечения подстроки между двумя пробелами:

На следующем скриншоте показан результат:

Аналогичным образом вы можете извлечь текст между любыми другими разделителями:

ПСТР( строка ; ПОИСК( разделитель ; строка ) +1; ПОИСК( разделитель ; строка ; ПОИСК( разделитель ; строка ) +1) - ПОИСК( разделитель ; строка ) -1)

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

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

Как получить N-е слово из текста.

Этот пример демонстрирует оригинальное использование сложной формулы ПСТР в Excel, которое включает 5 различных составных частей:

ДЛСТР - чтобы получить общую длину.
ПОВТОР - повторение определенного знака заданное количество раз.
ПОДСТАВИТЬ - заменить один символ другим.
ПСТР - извлечь подстроку.
СЖПРОБЕЛЫ - удалить лишние интервалы между словами.

Общая формула выглядит следующим образом:

СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ( строка ; " "; ПОВТОР (" "; ДЛСТР( строка ))); ( N -1) * ДЛСТР( строка ) +1; ДЛСТР( строка )))

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

Например, чтобы вытащить второе слово из A2, используйте это выражение:

Или вы можете ввести порядковый номер слова, которое нужно извлечь (N) в какую-либо ячейку, и указать эту ячейку в формуле, как показано на скриншоте ниже:

Как работает эта формула?

По сути, Excel «оборачивает» каждое слово исходного текста множеством пробелов, находит нужный блок «пробелы-слово-пробелы», извлекает его, а затем удаляет лишние интервалы. Чтобы быть более конкретным, это работает по следующей логике:

ПОДСТАВИТЬ и ПОВТОР заменяют каждый пробел в тексте несколькими. Количество этих дополнительных вставок равно общей длине исходной строки: ПОДСТАВИТЬ($A$2;" ";ПОВТОР(" ";ДЛСТР($A$2)))

Вы можете представить себе промежуточный результат как «астероиды» слов, дрейфующих в пространстве, например: слово1-пробелы-слово2-пробелы-слово3-… Эта длинная строка передается в текстовый аргумент ПСТР.

Затем вы определяете начальную позицию для извлечения (первый аргумент), используя следующее уравнение: (N-1) * ДЛСТР(A1) +1. Это вычисление возвращает либо позицию первого знака первого слова, либо, чаще, позицию в N-й группе пробелов.
Количество букв и цифр для извлечения (второй аргумент) - самая простая часть - вы просто берете общую первоначальную длину: ДЛСТР(A2).
Наконец, СЖПРОБЕЛЫ избавляется от начальных и конечных интервалов в извлечённом тексте.

=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ($A$2);" "; ПОВТОР(" ";ДЛСТР($A$2))); (B2-1)*ДЛСТР($A$2)+1; ДЛСТР($A$2)))

Следующий рисунок демонстрирует улучшенный вариант (2) в действии:

Если ваш исходный текст содержит несколько пробелов между словами, а также очень большие или очень короткие слова, дополнительно вставьте СЖПРОБЕЛЫ в каждое ДЛСТР, чтобы вы были застрахованы от ошибки:

=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ($A$2);" "; ПОВТОР(" ";ДЛСТР(СЖПРОБЕЛЫ ($A$2)))); (B2-1)*ДЛСТР(СЖПРОБЕЛЫ($A$2))+1; ДЛСТР(СЖПРОБЕЛЫ($A$2))))

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

Извлекаем слово, содержащее определенный символ.

В этом примере показана еще одна нестандартная формула Excel ПСТР, которая извлекает слово, содержащее определенную букву или цифру, из любого места:

СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ ( строка , " ", ПОВТОР(" ", 99)), МАКС(1, НАЙТИ( символ , ПОДСТАВИТЬ( строка , " ", ПОВТОР(" ", 99))) - 50), 99))

Предполагая, что исходный текст находится в ячейке A2, и вы хотите получить слово, содержащее символ «$» (цена), выражение принимает следующую форму:

=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(A2;" ";ПОВТОР(" ";99)); МАКС(1;НАЙТИ("$";ПОДСТАВИТЬ(A2;" ";ПОВТОР(" ";99)))-50);99))

Аналогичным образом вы можете извлекать адреса электронной почты (на основе знака «@»), имена веб-сайтов (на основе «www») и так далее.

Теперь разберём пошагово:

Как и в предыдущем примере, ПОДСТАВИТЬ и ПОВТОР превращают каждый пробел в исходном тексте в несколько, точнее, в 99.

НАЙТИ находит позицию нужного символа (в данном примере $), из которой вы вычитаете 50. Это возвращает вас на 50 позиций назад и помещает где-то в середине блока из 99 пробелов, который предшествует слову, содержащему указанный символ.

МАКС используется для обработки ситуации, когда нужное значение появляется в начале исходного текста. В этом случае результат ПОИСК() - 50 будет отрицательным числом, а МАКС(1, ПОИСК() - 50) заменяет его на 1.

С этой начальной точки ПСТР отбирает следующие 99 знаков и возвращает интересующее нас слово, окруженное множеством пробелов. Как обычно, СЖПРОБЕЛЫ помогает избавиться от лишних из них, оставив только один.

Совет. Если извлекаемый отрезок очень большой, замените 99 и 50 на более крупные числа, например 1000 и 500.

Как заставить ПСТР возвращать число?

Как и другие текстовые функции, Excel ПСТР всегда возвращает текст, даже если он содержит только цифры и очень похож на число. Вы можете убедиться с этом, взглянув на пример чуть выше, когда мы получили число «20%» как текст.

Чтобы преобразовать результат в число, просто передайте полученный результат в функцию ЗНАЧЕН (VALUE в английской версии), которая преобразует текстовое значение, состоящее из цифр, в число.

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

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

Тот же подход работает и для более сложных случаев. В приведенном выше примере, предполагая, что коды ошибок имеют переменную длину, вы можете извлечь их с помощью ПСТР, которая получает подстроку между двумя разделителями, вложенную в ЗНАЧЕН:

Вот как можно использовать функцию ПСТР в Excel.

Как извлечь текст из ячейки с помощью Ultimate Suite

Как вы только что видели, Microsoft Excel предоставляет набор различных функций для работы с текстовыми строками. Если вам нужно извлечь какое-то слово или часть текста из ячейки, но вы не уверены, какая функция лучше всего подходит для ваших нужд, передайте работу Ultimate Suite for Excel. Заодно не придётся возиться с формулами.

Вы просто переходите на вкладку Ablebits Data > Текст, выбираете инструмент Split Text и в выпадающем списке нажимаете Извлечь (Extract) :

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

Укажите, сколько символов вы хотите получить из начала, конца или середины строки; или выберите извлечение всего текста до или после определенного символа.
Щелкните Вставить результаты(InsertResults). Готово!

Кроме того, вы можете извлечь любое число символов с начала или в конце текста, из середины текста, между какими-то символами. Например, чтобы извлечь доменные имена из списка адресов электронной почты, вы выбираете чекбокс Все после текста (All after text) и вводите @ в поле рядом с ним. Чтобы извлечь имена пользователей, выберите переключатель Все до текста (All before text), как показано на рисунке ниже.

Помимо скорости и простоты, инструмент «Извлечь текст» имеет дополнительную ценность - он поможет вам изучить формулы Excel в целом и функции подстроки в частности. Как? Выбрав флажок Вставить как формула (Insert as formula) в нижней части панели, вы убедитесь, что результаты выводятся в виде формул, а не просто как значения. Естественно, эти формулы вы можете использовать в других таблицах.

В этом примере, если вы выберете ячейки B2 и C2, вы увидите следующие формулы соответственно:

Сколько времени вам потребуется, чтобы самостоятельно составить эти выражения? ;)

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

Если вам интересно попробовать это, а также множество других полезных функций, включенных в Ultimate Suite for Excel, вы можете загрузить ознакомительную версию. Если вам нравятся инструменты, вы можете получить лицензию по очень специальной цене, которая доступна только для наших русскоязычных пользователей.

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

Как быстро посчитать количество слов в Excel - В статье объясняется, как подсчитывать слова в Excel с помощью функции ДЛСТР в сочетании с другими функциями Excel, а также приводятся формулы для подсчета общего количества или конкретных слов в… Как быстро извлечь число из текста в Excel - В этом кратком руководстве показано, как можно быстро извлекать число из различных текстовых выражений в Excel с помощью формул или специального инструмента «Извлечь». Проблема выделения числа из текста возникает достаточно… Как удалить пробелы в ячейках Excel - Вы узнаете, как с помощью формул удалять начальные и конечные пробелы в ячейке, лишние интервалы между словами, избавляться от неразрывных пробелов и непечатаемых символов. В чем самая большая проблема с… Функция СЖПРОБЕЛЫ — как пользоваться и примеры - Вы узнаете несколько быстрых и простых способов, чтобы удалить начальные, конечные и лишние пробелы между словами, а также почему функция Excel СЖПРОБЕЛЫ (TRIM в английской версии) не работает и как… Функция ПРАВСИМВ в Excel — примеры и советы. - В последних нескольких статьях мы обсуждали различные текстовые функции. Сегодня наше внимание сосредоточено на ПРАВСИМВ (RIGHT в английской версии), которая предназначена для возврата указанного количества символов из крайней правой части… Функция ЛЕВСИМВ в Excel. Примеры использования и советы. - В руководстве показано, как использовать функцию ЛЕВСИМВ (LEFT) в Excel, чтобы получить подстроку из начала текстовой строки, извлечь текст перед определенным символом, заставить формулу возвращать число и многое другое. Среди… 5 примеров с функцией ДЛСТР в Excel. - Вы ищете формулу Excel для подсчета символов в ячейке? Если да, то вы, безусловно, попали на нужную страницу. В этом коротком руководстве вы узнаете, как использовать функцию ДЛСТР (LEN в английской версии)… Как быстро сосчитать количество символов в ячейке Excel - В руководстве объясняется, как считать символы в Excel. Вы изучите формулы, позволяющие получить общее количество символов в диапазоне и подсчитывать только определенные символы в одной или нескольких ячейках. В нашем предыдущем… Как в Excel разделить текст из одной ячейки в несколько - В руководстве объясняется, как разделить ячейки в Excel с помощью формул и стандартных инструментов. Вы узнаете, как разделить текст запятой, пробелом или любым другим разделителем, а также как разбить строки на… Как преобразовать текст в число в Excel — 10 способов. - В этом руководстве показано множество различных способов преобразования текста в число в Excel: опция проверки ошибок в числах, формулы, математические операции, специальная вставка и многое другое. Иногда значения в ваших…

Читайте также: