OpenOffice.cz - web věnovaný kancelářským balíkům OpenOffice.org

jasanek

Člen

Registrace: 28. 12. 2021

Příspěvků: 4

Rozložení textu z buňky do více buněk

Dobrý den, potřeboval bych pomoct s rozložením textu z buňky, který je namíchán z více textů do několika buněk. Texty jsou různě dlouhé, takže nelze použít funkci Text do sloupců. Samozřejmě mám i příklad toho co bych potřeboval s vlastním pokusem. Něco se mi podařilo, ale s některými částmi si nevím rady.
https://nahrajsoubor.cz/8da6d165d59ed27 … 9cc193afb7

Ještě doplním, v příkladu jsem použil xxxx, ve skutečnosti to jsou 4 různé znaky, ale vždy 4.

Děkuji za rady a tipy.

Editoval jasanek (28. 12. 2021 13:27:31)

kamlan

Člen

Registrace: 15. 9. 2016

Příspěvků: 368

Re: Rozložení textu z buňky do více buněk

Asi zkusit něco vracet regulárními výrazy přes fci REGEX.

=REGEX(A1;"\s.{4}\s")
=REGEX(A1;"\d+,-")
=REGEX(A1;"\d+$")

V úvahu možná připadá i menu Nástroje/ Možnosti/ LibreOffice Calc/ Výpočty -> Povolit regulární výrazy ve vzorcích. Ale nevím v jakých vzorcích se to povolení regulárů projeví a v jakých to beztak nebude mít vliv. 

Šlo by to i makrem, ale bylo by to spíš neohrabanější, služba com.sun.star.util.TextSearch není v LibreBasicu moc uživatelsky příjemná.

Sub vypisSubregulary
	dim p(), s$
	p=vratSubregulary("215050s sadlsdghlsghlsgja xxxx 4645,- 51451", "(\d+s)\s([^ ]+)\s(.{4})\s(\d+,-)\s(\d+)")
	s=join(p, chr(13))
	msgbox s
End Sub

Function vratSubregulary(optional s$, optional sreg$) as array 'vrátí všechny regulární podvýrazy které daný regulár obsahuje - tedy podvýrazy jež jsou v reguláru v závorkách (podvýraz)
	dim pole(2), oHledani, oHledaniParam, oNalez, oStart, oEnd, pocet%, i%, a()
	oHledani=CreateUnoService("com.sun.star.util.TextSearch") 'textové hledání proto, abych dostal subReguláry
	oHledaniParam=CreateUnoStruct("com.sun.star.util.SearchOptions")
	With oHledaniParam
	  .algorithmType=com.sun.star.util.SearchAlgorithms.REGEXP 'regulárem
	  .searchString=sreg 'celý regulár který nalézám
	End With
	oHledani.setOptions(oHledaniParam)
	oNalez=oHledani.searchForward(s,0,len(s)) 'prohledávat nalezený řetězec od začátku do konce
	oStart=oNalez.startOffset() 'pole se začátky pozic subRegulárů
	oEnd=oNalez.endOffset() 'pole s konci pozic subRegulárů
	rem nahrazování subRegulárů za potřebné znaky
	pocet=oNalez.subRegExpressions()-1
	if pocet=-1 then exit function
	redim a(pocet-1) 'pole pro subreguláry
	for i=1 to pocet 'projíždí nalezené subvýrazy
		a(i-1)=Mid(s,oStart(i)+1,oEnd(i)-oStart(i)) 'znaky ze subReguláru
	next i
	vratSubregulary=a()
End Function

jasanek

Člen

Registrace: 28. 12. 2021

Příspěvků: 4

Re: Rozložení textu z buňky do více buněk

Dobrý den.
Myslel jsem, že by to šlo i pomocí funkcí left, right, mid, find, asi nějak takto:

Zadání:
215050s sadls dghlsgh lsgja xxxx 4645,- 51451

od první mezery zkraje vše vlevo - to vím
=LEFT(A1;FIND(" ";A1)-1)
215050s / výsledná hodnota

od první mezery zkraje vše vpravo
=TRIM(MID(A6;FIND(" ";A6)+1;LEN(A6)))
sadls dghlsgh lsgja xxxx 4645,- 51451 / mezihodnota

