Excel Forum - Porady, Pomoc, Excel Help, Excel FAQ - Pozycja trzeciej (lub kolejnej) spacji w tekście.

bartss · Starszy Forumowicz Wersja: Win Office 365 Posty: 47

Napisałem sobie taką formułę, która wyciąga mi imię i nazwisko z raportu.
Czy jest inny (prostszy) sposób na napisanie tej formuły tak, aby wyszukiwała w tym przypadku 3 spacje?

Nie wchodzi w grę jakakolwiek ingerencja w treść raportu - więc "tekst jako kolumny" odpada.

Z góry dziękuję!

trzecia spacja.xlsx

Pobierz Plik ściągnięto 10 raz(y) 8.96 KB

Kaper · Wysłany: 29-03-2021, 13:39

Twoja formuła zwracała Jana Kowalskiego z dodatkową spacją na końcu. sprawdź taką korektę:

Kod:

=FRAGMENT.TEKSTU(A1;ZNAJDŹ(" ";A1;1)+1;ZNAJDŹ(" ";A1;ZNAJDŹ(" ";A1;ZNAJDŹ(" ";A1;1)+1)+1)-ZNAJDŹ(" ";A1;1)-1)

A inna propozycja (w tym łatwość zmiany liczby spacji) to np.:

Kod:

=FRAGMENT.TEKSTU(A1;ZNAJDŹ(" ";A1;1)+1;ZNAJDŹ("#";PODSTAW(A1;" ";"#";3))-ZNAJDŹ(" ";A1;1)-1)

Do takich operacji zwykle nieźle się sprawdzają wyrażenia regularne, ale to oznacza, że obecnie należy sięgnąć np. po VBA, żeby z nich korzystać. Być może wkrótce a pomocą funkcji LAMBDA, ale ... jeszcze nie bardzo jest dostępna - patrz http://www.excelforum.pl/...bda-vt70515.htm

bartss · Starszy Forumowicz Wersja: Win Office 365 Posty: 47

Dziekuję!
Co do pierwszej części - prawda, nie zauwazyłem tej spacji. :oops:

Co do drugiej, czy mógłbyś opisać tę składnię? Co w tym przypadku oznacza "#"? Znam używanie # tylko w dynamiczncyh zakresach, do rozlewających się formuł.

Lambda- oglądałem własnie wczoraj na anglojęzycznym YT lambdę, chciałem przetestować, ale niestety ze smutkiem odkryłem że jej jeszcze nie mam dostępnej.

Kaper · Wysłany: 29-03-2021, 14:01

# "nic nie oznacza"
to jakikolwiek znak, którego nie spodziewasz się w tekście.
Podstaw wstawia go zamiast trzeciej spacji w tym fragmencie:

Kod:

PODSTAW(A1;" ";"#";3)

a potem ten sam znak jest wyszukiwany żeby znaleźć jego pozycję w tym zmodyfikowanym łańcuchu tekstowym w

Kod:

