Spojování textových řetězců

Spojování textů (textových řetězců) v Excelu patří sice mezi triviální záležitosti,
přesto věřím, že si v dnešním článku každý najde to své. Pojďme na to.

Nebudu zde dlouze popisovat něco, co lze vyjádřit snadno obrázkem.

Řetězce spojujeme s pomocí ampersandu (znaku &), nikoliv znakem plus jako u čísel. Výsledkem spojení textového řetězce a čísla je také textový řetězec. Ve vzorcích se textový řetězec objevuje v uvozovkách a mezera je znak jako každý jiný (na listu si vyzkoušejte funkci ZNAK(32)). Odřádkování mají na starosti také znaky ASCII tabulky (zpravidla se používá ZNAK(13) spolu se ZNAK(10), někdy stačí i samotné užití ZNAK(10)). Pozn. Ručního zalomení prostého textu v buňce dosáhnete stiskem ALT+ENTER.

Tip
Víte, jak jednoduše zřetězíte obsah několika buněk? Ve volné buňce se vzorcem odkažte na danou oblast a ještě v režimu editace stiskněte F9. Dojde k částečnému výpočtu (dosazení hodnot). Výsledek ořízněte o úvodní znak rovná se a složené závorky a máte hotovo.

Tip na zřetězení (dílčí výpočet s pomocí F9)

Výstup není úplně ideální. Kupříkladu pro Data / Ověření bychom se museli ještě zbavit uvozovek a naopak užití ve VBA a jeho Array vyžaduje jako oddělovač čárku. Ukážeme si později, jak na to s vlastní funkcí.

A jak se provádí zřetězení pod VBA?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

Sub SpojovaniRetezcuA()

Dim strJmeno As String
Dim strPrijmeni As String
Dim strJmenoPrijmeni As String
Dim strDvaRadky As String
Dim strTabulator As String

strJmeno = "František"
strPrijmeni = "Koudelka"

'prosté spojení řetězců
'nepoužívejte znak + pro spojení
strJmenoPrijmeni = strJmeno & " " & strPrijmeni

MsgBox strJmenoPrijmeni

'odřádkování
'konstanta Visual Basicu představující dva znaky ASCII
'13 (CR, Carriage Return, "návrat vozíku")
'a 10 (LF, Line Feed, "posun o řádek")
'lze použít i konstanty vbLf nebo vbNewLine
strDvaRadky = "1. řádek" & vbCrLf & "2. řádek"

MsgBox strDvaRadky

'tabulátor, odřádkování
strTabulator = "Položka 1: " & vbTab & "nenalezena" _
& vbNewLine _
& "Položka 2: " & vbTab & 36

MsgBox strTabulator

End Sub

Ve VBA ještě zůstaneme a úlohu rozšíříme pro více textových vstupů.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

Sub Techniky()

Const cstrOddelovac As String = ", "

Dim aRetezce()
Dim strTempA As String
Dim strTempB As String
Dim rngBunka As Range

'a) Technika nabalování

'pro každou buňku v oblasti...
For Each rngBunka In Range("rngRetezce")
'nabalení řetězce a oddělovače
strTempA = strTempA & rngBunka.Text & cstrOddelovac
Next rngBunka

'oříznutí posledního oddělovače
strTempA = Left(strTempA, Len(strTempA) - Len(cstrOddelovac))

'b) Technika Join

'překlopení jednosloupcové pojmenované oblasti buněk
'do jednorozměrného (vodorovného) pole
aRetezce = WorksheetFunction.Transpose(Range("rngRetezce"))

'sloučení položek pole do řetězce
'mezi nimi volitelný oddělovač
strTempB = Join(aRetezce, cstrOddelovac)

End Sub

Výše uvedenou proceduru odkrokujte. Technika nabalování řetězců je nejrozšířenější, ale nepatří mezi efektivní. Vyžaduje spoustu paměťově náročných operací a ve finále musíme řetězec ještě ořezávat. Práci s polem a funkcemi Join (a její sestřičkou Split) si vryjte do paměti. Bude se vám hodit v mnoha kulišárnách.

Nyní naše programové snažení zapracujeme do tří vlastních funkcí.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79

Public Function epfRETEZA(Oblast As Range, Optional Oddelovac As String = _
vbNullString) As String

Dim rngBunka As Range
Dim strTemp As String

'pro každou buňku v oblasti
For Each rngBunka In Oblast
'pokud není buňka prázdná...
If Not IsEmpty(rngBunka) Then
'nabalování řetězců
strTemp = strTemp & rngBunka.Text & Oddelovac
End If
Next rngBunka

'řetězec oříznut od posledního oddělovače
epfRETEZA = Left(strTemp, Len(strTemp) - Len(Oddelovac))

End Function

Public Function epfRETEZB(Oblast As Range, Optional Oddelovac As String = _
vbNullString, Optional Uvozovky As Boolean = False, Optional Docistit As _
Boolean = True) As String

Dim strTemp As String
Dim strUvozovky As String

With WorksheetFunction

'Transpose (funkce listu TRANSPOZICE)
'převedení svislé oblasti
'do vodorovného jednorozměrného pole

'Join
'převod pole do řetězce položek
'mezi nimi volitelně oddělovac

strTemp = Join(.Transpose(Oblast), Oddelovac)

strUvozovky = Chr(34)

'vložit položky do uvozovek?
If Uvozovky = True Then
'ano
strTemp = strUvozovky & Replace(strTemp, Oddelovac, strUvozovky & _
Oddelovac & strUvozovky) & strUvozovky
End If

'odstranit nadbytečné mezery?
If Docistit = True Then
'ano
'Trim (funkce listu PROČISTIT)
'ořezání od nadbytečných mezer
strTemp = .Trim(strTemp)
End If

End With

epfRETEZB = strTemp

End Function

Public Function epfODSTAVEC(Oblast As Range) As String

'převedení svislé oblast
'do vodorovného jednorozměrného pole
'funkce listu TRANSPOZICE

'převod pole do řetězce položek
'mezi nimi mezera

'ořezání od nadbytečných mezer
'funkce listu PROČISTIT

With WorksheetFunction
epfODSTAVEC = .Trim(Join(.Transpose(Oblast), Chr(32)))
End With

End Function

První dvě funkce epfRETEZA a epfRETEZB se liší užitou technikou. Druhá z nich pak ještě nabízí možnost zbavení se nadbytečných mezer. Funkce epfODSTAVEC se snad hodí v případě, kdy odstavec zkopírovaný přes schránku se v Excelu rozkouskuje do jednotlivých buněk a vy je budete chtít znovu poskládat do bloku. Pozn. Tento článek se nevěnuje funkcím do detailu, přesto se sluší říci alespoň to, že jejich parametry deklarované jako Optional jsou volitelné.

Příloha
excel_spojovani_retezcu.zip

Uložit článek jako PDF