od znaků ",-" vpravo - to vím
=TRIM(MID(A1;FIND("- ";A1)+1;LEN(A1)))
51451 / výsledná hodnota

vypsat vše od znaků ",-" doleva, může být včetně znaků
potřeboval bych poradit se vzorcem
sadls dghlsgh lsgja xxxx 4645,- / mezihodnota

od první mezery zezadu vypsat vše doprava
potřeboval bych poradit se vzorcem
4645,- / výsledná hodnota

od první mezery zezadu vypsat vše doleva
potřeboval bych poradit se vzorcem
sadls dghlsgh lsgja xxxx / mezihodnota

od první mezery zezadu vypsat vše doprava
potřeboval bych poradit se vzorcem
xxxx / výsledná hodnota

od první mezery zezadu vypsat vše doleva
potřeboval bych poradit se vzorcem
sadls dghlsgh lsgja / výsledná hodnota

S Calc moc neumím, proto bych potřeboval poradit se vzorci viz. výše. Zároveň bych vzorec asi neřetězil v jedné buňce, ale použil bych více buněk pro mezioperace.

Děkuji.

Editoval jasanek (10. 1. 2022 13:34:32)

lp.

Člen

Registrace: 24. 9. 2009

Příspěvků: 844

Re: Rozložení textu z buňky do více buněk

Zkuste použít funkci FILTERXML - dělá do zjednodušeně "text do sloupců", jen vzorcem.

Tady je jen jeden oddělovač - mezera: 

=FILTERXML("<t><y>"&SUBSTITUTE(TRIM(A1);" ";"</y><y>")&"</y></t>";"/t/y")

Více oddělovačů - buď regulární výraz nebo vícekrát substitute - pro ilustraci je to nepřehledné.

Položky v řádku - funkce TRANSPOSE

Pokud máme pole, získat z něho jednu hodnotu je snadné (choose, index, ...), tady je použit index a vybrána poslední hodnota:

=INDEX(FILTERXML("<t><y>"&SUBSTITUTE(TRIM(A1);" ";"</y><y>")&"</y></t>";"//y");COUNTA(FILTERXML("<t><y>"&SUBSTITUTE(TRIM(A1);" ";"</y><y>")&"</y></t>";"//y")))

A tady je druhá hodnota:

=INDEX(FILTERXML("<t><y>"&SUBSTITUTE(TRIM(A1);" ";"</y><y>")&"</y></t>";"//y");2)

Editoval lp. (10. 1. 2022 14:44:41)

kamlan

Člen

Registrace: 15. 9. 2016

Příspěvků: 368

Re: Rozložení textu z buňky do více buněk

Tím REGEXem mi to přijde jednodušší ale ani tak to není žádné terno. Syntaxi regulárních výrazů je v Nápovědě. REGEX má to výhodu že může vracet hodnotu třeba od třetí mezery atd. Na těch pozicích u kraje si s MID aspol. vystačíte, ale v těch prostředcích je ten REGEX snažší.

vypsat vše od znaků ",-" doleva, může být včetně znaků

=TRIM(MID(A1;1;FIND(",-";A1)+1))

tedy od prvního znaku až do znaku ",-"

od první mezery zezadu vypsat vše doprava

=REGEX(A1;" [^ ]+$")

výraz který vrátí vše od mezery po níž následují až do konce jiné znaky než mezera, tedy od poslední mezery do konce

od první mezery zezadu vypsat vše doleva

=REGEX(A1;"(^.+) [^ ]+$";"$1")

řetězec brát jako část od začátku k poslední mezeře (^.+) a k tomu poslední mezera až nemezerovéZnakyDoKonce [^ ]+$; a jako náhradu celého výrazu dát jen tu první část čili $1

V tom dalším v té ukázce co kouskujete z podvýrazů se nevyznám, ale na to by vám ukázky výše mohly stačit. Ale ještě jinak. Pokud potřebujete třeba něco od druhý mezery po třetí mezeru, tak

=REGEX(A1;"( [^ ]+)";;2)

Tohle najde druhej výskyt výrazu: mezera a po ní NěcoJinýhoNežMezera+vícekrát