ZNAJDŹ("#";...

Kaper · Wysłany: 29-03-2021, 14:35

Albo może taka funkcja użytkownika:
(ma 4 argumenty, przy czym czwarty opcjonalny, jeśli nie podany - będzie użyta spacja

Kod:

Function FragmentTekstuMiedzyZnakami(podziel As String, pocz As Integer, kon As Integer, Optional znak As String = " ") As Variant
'
' (c) 2021 Kaper
' http://www.excelforum.pl/topics2/pozycja-trzeciej-lub-kolejnej-spacji-w-tekscie-vt70543.htm
'
' argumenty:
' podziel - łańcuch zawierający fragment
' pocz - od której spacji (lub innego znaku) - można wpisać 0 - wtedy od początku tekstu
' kon do której spacji. Jeśli kon>liczba spacji w tekście - do końca tekstu
' znak - używany do dzielenia (jeśli nie podano - spacja)
'
' przykłady:
' ?FragmentTekstuMiedzyZnakami("Ala ma kota", 0, 3) = "Ala ma kota"
' ?FragmentTekstuMiedzyZnakami("Ala ma kota", 1, 2) = "ma"
' ?FragmentTekstuMiedzyZnakami("Jan-Ola-Piotr-Ewa", 1, 3, "-") = "Ola-Piotr"
' ?FragmentTekstuMiedzyZnakami("Jan-Ola-Piotr-Ewa", 1, 3) = #N/D (błąd, bo nie ma spacji w tekście)
' ?FragmentTekstuMiedzyZnakami("Jan-Ola-Piotr-Ewa", 6, 7, "-") = #ARG! (błąd, bo nie ma 6 myślników)
' itp.

Dim tablica As Variant, i As Integer, wynik As String
If InStr(podziel, znak) = 0 Then
FragmentTekstuMiedzyZnakami = CVErr(xlErrNA)
Else
tablica = Split(podziel, znak)
If kon < pocz Or pocz > UBound(tablica) Then
FragmentTekstuMiedzyZnakami = CVErr(xlErrValue)
Else
wynik = tablica(pocz)
For i = pocz + 1 To kon - 1
If i <= UBound(tablica) Then wynik = wynik & znak & tablica(i)
Next i
FragmentTekstuMiedzyZnakami = wynik
End If
End If
End Function

A esencja funkcji bez obsługi błędów i narzutu na komentarze:

Kod:

Function FragmentTekstuMiedzyZnakami(podziel As String, pocz As Integer, kon As Integer, Optional znak As String = " ") As Variant
Dim tablica As Variant, i As Integer, wynik As String
tablica = Split(podziel, znak)
wynik = tablica(pocz)
For i = pocz + 1 To kon - 1
If i <= UBound(tablica) Then wynik = wynik & znak & tablica(i)
Next i
FragmentTekstuMiedzyZnakami = wynik
End Function

Zobacz w załączniku.

fragmentTekstuMiędzyZnakami-zObsługąBłędów.xlsm

Pobierz Plik ściągnięto 6 raz(y) 16.36 KB

bartss · Starszy Forumowicz Wersja: Win Office 365 Posty: 47

Za każdym razem, dowiaduję się, że jeszcze tyle nauki przede mną... :D
Dziękuję za wyjaśnienie!

Staszek1406 · Wysłany: 30-03-2021, 14:23

Witam,
UDF zaproponowany przez Kapera w poście wyżej, działa poprawnie, gdy parametry: "pocz" i "kon" są wpisywane ręcznie.
Próbując rozdzielić takie dane (dwa przykładowe wiersze) do kolejnych kolumn napotkałem na problemy:

Kod:

3;1;0;3
3

- nie można zastosować przeciągnij na kolejne kolumny;
- nie wyświetla wyniku, gdy w komórce występuje tylko jedna wartość (nie ma separatora).

Jak poprawić tę funkcję, aby również obejmowała podane przypadki?

Pozdrawiam

Kaper · Wysłany: 09-04-2021, 14:43

Chyba nie rozumiem w jakiej sytuacji nie działa. Umieść proszę plik z danymi jako załącznik na forum (i zdradź spodziewany wynik).

Staszek1406 · Wysłany: 09-04-2021, 17:04

Witam,
Przepraszam, trochę pospieszyłem się z tym postem.
Funkcja poprawnie rozdziela elementy komórki, gdy jest ich więcej niż jeden. Na wyznaczenie pocz i kon zastosowałem taki zapis; pocz; NR.KOlLUMNY(A1) - 1; kon; NR.KOLUMNY(A1). Przeciągając na kolejne kolumny działa poprawnie.
Problem pojawia się, gdy w komórce jest jeden element (brak znaku rozdzielającego) zastosowany zapis wyżej nie działa prawidłowo (błąd #N/D) . Wydaje mi się, że funkcja nie uwzględnia tego przypadku. Chodzi o to, by w komórce obok był ten sam element, czyli A2=4; B2=4.
Myślę, że wystarczająco przedstawiłem zagadnienie.

Pozdrawiam
Stanisław

Maciej Gonet · Wysłany: 09-04-2021, 17:22

Jak się domyślam, koncepcja tej funkcji była taka, że gdy nie ma separatora, to ma zwrócić błąd #N/D.
Jeśli wolisz, żeby w takiej sytuacji zwracała cały pierwotny tekst, to zmień ten fragment:

Kod:

If InStr(podziel, znak) = 0 Then
FragmentTekstuMiedzyZnakami = podziel ' CVErr(xlErrNA)

Tylko wtedy będzie taki sam wynik niezależnie jakie podamy numery separatorów, czyli przy "przeciąganiu" formuły wynik zawsze będzie pełnym tekstem - tyle razy ile razy skopiujesz.
Jeśli o to chodziło, to tak zrób.

Staszek1406 · Wysłany: 10-04-2021, 11:26

Witam,
dziękuję za poprawienie kodu UDFa. Dzięki tej poprawce funkcja stała się bardziej uniwersalna. Działa zgodnie z oczekiwaniami.

Pozdrawiam
Stanisław

Kaper · Wysłany: 12-04-2021, 12:47