В этом руководстве вы узнаете, как найти позицию последнего вхождения символа в строке в Excel.
Несколько дней назад коллега столкнулся с этой проблемой.
У него был список URL-адресов, как показано ниже, и ему нужно было извлечь все символы после последней косой черты («/»).
Так, например, с https://example.com/archive/Январь ему пришлось извлечь «январь».
Это было бы очень просто, если бы в URL-адресах была только одна косая черта.
У него был огромный список из тысяч URL-адресов разной длины и с различным количеством косых черт.
В таких случаях хитрость заключается в том, чтобы найти позицию последнего появления косой черты в URL-адресе.
В этом уроке я покажу вам два способа сделать это:
- Использование формулы Excel
- Использование пользовательской функции (созданной через VBA)
Получение последней позиции символа с помощью формулы Excel
Когда у вас есть позиция последнего вхождения, вы можете просто извлечь что-нибудь справа от него, используя функцию ВПРАВО.
Вот формула, которая находит последнюю позицию косой черты и извлекает весь текст справа от нее.
= ПРАВО (A2; LEN (A2) -НАЙТИ ("@", ПОДСТАВИТЬ (A2, "/", "@", LEN (A2) -LEN (ПОДСТАВИТЬ (A2, "/", ""))), 1 ))
Как работает эта формула?
Давайте разберем формулу и объясним, как работает каждая ее часть.
- ЗАМЕНА (A2; ”/”,“”) - Эта часть формулы заменяет косую черту пустой строкой. Так, например, если вы хотите найти вхождение любой строки, кроме косой черты, используйте ее здесь.
- LEN (A2) -LEN (ЗАМЕНА (A2; ”/”,“”)) - Эта часть сообщит вам, сколько косых черт в строке. Он просто вычитает длину строки без косой черты из длины строки с косой чертой.
- ПОДСТАВИТЬ (A2, ”/”, ”@”, LEN (A2) -LEN (ПОДСТАВИТЬ (A2, ”/”, ””))) - Эта часть формулы заменит последнюю косую черту на @. Идея состоит в том, чтобы сделать этого персонажа уникальным. Вы можете использовать любой персонаж, какой захотите. Просто убедитесь, что он уникален и его еще нет в строке.
- НАЙТИ («@», ПОДСТАВИТЬ (A2, «/», «@», LEN (A2) -LEN (ПОДСТАВИТЬ (A2, «/», »»))), 1) - Эта часть формулы даст вам позицию последней косой черты.
- LEN (A2) -FIND («@», ПОДСТАВИТЬ (A2, «/», «@», LEN (A2) -LEN (ПОДСТАВИТЬ (A2, «/», »»))), 1) - Эта часть формулы сообщает нам, сколько символов стоит после последней косой черты.
- = ПРАВО (A2; LEN (A2) -НАЙТИ («@», ПОДСТАВИТЬ (A2, «/», «@», LEN (A2) -LEN (ПОДСТАВИТЬ (A2, «/», »»))), 1 )) - Теперь это просто даст нам строку после последней косой черты.
Получение последней позиции символа с помощью пользовательской функции (VBA)
Вышеупомянутая формула прекрасна и работает как шарм, но она немного сложна.
Если вам удобно использовать VBA, вы можете использовать настраиваемую функцию (также называемую функцией, определяемой пользователем), созданную с помощью VBA. Это может упростить формулу и сэкономить время, если вам придется делать это часто.
Давайте воспользуемся тем же набором данных URL-адресов (как показано ниже):
Для этого случая я создал функцию с именем LastPosition, которая находит последнюю позицию указанного символа (в данном случае это косая черта).
Вот формула, которая сделает это:
= ПРАВО (A2; LEN (A2) -LastPosition (A2; "/") + 1)
Вы можете видеть, что это намного проще, чем тот, который мы использовали выше.
Вот как это работает:
- LastPosition - наша настраиваемая функция - возвращает позицию косой черты. Эта функция принимает два аргумента - ссылку на ячейку с URL-адресом и символ, позицию которого нам нужно найти.
- Затем функция ВПРАВО дает нам все символы после косой черты.
Вот код VBA, создавший эту функцию:
Функция LastPosition (rCell As Range, rChar As String) 'Эта функция выдает последнюю позицию указанного символа' Этот код был разработан Sumit Bansal (https://trumpexcel.com) Dim rLen As Integer rLen = Len (rCell) For i = rLen To 1 Step -1 If Mid (rCell, i - 1, 1) = rChar Then LastPosition = i Exit Function End If Next i End Function
Чтобы эта функция работала, вам нужно поместить ее в редактор VB. После этого вы можете использовать эту функцию, как любую другую обычную функцию Excel.
Вот шаги, чтобы скопировать и вставить этот код в серверную часть VB:
Вот шаги, чтобы поместить этот код в редактор VB:
- Перейдите на вкладку Разработчик.
- Выберите вариант Visual Basic. Это откроет редактор VB в бэкэнде.
- На панели Project Explorer в редакторе VB щелкните правой кнопкой мыши любой объект книги, в которую вы хотите вставить код. Если вы не видите Project Explorer, перейдите на вкладку View и нажмите Project Explorer.
- Перейдите во вкладку "Вставить" и нажмите "Модуль". Это вставит объект модуля для вашей книги.
- Скопируйте и вставьте код в окно модуля.
Теперь формула будет доступна на всех листах книги.
Обратите внимание, что вам необходимо сохранить книгу в формате .XLSM, поскольку в ней есть макрос. Кроме того, если вы хотите, чтобы эта формула была доступна во всех используемых вами книгах, вы можете сохранить ее в личной книге макросов или создать на ее основе надстройку.
Вам также могут понравиться следующие руководства по Excel:
- Как узнать количество слов в Excel.
- Как использовать ВПР с несколькими критериями.
- Найдите последнее вхождение подстановочного значения в списке в Excel.
- Извлечь подстроку в Excel.