Spájanie textu v Exceli: znak &, CONCATENATE a TEXTJOIN
Meno a priezvisko z dvoch stĺpcov, kód zložený z viacerých častí, veta s číslom: na to všetko stačí znak & alebo jedna z troch funkcií. Naučíš sa, kedy ktorú použiť.
Vyskúšaj si to: Meno a priezvisko
Do bunky C2 napíš =A2&" "&B2 a stlač Enter. Znak & spojí meno, medzeru a priezvisko do jedného textu.
| A | B | C | |
|---|---|---|---|
| 1 | Meno | Priezvisko | Celé meno |
| 2 | Tibor | Šimko |
Klikni na bunku a jej adresa sa vloží do vzorca.
Riešenie: =A2&" "&B2
A2&" "&B2 spojí „Tibor“, medzeru a „Šimko“. Bez medzery v úvodzovkách by vzniklo „TiborŠimko“.
Znak &
Najjednoduchší spôsob: =A2&" "&B2. Znak & spojí dve hodnoty do jedného textu. Všetko,
čo nie je v bunke, píšeš do úvodzoviek, aj medzeru " ", čiarku ", " či spojovník
"-". Spájať sa dá aj číslo, napríklad =B2&" ks".
Funkcia CONCATENATE
=CONCATENATE(text1; [text2]; …)
CONCATENATE spojí argumenty za sebou, môže ich byť najviac 255. Robí to isté ako &:
=CONCATENATE(A2;" ";B2). Každú bunku však treba zadať zvlášť, rozsah ako A2:C2 do jedného textu
nespojí. Microsoft ju ponecháva kvôli kompatibilite so staršími verziami a odporúča novšiu CONCAT.
Funkcie CONCAT a TEXTJOIN
CONCAT funguje ako CONCATENATE, ale berie aj rozsahy: =CONCAT(A2:C2) spojí tri
bunky. Medzery medzi ne nevloží.
=TEXTJOIN(oddeľovač; ignorovať_prázdne; text1; [text2]; …)
TEXTJOIN vloží medzi hodnoty oddeľovač, napríklad čiarku s medzerou. Druhý argument TRUE vynechá prázdne bunky, takže v texte nebudú dve čiarky za sebou. CONCAT aj TEXTJOIN sú v Exceli 2019 a novšom a v Microsoft 365. Starší Excel ich nepozná a vráti #NAME?.
Spojenie textu verzus zlúčenie buniek
Zlúčenie buniek na karte Domov spojí bunky do jednej veľkej, ale obsah ponechá len z ľavej hornej bunky. Text z ostatných sa stratí. Keď chceš spojiť obsah, napíš vzorec do novej bunky.
Príklady
Adresa z troch stĺpcov
=A2&", "&B2&" "&C2 | A | B | C | |
|---|---|---|---|
| 1 | Ulica | PSČ | Obec |
| 2 | Hlavná 12 | 040 01 | Košice |
| 3 | |||
| 4 | Adresa: | Hlavná 12, 040 01 Košice |
Zoznam s oddeľovačom
TEXTJOIN spojí mená z rozsahu a prázdnu bunku vynechá.
=TEXTJOIN(", ";TRUE;A2:A5) | A | B | |
|---|---|---|
| 1 | Služba | |
| 2 | Marta | |
| 3 | Juraj | |
| 4 | ||
| 5 | Peter | |
| 6 | ||
| 7 | Slúžia: | Marta, Juraj, Peter |
Číslo vo vete
Pri spájaní sa číslo zmení na text bez formátu: 0,25 ostane 0,25, nie 25 %, a 12,50 € sa ukáže ako 12,5. Percento preto vypočítaj a zaokrúhli priamo vo vzorci.
="Odpovedalo "&ROUND(B1/B2*100;0)&" % ľudí" | A | B | |
|---|---|---|
| 1 | Odpovedí | 46 |
| 2 | Oslovených | 120 |
| 3 | ||
| 4 | Veta: | Odpovedalo 38 % ľudí |
Pevný počet desatinných miest či dátum v texte nastaví funkcia TEXT, ktorá číslo prevedie na text v zadanom formáte.
Časté chyby a čo znamenajú
Chyba #VALUE! pri znaku +
Znak + sčítava čísla. Text sčítať nevie, preto =A2+B2 s menami vráti #VALUE!. Text spája znak &.
Slová sú zlepené
Medzera sa nepridá sama. Daj ju medzi časti ako text: =A2&" "&B2.
Chyba #NAME?
Text vo vzorci nie je v úvodzovkách, napríklad =B2&ks namiesto =B2&" ks".
Alebo Excel nepozná CONCAT či TEXTJOIN, lebo je starší ako verzia 2019.
Dátum sa zmenil na číslo
Excel ukladá dátum ako poradové číslo dňa. Pri spájaní sa ukáže toto číslo, napríklad 46000. Dátum v texte sformátuj funkciou TEXT.
Cvičenia
Päť úloh od ľahkých po náročnejšie. Vzorec píš do vyznačenej bunky, kontrolu spustíš klávesom Enter alebo tlačidlom Skontrolovať. Kontrola vzorec vyskúša aj s inými menami a číslami.
Cvičenie 1: Kód produktu
ľahkáKód produktu sa skladá zo skratky kategórie, spojovníka (znak -) a čísla, napríklad „TR-1042“. Do C2 napíš vzorec, ktorý ho poskladá. Vzorec sa skopíruje do C3 až C5.
| A | B | C | |
|---|---|---|---|
| 1 | Kategória | Číslo | Kód |
| 2 | TR | 1042 | |
| 3 | KO | 2210 | |
| 4 | TR | 1077 | |
| 5 | NA | 3005 |
Klikni na bunku a jej adresa sa vloží do vzorca. Bunky s prerušovaným okrajom ukážu, čo vzorec vypočíta po skopírovaní.
Riešenie: =A2&"-"&B2
Znak & spojí aj text s číslom: TR, spojovník a 1042 dajú „TR-1042“. Po skopírovaní sa odkazy posunú na ďalšie riadky.
Cvičenie 2: Meno s titulom
ľahkáDo D2 napíš vzorec s funkciou CONCATENATE, ktorý spojí titul, meno a priezvisko s medzerami, napríklad „Ing. Eva Horváthová“.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Titul | Meno | Priezvisko | Celé meno |
| 2 | Ing. | Eva | Horváthová |
Klikni na bunku a jej adresa sa vloží do vzorca.
Riešenie: =CONCATENATE(A2;" ";B2;" ";C2)
CONCATENATE(A2;" ";B2;" ";C2) spojí päť častí. Rovnaký výsledok dá =A2&" "&B2&" "&C2.
Cvičenie 3: Zoznam bez prázdnych buniek
strednáDo B8 napíš vzorec s funkciou TEXTJOIN, ktorý spojí krúžky z B2 až B6 do jedného textu. Krúžky oddeľ čiarkou a medzerou, napríklad „šach, plávanie, gitara“. Prázdne bunky vynechaj.
| A | B | |
|---|---|---|
| 1 | Deň | Krúžok |
| 2 | Pondelok | šach |
| 3 | Utorok | plávanie |
| 4 | Streda | |
| 5 | Štvrtok | gitara |
| 6 | Piatok | florbal |
| 7 | ||
| 8 | Všetky krúžky |
Klikni na bunku a jej adresa sa vloží do vzorca.
Riešenie: =TEXTJOIN(", ";TRUE;B2:B6)
TEXTJOIN vloží ", " medzi každé dve hodnoty a s TRUE preskočí prázdnu stredu. Funguje v Exceli 2019 a novšom a v Microsoft 365.
Cvičenie 4: Začiatočné písmeno
strednáDo menoviek treba skrátené meno, napríklad „E. Horváthová“: prvé písmeno mena, bodka, medzera a priezvisko. Do C2 napíš vzorec, ktorý sa skopíruje do C3 až C5.
| A | B | C | |
|---|---|---|---|
| 1 | Meno | Priezvisko | Menovka |
| 2 | Eva | Horváthová | |
| 3 | Štefan | Novotný | |
| 4 | Ľubica | Gálová | |
| 5 | Ondrej | Čech |
Klikni na bunku a jej adresa sa vloží do vzorca. Bunky s prerušovaným okrajom ukážu, čo vzorec vypočíta po skopírovaní.
Riešenie: =LEFT(A2;1)&". "&B2
LEFT(A2;1) vráti „E“, k nemu sa pripojí bodka s medzerou a priezvisko. Druhý argument LEFT sa dá vynechať, predvolene vráti jeden znak.
Cvičenie 5: Veta s percentom
náročnejšiaDo B4 napíš vzorec, ktorý vytvorí vetu „Splnené na 87 %“. Percento vypočítaj z B1 a B2 a zaokrúhli ho funkciou ROUND na celé číslo. Medzi číslom a znakom % je medzera.
| A | B | |
|---|---|---|
| 1 | Predané (ks) | 260 |
| 2 | Plán (ks) | 300 |
| 3 | ||
| 4 | Stav plánu |
Klikni na bunku a jej adresa sa vloží do vzorca.
Riešenie: ="Splnené na "&ROUND(B1/B2*100;0)&" %"
Pri spájaní sa číslo zmení na text bez formátu. 260 / 300 je 0,8666…, preto ho treba vynásobiť 100 a zaokrúhliť. ROUND vráti 87 a & z neho urobí časť vety.
Otázky a odpovede
Ako spojiť dva stĺpce v Exceli?
Do prvého riadku nového stĺpca napíš =A2&" "&B2 a vzorec skopíruj nadol. Keď chceš mať
v tabuľke len výsledok bez vzorcov, skopíruj stĺpec a prilep ho ako hodnoty.
Aký je rozdiel medzi CONCATENATE a CONCAT?
CONCAT je novšia a berie aj rozsahy, napríklad A2:D2. CONCATENATE funguje vo všetkých verziách, ale každú bunku treba vypísať zvlášť. V novom súbore stačí znak & alebo CONCAT.
Ako spojiť text s čiarkou medzi hodnotami?
Funkciou TEXTJOIN: =TEXTJOIN(", ";TRUE;A2:A20). Vloží čiarku medzi každé dve hodnoty a prázdne
bunky vynechá.
Ako spojiť bunky bez straty údajov?
Nepoužívaj zlúčenie buniek, to ponechá len ľavú hornú hodnotu. Obsah spoj vzorcom so znakom & do novej bunky.