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