A třeba od třetí mezery do pátý mezery - byť to už není tak přehledné.

=REGEX(A1;"[^ ]+ [^ ]+ [^ ]+ ([^ ]+ [^ ]+ ).+";"$1")

Zase kombinace nemezerovýchZnakůVícekrát a k tomu mezera; a pak v závorkách () část kterou potřebujete vrátit.

Podobně můžete dostat jednotlivý část řetězce třeba po jednom

=REGEX(A1;"(^[^ ]+) ([^ ]+) ([^ ]+) ([^ ]+) ([^ ]+) ([^ ]+) (.+$)";"$1")
=REGEX(A1;"(^[^ ]+) ([^ ]+) ([^ ]+) ([^ ]+) ([^ ]+) ([^ ]+) (.+$)";"$2")
...
=REGEX(A1;"(^[^ ]+) ([^ ]+) ([^ ]+) ([^ ]+) ([^ ]+) ([^ ]+) (.+$)";"$7")

Anebo si třeba dvě různý části z toho spojit

=REGEX(A1;"(^[^ ]+) ([^ ]+) ([^ ]+) ([^ ]+) ([^ ]+) ([^ ]+) (.+$)";"$1@$2")
=REGEX(A1;"(^[^ ]+) ([^ ]+) ([^ ]+) ([^ ]+) ([^ ]+) ([^ ]+) (.+$)";"$1&$5")
=REGEX(A1;"(^[^ ]+) ([^ ]+) ([^ ]+) ([^ ]+) ([^ ]+) ([^ ]+) (.+$)";"$3+++$7")

jasanek

Člen

Registrace: 28. 12. 2021

Příspěvků: 4

Re: Rozložení textu z buňky do více buněk

Děkuji za odpovědi.
Jde o to, že zadání je různé a skládá se z pěti částí, které potřebuji rozdělit do pěti sloupců. Jedná se o větší zadání, které se mění, proto bych to chtěl zautomatizovat pomocí vzorců, před ručním manuální úpravou.

První část je číslo o 7-mi znacích, případně i jednom písmenu. Druhá část je různě dlouhá a může obsahovat i mezery či některé znaky. Třetí část se skládá ze čtyř znaků. Čtvrtá část je různě dlouhé číslo zakončené ,- a pátá část je různě dlouhé číslo nebo i žádné. Viz. příklad dole. Z toho důvodu je problém použít například jen funkci rozdělit do sloupců např. dle mezer nebo znaků.
Proto se to snažím rozsekat pomocí vzorců.
Zkusil jsem použít vzorec REGEX, ale hází mi to chybu #NAME?

=REGEX(A1;" [^ ]+$")

Zadání

215050s sadlsdghlsghlsgja xxxx 4645,- 51451
215050 sadlsdgh lsghlsgja xxxx 46445,- 515152
215050 sadls-dghlsghlsgja xxxx 464,- 51453
215050 sadl sdghlsghls gja xxxx 46445,-

Výsledek:

A             B                       C       D           E
215050s       sadlsdghlsghlsgja       xxxx    4645,-      51451
215050        sadlsdgh lsghlsgja      xxxx    46445,-     515152
215050        sadls-dghlsghlsgja      xxxx    464,-       51453
215050        sadl sdghlsghls gja     xxxx    46445,-

Děkuji.

EDIT: K REGEXu mi píše neznámá funkce. Mám Apache OpenOffice 4.1.9
EDIT2: Teď jsem aktualizoval na 4.1.11, ale také nezná.

Editoval jasanek (10. 1. 2022 19:22:09)

kamlan

Člen

Registrace: 15. 9. 2016

Příspěvků: 368

Re: Rozložení textu z buňky do více buněk

Já používám LibreOffice a v AOO vám to nemohu odzkoušet. Ale možná by vám tam chodil tenhle vzorec založený tedy na makru.

