Excel: ostatni znak / ciąg pasuje do ciągu

Czy istnieje skuteczny sposób na identyfikację ostatniego dopasowania znaku / ciągu w ciągu za pomocą funkcji podstawowych? Czyli nie ostatni znak / string z łańcucha, ale pozycja ostatniego wystąpienia o charakterze / String jest w ciąg. Searchi findoba działają od lewej do prawej, więc nie mogę wymyślić, jak zastosować bez długiego algorytmu rekurencyjnego. I to rozwiązanie wydaje się przestarzała.

Wraz z nowszymi wersjami programu Excel pojawiają się nowe funkcje, a tym samym nowe metody. Chociaż można go powielać w starszych wersjach (ale nie widziałem go wcześniej), gdy używa się programu Excel O365, można użyć:

=MATCH(2,1/(MID(A1,SEQUENCE(LEN(A1)),1)="Y"))

Można to również wykorzystać do pobrania ostatniej pozycji (nakładających się) podciągów:

=MATCH(2,1/(MID(A1,SEQUENCE(LEN(A1)),2)="YY"))

| Value  | Pattern | Formula                                        | Position |
|--------|---------|------------------------------------------------|----------|
| XYYZ   | Y       | =MATCH(2,1/(MID(A2,SEQUENCE(LEN(A2)),1)="Y"))  | 3        |
| XYYYZ  | YY      | =MATCH(2,1/(MID(A3,SEQUENCE(LEN(A3)),2)="YY")) | 3        |
| XYYYYZ | YY      | =MATCH(2,1/(MID(A4,SEQUENCE(LEN(A4)),2)="YY")) | 4        |

Chociaż pozwala to nam nie używać już dowolnych znaków zastępczych i pozwala na nakładanie się wzorców, „minusem” jest użycie tablicy.

Uwaga: Możesz wymusić to samo zachowanie w starszych wersjach programu Excel za pomocą jednego z nich

=MATCH(2,1/(MID(A2,ROW(A1:INDEX(A:A,LEN(A2))),1)="Y"))

Wprowadzone przez CtrlShiftEnterlub za pomocą wstawki, INDEXaby pozbyć się niejawnego skrzyżowania:

=MATCH(2,INDEX(1/(MID(A2,ROW(A1:INDEX(A:A,LEN(A2))),1)="Y"),))

Myślę, że rozumiem o co ci chodzi. Powiedzmy na przykład, że chcesz najbardziej w prawo \ w następującym ciągu (który jest przechowywany w komórce A1):

Dysk: \ Folder \ Podfolder \ Nazwa pliku. Tekst

Aby uzyskać pozycję ostatniego \, użyłbyś tej formuły:

