Найти последний символ в ячейке Excel

Обновлено: 05.07.2024

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

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

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

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

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

В этой статье описываются семь способов извлечения первых или последних N символов из строки текстовых данных в Microsoft Excel.

Пример данных, использованных в этом сообщении

Примеры в этом сообщении будут извлекать первые и последние 2 символа из ProductSKU в приведенном выше наборе небольших данных о продукте. Первые 2 символа в артикуле содержат код категории товара, а последние 2 символа – размер товара.

Извлечение символов с помощью функций ВЛЕВО и ВПРАВО

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

ЛЕВАЯ функция

Синтаксис:

Текст — это текстовая строка, из которой вы хотите извлечь данные. Это также может быть действительная ссылка на ячейку в книге.
Число [Необязательно] — это количество символов, которое вы хотите извлечь из текстовой строки. Значение должно быть больше или равно нулю. Если значение больше длины текстовой строки, будут возвращены все символы. Если значение опущено, предполагается, что оно равно единице.

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

ВПРАВО

Синтаксис:

Параметры работают так же, как и для функции ВЛЕВО, описанной выше.

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

Извлечение символов с текстом в столбец

Текст в столбцы позволяет разделить столбец на разделы, задав фиксированные пределы для вашего текстового раздела. Чтобы использовать эту методологию:

Выберите все данные в столбце ProductSKU в образце данных.
Нажмите вкладку "Данные" на ленте Excel.
Нажмите значок «Текст в столбцы» в группе «Инструменты данных» на ленте Excel, и появится мастер, который поможет вам настроить разделение текста.
Нажмите кнопку выбора "Фиксированная ширина".
Нажмите "Далее", чтобы перейти к следующему шагу мастера.

< бр />

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

Нажмите "Далее", чтобы перейти к последнему шагу мастера.

< бр />

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

Нажмите "Готово", и ваши данные будут импортированы в три столбца, начиная с ячейки E3.

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

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

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

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

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

Можно также активировать "Быстрое заполнение" в группе "Инструменты данных" на вкладке "Данные" на ленте Excel или с помощью сочетания клавиш Ctrl + E.

Затем Excel автоматически заполнит оставшиеся данные на основе ваших примеров.

Извлечение символов с помощью VBA

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

Для доступа к редактору Visual Basic (VBE) используйте ALT + F11 .

В базовом визуальном редакторе.

Нажмите «Вставить» в строке меню.
Нажмите «Модуль», и для модуля появится новая панель.
Вставьте приведенный выше код.

Теперь ваше окно будет выглядеть, как показано выше.

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

Код перебирает значения от 3 до 12, которые являются номерами строк для выборочных данных. Он вводит первые два символа текста, найденного в строке с номером N и столбце 2, в столбец 5 (столбец E).

Это очень простой код VBA, но его недостаток состоит в том, что он принимает только первые два символа кода продукта. Если код продукта длиннее, будет получен неправильный результат.
Однако код можно расширить для поиска символа-разделителя (-), а затем взять количество символов до этой точки.

Извлечение символов с помощью Power Query

Power Query — это полезный инструмент в Excel для управления данными, и его можно легко использовать для разделения столбца данных на разделы.

Во-первых, вам нужно преобразовать данные в таблицу Excel.

Создайте запрос из таблицы/диапазона.

Выберите ячейку внутри таблицы.
Перейдите на вкладку "Данные".
Нажмите «Из таблицы/диапазона» в группе «Получить и преобразовать данные».

< бр />

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

Нажмите на столбец ProductSKU.
Перейдите на вкладку "Добавить столбец" в редакторе расширенных запросов.
Нажмите «Извлечь» в группе «Из текста».
Выберите «Первые символы» в раскрывающемся списке.

Появится всплывающее окно. Введите 2 в поле Счет. Нажмите «ОК», и будет добавлен новый столбец «Первые символы». Дважды щелкните заголовок нового столбца и переименуйте его в «Категория».

Это приведет к приведенной выше формуле M-кода.

Если вам нужны последние 2 символа, нажмите «Последние символы» в раскрывающемся списке «Извлечь».

Это приведет к приведенной выше формуле M-кода.

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

Извлечение символов с помощью вычисляемого столбца Power Pivot

Вы также можете использовать Power Pivot Table для отображения первых двух символов кода продукта.

Чтобы импортировать данные в Power Pivot:

Выберите ячейку внутри таблицы.
Перейдите на вкладку Power Pivot. Если вы не видите вкладку Power Pivot, вы можете установить ее, перейдя на вкладку "Данные" и нажав "Управление моделью данных".
Выберите "Добавить в модель данных".

При этом откроется надстройка Power Pivot с загруженной таблицей. Вы можете открыть его в любое время на вкладке Power Pivot, выбрав Управление в группе Модель данных на ленте Excel.

< бр />

Щелкните правой кнопкой мыши заголовок "Добавить столбец" и выберите "Переименовать столбец" во всплывающем меню. Вы также можете дважды щелкнуть столбец, чтобы переименовать его. Затем вы можете переименовать его во что-то вроде «Категория».