Function VRATSUBREGULARY(s$, ii&) 'ii-tý subregulár z řetězce s, regulár je zadán v .searchString
	dim pole(2), oHledani, oHledaniParam, oNalez, oStart, oEnd, pocet%, i%, a()
	oHledani=CreateUnoService("com.sun.star.util.TextSearch") 'textové hledání proto, abych dostal subReguláry
	oHledaniParam=CreateUnoStruct("com.sun.star.util.SearchOptions")
	With oHledaniParam
	  .algorithmType=com.sun.star.util.SearchAlgorithms.REGEXP 'regulárem
	  .searchString="^(\d{6}.?)\s+(.+?)\s+([^ ]{4})\s+(\d+,-)\s*(\d*)$" 'celý regulár který nalézám
	End With
	oHledani.setOptions(oHledaniParam)
	oNalez=oHledani.searchForward(s,0,len(s)) 'prohledávat nalezený řetězec od začátku do konce
	oStart=oNalez.startOffset() 'pole se začátky pozic subRegulárů
	oEnd=oNalez.endOffset() 'pole s konci pozic subRegulárů
	rem nahrazování subRegulárů za potřebné znaky
	pocet=oNalez.subRegExpressions()-1
	if pocet=-1 then exit function
	redim a(pocet-1) 'pole pro subreguláry
	for i=1 to pocet 'projíždí nalezené subvýrazy
		a(i-1)=Mid(s,oStart(i)+1,oEnd(i)-oStart(i)) 'znaky ze subReguláru
	next i
	VRATSUBREGULARY=a(ii-1)
End Function

Použití:
=VRATSUBREGULARY(A1;1)
=VRATSUBREGULARY(A1;2)
=VRATSUBREGULARY(A1;3)
=VRATSUBREGULARY(A1;4)
=VRATSUBREGULARY(A1;5)

Krom REGEX mě napadá použít MID a pozice podvýrazů získávat funkcí SEARCH, do níž lze též vložit reguláry. Ale je potřeba je zapnout v Nástroje/ Možnosti/ LibreOffice Calc/ Výpočty

=LEFT(A1;FIND(" ";A1)-1)
=MID(A1;1+FIND(" ";A1);SEARCH("\s+.{4}\s+\d+,-";A1)-FIND(" ";A1))
=MID(A1;1+SEARCH("\s.{4}\s+\d+,-";A1);5)
=MID(A1;SEARCH("\d+,-";A1);SEARCH(",-";A1)-SEARCH("\d+,-";A1))
=TRIM(MID(A1;2+SEARCH(",-";A1);LEN(A1)))

Kdybyste přešel na LibreOffice, tak to lze tedy rozkouskovat docela snadno REGEXem: \s+ je kdyby tam bylo víc mezer za sebou či tabulátor apod.

=REGEX(A1;"^(\d{6}.?)\s+(.+?)\s+([^ ]{4})\s+(\d+,-)\s*(\d*)$";"$1")
=REGEX(A1;"^(\d{6}.?)\s+(.+?)\s+([^ ]{4})\s+(\d+,-)\s*(\d*)$";"$2")
=REGEX(A1;"^(\d{6}.?)\s+(.+?)\s+([^ ]{4})\s+(\d+,-)\s*(\d*)$";"$3")
=REGEX(A1;"^(\d{6}.?)\s+(.+?)\s+([^ ]{4})\s+(\d+,-)\s*(\d*)$";"$4")
=REGEX(A1;"^(\d{6}.?)\s+(.+?)\s+([^ ]{4})\s+(\d+,-)\s*(\d*)$";"$5")

jasanek

Člen

Registrace: 28. 12. 2021

Příspěvků: 4

Re: Rozložení textu z buňky do více buněk

Děkuji. To je přesně ono. Počet upravovaných položek se liší, takže mám na druhém listu rozkopírované vzorce do více řádek než je vstup, který je na prvním listu. Vzorce které nemají vstupní data samozřejmě vypisují chybu. Je možné jej zabalit do iferroru. Jsem zvyklí na excel a v tomto programu se vzorci moc pracovat neumím. Toto mám do práce, kde je zastaralý systém na ulehčení, zjednodušení a zrychlení práce. Myslel jsem si, že pomocí automatického filtru potom vyberu NEPRÁZDNÉ buňky, vyberu a zkopíruji a v novém sešitu už budu pracovat s čistými daty, filtrovat, řadit apod.