=FIND("@",SUBSTITUTE(A1,"\","@",(LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))/LEN("\")))

To mówi nam, że najbardziej na prawo \ jest znak 24. Czyni to, szukając „@” i zastępując ostatnie „\” znakiem „@”. Określa ostatni za pomocą

(len(string)-len(substitute(string, substring, "")))\len(substring)

W tym scenariuszu podciąg to po prostu „\”, który ma długość 1, więc możesz zrezygnować z podziału na końcu i po prostu użyć:

=FIND("@",SUBSTITUTE(A1,"\","@",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))

Teraz możemy go użyć, aby uzyskać ścieżkę folderu:

=LEFT(A1,FIND("@",SUBSTITUTE(A1,"\","@",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))

Oto ścieżka do folderu bez końcowego \

=LEFT(A1,FIND("@",SUBSTITUTE(A1,"\","@",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))-1)

Aby uzyskać tylko nazwę pliku:

=MID(A1,FIND("@",SUBSTITUTE(A1,"\","@",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,LEN(A1))

Jednak tutaj jest alternatywna wersja przenoszenia wszystkiego na prawo od ostatniej instancji określonej postaci. Korzystając z tego samego przykładu, zwróciłoby to również nazwę pliku:

=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",LEN(A1))),LEN(A1)))

Co powiesz na utworzenie niestandardowej funkcji i użycie jej w formule? VBA ma wbudowaną funkcję,InStrRev , która robi dokładnie to, czego szukasz.

Umieść to w nowym module:

Function RSearch(str As String, find As String)
    RSearch = InStrRev(str, find)
End Function

A twoja funkcja będzie wyglądać tak (zakładając, że oryginalny ciąg znajduje się w B1):

=LEFT(B1,RSearch(B1,"\"))

tigeravatar i Jean-François Corbett zasugerowali użycie tej formuły do ​​wygenerowania ciągu znaków po ostatnim wystąpieniu znaku „\”

=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",LEN(A1))),LEN(A1)))

Jeśli znakiem używanym jako separator jest spacja „”, należy zmienić formułę na:

=SUBSTITUTE(RIGHT(SUBSTITUTE(A1," ",REPT("{",LEN(A1))),LEN(A1)),"{","")

Nie trzeba wspominać, że znak „{” można zastąpić dowolnym znakiem, który „normalnie” nie występuje w przetwarzanym tekście.

Właśnie wymyśliłem to rozwiązanie, nie wymaga VBA;

Znajdź ostatnie wystąpienie „_” w moim przykładzie;

=IFERROR(FIND(CHAR(1);SUBSTITUTE(A1;"_";CHAR(1);LEN(A1)-LEN(SUBSTITUTE(A1;"_";"")));0)

Wyjaśnione na lewą stronę;

SUBSTITUTE(A1;"_";"") => replace "_" by spaces
LEN( *above* ) => count the chars
LEN(A1)- *above*  => indicates amount of chars replaced (= occurrences of "_")
SUBSTITUTE(A1;"_";CHAR(1); *above* ) => replace the Nth occurence of "_" by CHAR(1) (Nth = amount of chars replaced = the last one)
FIND(CHAR(1); *above* ) => Find the CHAR(1), being the last (replaced) occurance of "_" in our case
IFERROR( *above* ;"0") => in case no chars were found, return "0"

Mam nadzieję, że to było pomocne.

Możesz użyć tej funkcji, którą utworzyłem, aby znaleźć ostatnią instancję ciągu w ciągu.

Upewnij się, że przyjęta formuła programu Excel działa, ale jest zbyt trudna do odczytania i użycia. W pewnym momencie musisz podzielić się na mniejsze części, aby można było je utrzymać. Moja funkcja poniżej jest czytelna, ale to nie ma znaczenia, ponieważ wywołujesz ją w formule przy użyciu nazwanych parametrów. To sprawia, że ​​korzystanie z niego jest proste.

Public Function FindLastCharOccurence(fromText As String, searchChar As String) As Integer
Dim lastOccur As Integer
lastOccur = -1
Dim i As Integer
i = 0
For i = Len(fromText) To 1 Step -1
    If Mid(fromText, i, 1) = searchChar Then
        lastOccur = i
        Exit For
    End If
Next i

FindLastCharOccurence = lastOccur
End Function

Używam tego w ten sposób:

=RIGHT(A2, LEN(A2) - FindLastCharOccurence(A2, "\"))

Biorąc pod uwagę część komentarza dokonanego przez @SSilk, moim ostatecznym celem było doprowadzenie wszystkiego do prawej strony tego ostatniego wystąpienia. Alternatywnym podejściem z bardzo prostą formułą jest skopiowanie kolumny (powiedzmy A) ciągów znaków i skopiowanie (powiedzmy) Kolumna B) zastosuj Znajdź i zamień. Na przykład biorąc przykład:Drive:\Folder\SubFolder\Filename.ext

Zwraca to, co pozostaje (tutaj Filename.ext) po ostatnim wystąpieniu dowolnej postaci (tutaj \), co i tak czasami jest celem i ułatwia znalezienie pozycji ostatniej takiej postaci za pomocą krótkiej formuły, takiej jak:

=FIND(B1,A1)-1

Jestem trochę spóźniony na imprezę, ale może to może pomóc. Link w pytaniu miał podobną formułę, ale moja używa instrukcji IF (), aby pozbyć się błędów.

Jeśli nie boisz się klawiszy Ctrl + Shift + Enter, możesz całkiem dobrze poradzić sobie z formułą tablicową.

Ciąg (w komórce A1): „one.two.three.four”

Formuła:

{=MAX(IF(MID(A1,ROW($1:$99),1)=".",ROW($1:$99)))}  use Ctrl+Shift+Enter

Wynik: 14

Pierwszy,

ROW($1:$99)

zwraca tablicę liczb całkowitych od 1 do 99: {1,2,3,4,...,98,99} .

Kolejny,

MID(A1,ROW($1:$99),1)

zwraca tablicę ciągów o długości 1 znalezionych w ciągu docelowym, a następnie zwraca puste ciągi po osiągnięciu długości ciągu docelowego: {"o","n","e",".",..."u","r","","",""...}

Kolejny,

IF(MID(I16,ROW($1:$99),1)=".",ROW($1:$99))

porównuje każdy element w tablicy z ciągiem „.” i zwraca indeks znaku w ciągu lub FAŁSZ:{FALSE,FALSE,FALSE,4,FALSE,FALSE,FALSE,8,FALSE,FALSE,FALSE,FALSE,FALSE,14,FALSE,FALSE.....}

Ostatni, ubiegły, zeszły,

=MAX(IF(MID(I16,ROW($1:$99),1)=".",ROW($1:$99)))

zwraca maksymalną wartość tablicy: 14

Zaletą tej formuły jest to, że jest krótka, stosunkowo łatwa do zrozumienia i nie wymaga żadnych unikalnych znaków.

Wady to wymagane użycie Ctrl + Shift + Enter i ograniczenie długości łańcucha. Można to obejść za pomocą wariantu pokazanego poniżej, ale ta odmiana korzysta z funkcji OFFSET (), która jest funkcją zmienną (czytaj: wolno).

Nie jestem pewien, jaka jest szybkość tej formuły w porównaniu z innymi.

Wariacje:

=MAX((MID(A1,ROW(OFFSET($A$1,,,LEN(A1))),1)=".")*ROW(OFFSET($A$1,,,LEN(A1)))) works the same way, but you don't have to worry about the length of the string

=SMALL(IF(MID(A1,ROW($1:$99),1)=".",ROW($1:$99)),2) determines the 2nd occurrence of the match

=LARGE(IF(MID(A1,ROW($1:$99),1)=".",ROW($1:$99)),2) determines the 2nd-to-last occurrence of the match

=MAX(IF(MID(I16,ROW($1:$99),2)=".t",ROW($1:$99))) matches a 2-character string **Make sure you change the last argument of the MID() function to the number of characters in the string you wish to match!

W nowszych wersjach programu Excel (2013 i nowsze) wypełnienie flash może być prostym i szybkim rozwiązaniem patrz: Korzystanie z Flash Fill w programie Excel .

Prostym sposobem na to w VBA jest:

YourText = "c:\excel\text.txt"
xString = Mid(YourText, 2 + Len(YourText) - InStr(StrReverse(YourText), "\" ))

Bardzo późno na imprezę, ale prostym rozwiązaniem jest użycie VBA do stworzenia niestandardowej funkcji.

Dodaj funkcję do VBA w skoroszycie, arkuszu lub module VBA

Function LastSegment(S, C)
    LastSegment = Right(S, Len(S) - InStrRev(S, C))
End Function

Następnie formuła komórkowa

=lastsegment(B1,"/")

w komórce, a ciąg do przeszukania w komórce B1 zapełni komórkę tekstem kończącym ostatnie „/” z komórki B1. Bez ograniczenia długości, bez niejasnych wzorów. Jedynym minusem jest to, że potrzebuję skoroszytu z obsługą makr.

W ten sposób można wywołać dowolną funkcję VBA użytkownika, aby zwrócić wartość do formuły komórkowej, w tym jako parametr do wbudowanej funkcji Excel.

Jeśli zamierzasz intensywnie korzystać z tej funkcji, powinieneś sprawdzić wielkość liter, gdy znak nie znajduje się w ciągu, a następnie ciąg jest pusty itp.

Komórka A1 = find/the/position/of/the last slash

prosty sposób to odwrócić tekst, a następnie normalnie znaleźć pierwszy ukośnik. Teraz możesz uzyskać długość pełnego tekstu minus ten numer.

Tak jak:

=LEN(A1)-FIND("/",REVERSETEXT(A1),1)+1

Zwraca 21, pozycję ostatniego /