< бр />

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

В столбце "Категория" теперь отображаются первые два символа кода продукта.

Вы можете сделать то же самое с приведенной выше формулой для последних двух символов.

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

Укажите расположение сводной таблицы в первом всплывающем окне и нажмите OK. Если панель «Поля сводной таблицы» не отображается автоматически, щелкните правой кнопкой мыши скелет сводной таблицы и выберите «Показать список полей».

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

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

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

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

Извлечение символов с помощью Power Pivot Measure

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

Если щелкнуть вкладку Power Pivot и щелкнуть значок "Управление", можно ввести показатель в окно диспетчера Power Pivot.

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

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

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

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

В списке полей сводной таблицы теперь есть вычисляемое поле под названием «Категория», помеченное символом fx перед ним. Это возвращает первые два символа поля ProductSKU.

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

Это огромный недостаток чрезмерного использования вычисляемого столбца в Power Pivot.

Заключение

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

Вы можете использовать формулы, текст в столбец, мгновенное заполнение, VBA, Power Query или Power Pivot.

Как найти первое, последнее или n-е вхождение символа в Excel?

Предположим, у вас есть следующий список текстовых строк, включающих символ «-», и теперь вы хотите получить последнее или n-е вхождение символа «-», есть ли у вас какие-нибудь идеи по решению этой проблемы?

Найти последнее вхождение символа с формулами

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

<р>1. Рядом с текстовой строкой введите или скопируйте любую из приведенных ниже формул в пустую ячейку:

=ПОИСК("^^",ПОДСТАВИТЬ(A2,"-","^^",ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2,"-",""))))
= ПРОСМОТР(2,1/(СРЕДН(A2,СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A2))),1)="-"),СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A2))))

<р>2. Затем перетащите дескриптор заполнения в диапазон, к которому вы хотите применить эту формулу, и вы получите последнее вхождение определенного символа «-», см. снимок экрана:

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

Найти последнее вхождение символа с помощью пользовательской функции

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

<р>1. Откройте рабочий лист, который вы хотите использовать.

<р>2. Удерживайте клавиши ALT + F11, чтобы открыть окно Microsoft Visual Basic для приложений.

<р>3. Затем нажмите «Вставить» > «Модуль» и вставьте следующий макрос в окно «Модуль».

Код VBA: найти последнее вхождение символа

<р>4. Затем сохраните и закройте этот код, вернитесь на рабочий лист и введите эту формулу = lastpositionofchar (A2, "-") в пустую ячейку помимо ваших данных, см. снимок экрана:

<р>5. А затем перетащите дескриптор заполнения в диапазон, который вам нужен, чтобы применить эту формулу, и появление последнего символа «-» было извлечено из текстовых строк, как показано на следующем снимке экрана:

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

Найти первое или n-е вхождение символа с формулой

Чтобы получить первую или n-ю позицию определенного символа, примените следующую формулу:

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

<р>2. Затем перетащите маркер заполнения вниз к ячейкам, к которым вы хотите применить эту формулу, и позиции второго символа «-» были рассчитаны, см. снимок экрана:

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

Найти первое или n-е вхождение определенного символа с помощью простой функции

С помощью Kutools for Excel Найдите, где символ появляется Nth в строковой утилите, вы можете быстро и легко получить позицию первого или n-го конкретного символа.

Примечание. Чтобы применить этот поиск, где символ появляется Nth в строке, во-первых, вам следует загрузить Kutools for Excel, а затем быстро и легко применить эту функцию.

После установки Kutools for Excel сделайте следующее:

Например, я хочу получить второе вхождение символа "-", сделайте следующее:

<р>1. Щелкните ячейку, в которую вы хотите поместить результат.

<р>2. Затем нажмите Kutools > Помощник по формулам > Помощник по формулам, см. снимок экрана:

<р>3. В диалоговом окне помощника по формулам:

Выберите параметр «Поиск» в раскрывающемся списке «Тип формулы»;
Затем выберите «Найти, где символ появляется N-м в строке» в списке «Выбрать формулу»;
В разделе ввода «Аргументы» выберите ячейку, содержащую текстовую строку, которую вы хотите использовать, и введите символ, положение которого вы хотите получить, и, наконец, укажите число, которое вы хотите получить в качестве положения символа.

<р>4.Затем нажмите кнопку «ОК», а затем перетащите маркер заполнения вниз к ячейкам, которые вы хотите получить, чтобы получить следующий результат, который вам нужен.

Есть ли эффективный способ определения последнего совпадения символа/строки в строке с помощью базовых функций? т.е. не последний символ/строка строки, а позиция последнего вхождения символа/строки в строке. Поиск и поиск работают слева направо, поэтому я не могу придумать, как применить без длинного рекурсивного алгоритма. И это решение теперь кажется устаревшим.

Я бы сказал, посмотрите на это с другой точки зрения, это означает, что другие люди также не поняли, что вы имели в виду в своем вопросе, и поэтому решили, что предложение было правильным решением. Даже выбранный вами ответ начинался с «Думаю, я понимаю, что вы имеете в виду». Не критика, а скорее просьба сделать ваши вопросы более понятными, чтобы людям было легче на них отвечать.

Я должен был добавить пример к вопросу, но я думаю, что его было достаточно, чтобы отличить его от запроса о последнем символе строки: поиск и поиск строки запроса content, ' match" – это стандартный термин, а также связанный пример.

14 ответов 14

Кажется, я понял, что вы имеете в виду. Предположим, например, что вам нужен самый правый \ в следующей строке (которая хранится в ячейке A1):

Чтобы получить позицию последнего символа \, используйте следующую формулу:

Это говорит нам о том, что самый правый символ \ находится на 24-м символе. Он делает это, ища "@" и заменяя самый последний "\" на "@". Он определяет последний с помощью

В этом сценарии подстрока представляет собой просто "\" длиной 1, поэтому вы можете не использовать деление в конце и просто использовать:

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

Вот путь к папке без завершающего \

И чтобы получить только имя файла:

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

Поиск позиций определенного слова или символа довольно распространен в Excel.

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

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

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

Какой бы ни была причина, в этом руководстве мы покажем вам два способа найти последний пробел в строке в Excel:

Использование формулы Excel
Использование скрипта VBA

Оглавление

Формула Excel для поиска последнего пробела в строке в Excel

Допустим, у нас есть следующий список строк:

Мы хотим найти позиции последнего пробела в каждой строке.

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

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

Скопируйте эту формулу в остальные ячейки.

Вот результат, который вы должны получить:

Объяснение формулы

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

ПОДСТАВИТЬ(A2, ","). Эта формула удаляет все пробелы из строки в ячейке A2. Он возвращает строку «TheReaperIntheNorth»
LEN(SUBSTITUTE(A2", ",")) — эта формула находит общую длину строки после удаления всех пробелов. Он возвращает значение 19.
ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2, ",")) – эта формула вычитает общую длину строки без пробелов из общей длины исходной строки. Другими словами, эта функция определяет количество пробелов в строке. Формула возвращает значение 4.
ПОДСТАВИТЬ(A2", ","/", ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2", ","))) — эта формула заменяет последний пробел случайным символом ("/" в этом случае, но вы можете использовать любой символ, который вы хотите, убедившись, что он еще не существует в исходной строке). Это делается для того, чтобы его можно было легко найти в строке. Он возвращает строку «Жнец на/севере».
НАЙТИ("/", ПОДСТАВИТЬ(A2", ",/", ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2", ","))))) – Теперь все, что делает функция НАЙТИ, это находит и вернуть позицию символа '/' в строке. Это не что иное, как позиция последнего пробела, который мы заменили.

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

Например, если вы хотите извлечь только текст, который идет после последнего пробела, вы можете обернуть ПРАВИЛЬНУЮ функцию вокруг результата этой формулы, как показано ниже:

Приведенная выше формула извлекает символы LEN(A2)–B2 справа от строки в ячейке A2.

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

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

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

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

Обобщая, n-й последний пробел в строке можно найти по формуле:

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

Использование пользовательской функции VBA для поиска последнего пробела в строке в Excel

Альтернативный способ найти последний пробел в строке – использовать пользовательскую функцию VBA (также известную как функция, определяемая пользователем).

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

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

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

Чтобы найти последний пробел, мы создали функцию с именем FindLastSpace и определили ее следующим образом:

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

Чтобы заставить эту функцию работать, нам нужно сначала поместить ее в редактор Visual Basic.

Вот как добавить эту пользовательскую функцию в Excel, чтобы вы могли использовать ее как обычную функцию на листе:

В главном меню выберите Для разработчиков>Visual Basic.

Откроется окно VBA, выберите Вставить->Модуль, и теперь вы можете начать кодирование.

Введите или скопируйте и вставьте указанный выше скрипт в окно модуля.

Теперь ваша пользовательская функция готова к использованию.

Просто используйте эту функцию на листе Excel, как и любую другую функцию Excel.

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

Когда вы нажимаете клавишу возврата, вы должны увидеть результат в ячейке B2. Скопируйте эту формулу в остальные ячейки столбца B (используя маркер заполнения).

Вот результат, который вы должны получить:

Формула

Объяснение скрипта

Основная часть скрипта находится в следующих нескольких строках:

Этот блок кода начинает цикл в обратном направлении, начиная с последнего символа заданной строки.

Мы используем функцию Mid() для извлечения символа в каждом цикле.

Как только цикл встречает символ пробел, он возвращает позицию (i -1) символа в строке вызывающей функции и останавливает цикл.

Теперь ваша пользовательская функция (FindLastSpace) должна быть доступна на всех листах вашей книги.

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

В этом руководстве мы показали вам два способа поиска последнего пробела в строке.

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

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