Excel přidá text a číslo na zadanou pozici buňky
V Excelu je přidávání textů nebo čísel do buněk velmi běžnou prací. Například přidání mezery mezi jména, přidání předpony nebo přípony do buněk, přidání pomlček ke společenským číslům. Zde v tomto tutoriálu uvádí téměř všechny scénáře přidávání v aplikaci Excel a poskytuje odpovídající metody pro vás.
Poznámka
V tomto tutoriálu vytvořím několik příkladů pro vysvětlení metod, můžete změnit odkazy podle potřeby, když použijete níže uvedený kód nebo vzorce VBA, nebo si můžete přímo stáhnout ukázky pro vyzkoušení metod.
Kliknutím stáhnete ukázkový soubor
Tato část uvádí různé metody přidávání znaků na začátek každé buňky, jak je uvedeno níže:
1.1 Přidejte na začátek buněk pomocí vzorce
Zde si můžete vybrat jeden ze tří níže uvedených vzorců:
Operátor zřetězení vzorce 1 „&“
Spojte více textů dohromady znakem ampersand "&".
"text"&buňka
Funkce Formula2 CONCATENATE
Funkce CONCATENATE se používá ke spojení textů dohromady.
CONCATENATE("text";buňka)
Funkce Formula3 CONCAT
Toto je nová funkce, která se objevuje pouze v Excelu 2019, Office 365 a Excelu online.
CONCAT("text";buňka)
V obecných vzorcích: buňka je odkaz na buňku na text, ke kterému chcete přidat předponu, a text je text, který byl použit k přidání do buňky.
Pomocí výše uvedeného příkladu můžete použít vzorce v řádku vzorců takto:
"&"
=$E$3&B3 or "Iphone"&B3
Funkce CONCATENATE
=CONCATENATE($E$3,B3) or =CONCATENATE("Iphone",B3)
Funkce CONCAT
=CONCAT($E$3;B3) or =CONCAT("Iphone";B3)
Stisknout vstoupit , abyste získali výsledek, poté přetáhněte úchyt automatického vyplňování dolů a přidejte text do každé buňky v rozsahu B3:B6.
| Pozor: Text by měl být uzavřen do dvojitých uvozovek, nebo vzorec vrátí chybovou hodnotu #NAME?. Pokud používáte odkaz na buňku na text, nezapomeňte použít absolutní odkaz, můžete stisknutím klávesy F4 změnit relativní odkaz na absolutní odkaz. |
1.2 Přidat na začátek buněk pomocí Flash Fill
Pokud používáte Excel 2013 nebo novější verze, výkonná nová funkce, Flash Fill, může vyplnit buňky na základě výše uvedených buněk, které jste zadali automaticky.
Vyberte buňku vedle prvních původních dat, ručně zadejte první data s textem předpony, jak je uvedeno níže:
Poté do buňky níže pokračujte v psaní druhého údaje s textem předpony, během psaní se zobrazí seznam v šedé barvě, stiskněte vstoupit umožní Flash Fill automaticky vyplnit data.
Pokud se seznam náhledů nevygeneruje, přejděte na kartu Domů a klikněte Vyplnit > Flash Fill spustit ručně.
1.3 Přidejte na začátek buněk pomocí kódu VBA
Pokud jste obeznámeni s kódem VBA, zde představujeme kód VBA pro přidání textu na začátek každé buňky v rozsahu.
1. Vyberte rozsah buněk, do kterých chcete přidat text předpony, a stiskněte Další + F11 klávesy pro povolení Microsoft Visual Basic pro aplikace okno.
2. Poté v překryvném okně klikněte Vložit > Modul pro vložení nového prázdného modulu.
3. Zkopírujte a vložte níže uvedený kód VBA do nového modulu.
VBA: Přidat na začátek buněk
Sub AppendToExistingOnLeft()
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = "Iphone" & c.Value
Next
End Sub| Pozor: v kódovém skriptu, iphone je text, který chcete přidat na začátek vybraných buněk, změňte jej podle potřeby. |
4. lis F5 klíč nebo klik Běh tlačítko 
v okně aktivujte kód VBA.
Nyní je do všech vybraných buněk na začátku přidán text „Iphone“.
Kromě přidávání textu na začátek buněk je také běžné přidávání textu na konec buněk. Zde vezmeme přidání jednotky na konec každé buňky pro příklady, jak je uvedeno níže:
2.1 Přidejte na konec buněk pomocí vzorce
Zde si můžete vybrat jeden ze tří níže uvedených vzorců:
Operátor zřetězení vzorce 1 „&“
Spojte více textů dohromady znakem ampersand "&".
Buňka&"text"
Funkce Formula2 CONCATENATE
Funkce CONCATENATE se používá ke spojení textů dohromady.
CONCATENATE(buňka "text")
Funkce Formula3 CONCAT
Toto je nová funkce, která se objevuje pouze v Excelu 2019, Office 365 a Excelu online.
CONCAT(buňka "text")
V obecných vzorcích: buňka je odkaz na buňku na text, ke kterému chcete přidat příponu, a text je text, který byl použit k přidání do buňky.
Pomocí výše uvedeného příkladu můžete použít vzorce v řádku vzorců takto:
"&"
=B3&$E$3 or =B3&"kg"
Funkce CONCATENATE
=CONCATENATE(B3; $E$3) or =CONCATENATE(B3; "Kg")
Funkce CONCAT
=CONCAT(B3; $E$3) or =CONCAT(B3; "Kg")
Stisknout vstoupit , abyste získali výsledek, poté přetáhněte úchyt automatického vyplňování dolů a přidejte text na konec každé buňky v rozsahu B3:B6.
| Pozor: Text by měl být uzavřen do dvojitých uvozovek, nebo vzorec vrátí chybovou hodnotu #NAME?. Pokud používáte odkaz na buňku na text, nezapomeňte použít absolutní odkaz, můžete stisknutím klávesy F4 změnit relativní odkaz na absolutní odkaz. |
2.2 Přidejte na konec buněk pomocí Flash Fill
Pokud používáte Excel 2013 nebo novější verze, výkonná nová funkce, Flash Fill, může vyplnit buňky na základě výše uvedených buněk, které jste zadali automaticky.
Vyberte buňku vedle prvních původních dat, ručně zadejte první data s textem přípony, jak je uvedeno níže:
Poté aktivujte buňku pod prvním výsledkem, klikněte Data > Flash Fill, všechny níže uvedené buňky včetně aktivní buňky budou vyplněny automaticky.
2.3 Přidejte na konec buněk pomocí kódu VBA
Zde je kód VBA, který může přidat text na konec každé buňky v rozsahu.
1. Vyberte rozsah buněk, do kterých chcete přidat text přípony, a stiskněte Další + F11 klávesy pro povolení Microsoft Visual Basic pro aplikace okno.
2. Poté v překryvném okně klikněte Vložit > Modul pro vložení nového prázdného modulu.
3. Zkopírujte a vložte níže uvedený kód VBA do nového modulu.
VBA: Přidat na konec buněk
Sub AppendToExistingOnRight()
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = c.Value & "Kg"
Next
End Sub| Pozor: v kódovém skriptu, Kg je text, který chcete přidat na konec vybraných buněk, změňte jej podle potřeby. |
4. lis F5 klíč nebo klik Běh tlačítko v okně aktivujte kód VBA.
Nyní byl na konec každé buňky přidán text "Kg".
Pro většinu z vás je přidávání textů na začátek nebo konec buněk snadné, ale přidávání textů do středu buněk může být poněkud obtížné. Vezměte příklad, jak je znázorněno na obrázku níže, přidáním oddělovače, jako je dvojtečka : na konec druhého znaku každé buňky v rozsahu B3:B7.
3.1 Přidejte za n-tý znak řetězce podle vzorce
Zde jsou čtyři vzorce, které vám pomohou zvládnout tuto úlohu, vyberte si jeden, který chcete použít:
Formula1 Kombinujte funkce LEFT a RIGHT
LEFT(buňka, n) & "text" & RIGHT(buňka, DÉLKA(buňka) -n)
Formule 2 KOMBINUJTE funkce CONCATENATE(CONCAT), RIGHT a LEN
CONCATENATE(LEFT(buňka, n); "text", RIGHT(buňka, DÉLKA(buňka) -n))
Or
CONCAT(LEFT(buňka, n); "text", RIGHT(buňka, DÉLKA(buňka) -n))
Formule3 REPLACE funkce
REPLACE(buňka; n+1; 0; "text")
V obecných vzorcích: buňka je odkaz na buňku na text, do kterého chcete přidat text uprostřed, a text je text, který byl použit k přidání do buňky, n je číslo určující, za kterým znakem v textovém řetězci chcete přidat přidej text.
Pro řešení výše uvedené úlohy použijte následující vzorce:
Kombinujte funkce LEFT a RIGHT
=LEFT(B3; 2) & ":" & RIGHT(B3; DÉLKA(B3) -2) or =LEFT(B3; 2) & $E$3 & RIGHT(B3, DÉLKA(B3) -2)
KOMBINOVAT funkce CONCATENATE(CONCAT), RIGHT a LEN
=CONCATENATE(LEFT(B3; 2); ":", RIGHT(B3; DÉLKA(B3) -2)) or =CONCAT (LEFT(B3; 2); ":", RIGHT(B3, DÉLKA(B3) -2))
Funkce REPLACE
=REPLACE(B3; 2+1; 0; ":") or =REPLACE(B3; 2+1; 0; $E$3)
Stisknout vstoupit , abyste získali výsledek, poté přetáhněte úchyt automatického vyplňování dolů a přidejte text za druhý znak každé buňky z rozsahu B3:B7.
| Pozor: Text by měl být uzavřen do dvojitých uvozovek, nebo vzorec vrátí chybovou hodnotu #NAME?. Pokud používáte odkaz na buňku na text, nezapomeňte použít absolutní odkaz, můžete stisknutím klávesy F4 změnit relativní odkaz na absolutní odkaz. |
3.2 Přidejte za n-tý znak řetězce pomocí Flash Fill
Pokud používáte Excel 2013 nebo novější verze, Flash Fill, může vyplnit buňky na základě výše uvedených buněk, které jste zadali automaticky.
Vyberte buňku vedle prvních původních dat, ručně zadejte první data s dvojtečkou za druhým znakem, jak je uvedeno níže:
Poté aktivujte buňku pod prvním výsledkem, stiskněte Ctrl + E klávesy pro aktivaci Flash Fill všechny níže uvedené buňky včetně aktivní buňky byly vyplněny automaticky.
| Pozor: Pokud jste v systému MAC, stiskněte klávesy Command + E. |
3.3 Přidejte za n-tý znak řetězce pomocí kódu VBA
Kód VBA může dělat tuto práci také.
1. lis Další + F11 klávesy pro povolení Microsoft Visual Basic pro aplikace okno.
2. Poté v překryvném okně klikněte Vložit > Modul pro vložení nového prázdného modulu.
3. Zkopírujte a vložte níže uvedený kód VBA do nového modulu.
VBA: Přidat do středu buněk
Sub AddToMidduleOfString()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
Rng.Value = VBA.Left(Rng.Value, 2 & ":" & VBA.Mid(Rng.Value, 3, VBA.Len(Rng.Value) - 1)
Next
End Sub| Pozor: v kódovém skriptu, 2 je číslo definuje pozici, za kterou chcete přidat text, : je text, ke kterému chcete přidat, 3 je součet 2 a délka textu, který přidáte. Vezměte další příklad, chcete-li přidat „a“ za první znak řetězce „AB“, skript by měl být Rng.Value = VBA.Left(Rng.Value, 1) & " a " & VBA.Mid(Rng.Value, 4, VBA.Len(Rng.Value) – 1) |
4. lis F5 klíč nebo klik Běh tlačítko v okně aktivujte kód VBA. Zobrazí se dialogové okno pro výběr buněk pro přidání textu.
5. cvaknutí OK. Nyní byl za druhý znak každé buňky v rozsahu B19:B23 přidán text ":".
Pokud vás nebaví vzorce a VBA, můžete vyzkoušet výkonný a šikovný nástroj, Kutools pro Excel, která vám s tím může pomoci doplnit text vlastnost.
Tato funkce Přidat text může:
- Přidejte text před první znak
- Přidejte text za poslední znak
- Přidejte text na určené pozice
- Prodloužení
Přidejte text před velká písmena
Přidejte text před malá písmena
Přidejte text před velká/malá písmena
Přidejte text před číselné znaky
Kliknutím získáte 30denní bezplatnou zkušební verzi.
Vyberte buňky, do kterých chcete přidat text, a poté kliknutím použijte Přidat text Kutools > Text > doplnit text.
4.1 Přidejte text před první znak nebo za poslední znak
v doplnit text zadejte text, který chcete přidat do buněk v Text textové pole a poté zaškrtněte Před první postavou v Pozice sekce.
klikněte Podat žádost or Ok dokončení přidávání.
Chcete-li přidat text za poslední znak, zadejte text, který chcete přidat do buněk v Text textové pole a poté zaškrtněte Po posledním znaku v Pozice sekce.
klikněte Podat žádost or Ok dokončení přidávání.
4.2 Přidejte text na určené pozice
Někdy můžete chtít přidat text doprostřed buňky nebo přidat text na několik pozic buňky. Předpokládejme přidání oddělovače "-" za třetí a sedmou číslici sociálních čísel, jak je uvedeno níže:
v doplnit text dialogovém okně zadejte text do Text textové pole a zaškrtněte Specifikovat poté zadejte pozice pomocí čárky pro oddělení do textového pole a poté klikněte Ok or Podat žádost.
4.3 Rozšíření nástroje Přidat text
Kromě přidání textu na normální pozice (začátek, konec a zadaná pozice), doplnit text nástroj také podporuje přidání textu
- Před velkými písmeny
- Před malými písmeny
- Před velkými/malými písmeny
- Před numerickými znaky
v doplnit text dialogovém okně zadejte text do Text textové pole a zaškrtněte Pouze přidat do a poté klikněte na rozevírací nabídku níže a vyberte požadovanou operaci.
Tato část poskytuje vzorce pro přidávání textu před nebo za první konkrétní text v Excelu.
5.1 Přidejte text před první konkrétní text
Chcete například přidat text „(prodej)“ před první text „cena“ v buňce, jak je uvedeno níže:
V Excelu mohou tuto úlohu vyřešit funkce REPLACE a SUBSTITUTE.
Funkce REPLACE
REPLACE(buňka,NAJÍT("najít_text",buňka),0,"přidat_text")
Funkce SUBSTITUTE
SUBSTITUTE(buňka,"najít_text","nahradit_text"1)
V tomto případě použijte výše uvedené vzorce:
=REPLACE(B4,FIND("cena",B4);0,"(prodej)")
Or
=SUBSTITUTE(B4,"cena","(prodej)cena",1)
Argumenty vzorce v tomto případě jsou
Buňka: B4,
Find_text: cena,
Add_text: (prodej),
Replace_text:(prodej)cena.
Stisknout vstoupit , abyste získali první výsledek přidávání, pak přetáhněte úchyt automatického vyplňování přes buňky, které tento vzorec potřebují.
5.2 Přidejte text za první konkrétní text
Chcete například přidat symbol měny „$“ za první dvojtečku „:“ v buňce, jak je znázorněno na snímcích níže:
V Excelu mohou tuto úlohu vyřešit funkce REPLACE a SUBSTITUTE.
Funkce REPLACE
REPLACE(buňka,NAJÍT("najít_text",buňka),najít délku_textu,"nahradit_text")
Funkce SUBSTITUTE
SUBSTITUTE(buňka,"najít_text","nahradit_text")
V tomto případě použijte výše uvedené vzorce:
=REPLACE(B12,FIND(":",B12);1,":$")
Or
=SUBSTITUTE(B12,":",":$")
Argumenty vzorce v tomto případě jsou
Buňka: B12,
Najít_text: :,
Nahradit_text::$.
Najít_délku_textu:1
Stisknout vstoupit , abyste získali první výsledek přidávání, pak přetáhněte úchyt automatického vyplňování přes buňky, které tento vzorec potřebují.
V této části tutoriálu představuje tři různé metody (vzorec, Najít a nahradit, VBA), které vám pomohou přidat znaky před nebo za každé slovo buňky.
| Před každé slovo přidejte znaky |
 |
| Přidejte znaky za každé slovo |
 |
6.1 Přidejte znak(y) před nebo za každé slovo pomocí vzorce
K vyřešení této úlohy lze použít funkci SUBSTITUTE, obecné vzorce jako jsou tyto:
Přidejte před každé slovo
"znak"&NAHRADIT(řetězec, " "," znak")
Přidejte za každé slovo
SUBSTITUTE(řetězec," ","znak ")&"znak"
Předpokládejme, že přidáte „+“ před každé slovo v seznamu B3:B6, použijte prosím vzorec takto:
="+"&SUBSTITUTE(B3; " "," +")
Stisknout vstoupit , abyste získali první výsledek, poté přetáhněte rukojeť automatického vyplňování dolů, abyste získali všechny výsledky.
Předpokládejme, že za každé slovo v seznamu B11:B14 přidáte ($), použijte níže uvedený vzorec:
=SUBSTITUTE(B11," ","($) ")&"($)"
Stisknout vstoupit , abyste získali první výsledek, poté přetáhněte rukojeť automatického vyplňování dolů, abyste získali všechny výsledky.
6.2 Přidejte znak(y) před nebo za každé slovo pomocí funkce Najít a nahradit
Pokud chcete přidat znaky před každé slovo kromě prvního nebo přidat znaky za každé slovo kromě posledního, jak je znázorněno na obrázku níže, vestavěný Excel Najít a nahradit nástroj tuto práci zvládne.
Přidejte před každé slovo kromě prvního
Přidejte za každé slovo kromě posledního
1. Vyberte buňky, do kterých chcete přidat znaky před nebo za každé slovo, a stiskněte Ctrl + H klávesy pro povolení Najít a nahradit dialog.
2. Do pole zadejte mezeru Najít to, co textového pole a poté zadejte nové znaky s mezerou Nahradit textové pole a nakonec klepněte na Nahradit vše .
| Operace | obecně | Příklad |
| Před každé slovo přidejte znaky | Za mezerou přidejte nové znaky | Přidat (prodej) před každé slovo |
| Operace | obecně | Příklad |
| Přidejte znaky za každé slovo | Nové znaky následovaly s mezerou | Přidejte - za každé slovo |
Zde jsou dva kódy VBA pro přidání znaků před nebo za každé slovo. Spusťte prosím kód podle následujících kroků.
1. lis Další + F11 klávesy pro povolení Microsoft Visual Basic pro aplikace okno.
2. V rozbalovacím okně klikněte na Vložit > Modul pro vložení nového prázdného modulu.
3. Zkopírujte a vložte níže uvedený kód VBA do modulu.
Přidejte před každé slovo
Sub InsertCharBeforeWord()
'UpdateByExtendOffice
Dim ws As Worksheet
Dim xRg As Range
Dim xSRg As Range
Dim xCell As Range
Dim xInStr As String
Dim xArr As Variant
Dim xValue As String
On Error Resume Next
Set xSRg = Application.Selection
Set xRg = Application.InputBox("Select cells(continuous):", "Kutools for Excel", xSRg.Address, , , , , 8)
If xRg Is Nothing Then Exit Sub
xInStr = Application.InputBox("Type characters you want to add:", "Kutools for Excel", "", , , , , 2)
If StrPtr(xInStr) = 0 Then Exit Sub
Application.ScreenUpdating = False
For Each xCell In xRg
xArr = Split(xCell.Text, " ")
xValue = ""
For Each xStr In xArr
If Trim(xStr) <> "" Then
If xValue = "" Then
xValue = xInStr & Trim(xStr)
Else
xValue = xValue & " " & xInStr & Trim(xStr)
End If
End If
Next
xCell.Value = xValue
Next
Application.ScreenUpdating = True
End SubPřidejte za každé slovo
Sub InsertCharAfterWord()
'UpdateByExtendOffice
Dim ws As Worksheet
Dim xRg As Range
Dim xSRg As Range
Dim xCell As Range
Dim xInStr As String
Dim xArr As Variant
Dim xValue As String
On Error Resume Next
Set xSRg = Application.Selection
Set xRg = Application.InputBox("Select cells(continuous):", "Kutools for Excel", xSRg.Address, , , , , 8)
If xRg Is Nothing Then Exit Sub
xInStr = Application.InputBox("Type characters you want to add:", "Kutools for Excel", "", , , , , 2)
If StrPtr(xInStr) = 0 Then Exit Sub
Application.ScreenUpdating = False
For Each xCell In xRg
xArr = Split(xCell.Text, " ")
xValue = ""
For Each xStr In xArr
If Trim(xStr) <> "" Then
If xValue = "" Then
xValue = Trim(xStr) & xInStr
Else
xValue = xValue & " " & Trim(xStr) & xInStr
End If
End If
Next
xCell.Value = xValue
Next
Application.ScreenUpdating = True
End Sub4. lis F5 pro spuštění kódu (nebo můžete kliknout Běh tlačítko ), objeví se dialogové okno s výzvou, abyste vybrali souvislé buňky, které mají fungovat, klepněte OK.
5. Ve druhém vyskakovacím dialogovém okně zadejte znaky, které chcete přidat, a klikněte OK.
Poté budou znaky přidány na začátek nebo konec každého slova.
Někdy můžete chtít přidat text mezi všechny znaky. Zde v této části jsou uvedeny dva scénáře, jedním je přidání textu mezi každý znak a dalším přidáním mezery mezi každé číslo.
7.1 Přidejte text mezi každý znak/číslici pomocí funkce definované uživatelem
Chcete-li přidat znaky mezi každý znak v řetězci, může vám pomoci funkce User-Defined.
V tomto případě přidáme mezi každý znak mezeru.
1. lis Další + F11 klávesy pro povolení Microsoft Visual Basic pro aplikace okno.
2. V rozbalovacím okně klikněte na Vložit > Modul pro vložení nového prázdného modulu.
3. Zkopírujte a vložte níže uvedený kód VBA do modulu.
Přidejte text mezi znaky
Function AddText(Str As String) As String
Dim i As Long
For i = 1 To Len(Str)
AddText = AddText & Mid(Str, i, 1) & " "
Next i
AddSpace = Trim(AddSpace)
End Function4. Uložte kód a zavřete okno, abyste se vrátili zpět do listu, poté zadejte níže uvedený vzorec do buňky, do které chcete umístit výsledek přidávání.
=AddText(B3)
B3 je buňka obsahující řetězec, který chcete přidat mezeru mezi znaky.
5. lis vstoupit Chcete-li získat výsledek, přetáhněte rukojeť automatického vyplňování přes buňky, abyste získali další výsledky, jak potřebujete.
|
Pozor: 1) V kódu VBA můžete změnit "" na jiné texty, jak potřebujete, předpokládejme, že to změňte na "-" přidat - mezi znaky. 2） Kód přidá nový text za každý znak. Pokud to nepotřebujete, můžete poslední přidaný text odstranit pomocí níže uvedeného vzorce. Další podrobnosti o vzorci naleznete na adrese: Jak odstranit prvních nebo posledních N znaků z buňky nebo řetězce v Excel? LEFT(buňka,DÉLKA(buňka)-délka_textu) |
7.2 Přidejte mezeru mezi každé číslo se vzorcem
Pokud chcete přidat mezeru mezi číslice řetězce, což je číselná hodnota, můžete použít funkci TEXT.
| Pozor: čísla rozsahů, mezi které chcete přidat mezeru, musí mít stejnou délku, jinak mohou být některé výsledky nesprávné. |
Předpokládejme, že zde je seznam 8místných čísel v rozsahu B10:B12, vyberte buňku, do které bude umístěn výsledek, zadejte níže vzorec:
=TEXT(B10,"# # # # # # # #")
Stisknout vstoupit a poté přetáhněte úchyt automatického vyplňování přes další buňky.
Pokud je délka čísel 5, vzorec se změní jako =TEXT(B10,"# # # # #").
Chcete-li přidat znak (znaky) mezi každé slovo, za předpokladu, že přidáte mezeru mezi první, prostřední a příjmení, jak je znázorněno na obrázku níže, můžete použít funkci definovanou uživatelem.
1. lis Další + F11 klávesy pro povolení Microsoft Visual Basic pro aplikace okno.
2. V rozbalovacím okně klikněte na Vložit > Modul pro vložení nového prázdného modulu.
3. Zkopírujte a vložte níže uvedený kód VBA do modulu.
Přidejte znaky mezi slova
Function AddCharacters(pValue As String) As String
'UpdatebyExtendoffice20160908
Dim xOut As String
xOut = VBA.Left(pValue, 1)
For i = 2 To VBA.Len(pValue)
xAsc = VBA.Asc(VBA.Mid(pValue, i, 1))
If xAsc >= 65 And xAsc <= 90 Then
xOut = xOut & " " & VBA.Mid(pValue, i, 1)
Else
xOut = xOut & VBA.Mid(pValue, i, 1)
End If
Next
AddCharacters = xOut
End Function4. Uložte kód a zavřete okno, abyste se vrátili zpět do listu, poté zadejte níže uvedený vzorec do buňky, do které chcete umístit výsledek přidávání.
=AddCharacters(B3)
B3 je buňka obsahující řetězec, který chcete přidat mezeru mezi slova.
5. lis vstoupit Chcete-li získat výsledek, přetáhněte rukojeť automatického vyplňování přes buňky, abyste získali další výsledky, jak potřebujete.
| Pozor: v kódu VBA můžete změnit "" na jiné texty, jak potřebujete, předpokládejme, že to změňte na "-" přidat - mezi slov. |
Chcete-li přidat konkrétní znak (znaky) mezi texty a čísla, jak je znázorněno na níže uvedeném snímku obrazovky, nabízí dva způsoby.
9.1 Přidejte znak mezi texty a čísla pomocí vzorce
Pokud jsou texty před čísly, obecný vzorec je
TRIM(REPLACE(řetězec,MIN(NAJÍT({1,2,3,4,5,6,7,8,9,0},řetězec&"1234567890")),0,"-"))
Použijte výše uvedený případ jako příklad, použijte prosím vzorec takto:
=TRIM(REPLACE(B4,MIN(FIND({1,2,3,4,5,6,7,8,9,0},B4&"1234567890")),0,"-"))
Stisknout vstoupit , abyste získali první výsledek, pak přetáhněte úchyt automatického vyplňování přes další buňky, abyste vyplnili tento vzorec.
Pokud jsou čísla na začátku textu, obecný vzorec je
VLEVO(řetězec,SOUČET(DÉLKA(řetězec)-DÉLKA(NAHRADIT(řetězec,{0,1,2,3,4,5,6,7,8,9},""))))&"."&RIGHT (řetězec,DÉLKA(B13)- SOUČET(DÉLKA(řetězec)-DÉLKA(NAHRADIT(řetězec, {0,1,2,3,4,5,6,7,8,9}, ""))))
Použijte výše uvedený případ jako příklad, použijte prosím vzorec takto:
=LEFT(B13,SUM(LEN(B13)-LEN(SUBSTITUTE(B13,{0,1,2,3,4,5,6,7,8,9},""))))&"."&RIGHT(B13,LEN(B13)- SUM(LEN(B13)-LEN(SUBSTITUTE(B13, {0,1,2,3,4,5,6,7,8,9}, ""))))
Stisknout vstoupit , abyste získali první výsledek, pak přetáhněte úchyt automatického vyplňování přes další buňky, abyste vyplnili tento vzorec.
9.2 Přidejte znak mezi texty a čísla pomocí Flash Fill
Pokud používáte Excel 2013 nebo novější verze, Flash Fill také vám může pomoci přidat znak(y) mezi texty a čísla.
Vedle původních dat zadejte nová data, do kterých byly přidány znaky mezi texty a čísla. Poté stisknutím klávesy Enter přejděte na další buňku prvního výsledku.
A stiskněte Ctrl + E pro aktivaci Flash Fill.
Prodloužení
Tento tutoriál také uvádí některé scénáře přidávání textu, se kterými se můžeme setkat v každodenním životě nebo práci.
Někdy je potřeba přidat oddělovače, jako jsou pomlčky, k sérii telefonních čísel nebo sociálních čísel, jak je uvedeno níže. Pokud je potřeba přidat stovky čísel, vyzkouší se ruční zadání. V této části představuje tři trikové způsoby, jak tuto práci rychle zvládnout.
10.1 Přidání pomlček do čísel pomocí vzorce
Zde jsou dva vzorce, které mohou rychle přidat pomlčky k číslům. Chcete-li přidat pomlčky za třetí a šesté číslo v buňce B3, použijte následující vzorec:
Funkce REPLACE
=REPLACE(REPLACE(B3,4,0;8,0,"-"),XNUMX,"-")
Funkce TEXT
=TEXT(B3,"???-???-??? ")
Stisknout vstoupit , abyste získali první výsledek, pak přetáhněte úchyt automatického vyplňování přes další buňky.
|
Pozor: Argumenty výše uvedených vzorců můžete podle potřeby změnit. Předpokládejme přidat "-" po druhé, čtvrté a šesté číslici můžete vzorce použít jako: =REPLACE(REPLACE(REPLACE(B3,3,0,"-"),6,0,"-"),9,0,"-") =TEXT(B3,"??-??-??-??? ") |
10.2 Přidání pomlček do čísel podle Formát buněk
Chcete-li přímo přidat pomlčky k číslům v původních buňkách, můžete použít Formát buněk vlastnost.
1. Vyberte čísla, ke kterým chcete přidat pomlčky, a stiskněte Ctrl + 1 klávesy pro povolení Formát buněk dialog.
2. V Formát buněk dialogové okno pod Číslo kartu, vyberte Zvyk z Kategorie sekce.
3. Potom vpravo Styl sekce, typ ###-###-### do textového pole klikněte OK.
Nyní byly k vybraným číslům přidány pomlčky.
10.3 Přidejte pomlčky k telefonnímu číslu pomocí Přidat text Kutools pro Excel
Pokud máte Kutools pro Excel nainstalovaný v Excelu, doplnit text funkce vám může udělat laskavost.
1. Vyberte buňky, které obsahují čísla potřebná k přidání pomlček, klepněte Kutools > Text > Přidej text.
2. V doplnit text dialogovém okně zadejte pomlčku nebo jiný oddělovač Text textové pole, vyberte Specifikovat poté zadejte pozice, za které chcete přidat pomlčky, a oddělte je čárkami do textového pole níže.
3. cvaknutí Ok nebo Použít. Poté byly k vybraným číslům přidány pomlčky.
Zdarma ke stažení Přidat text na 30denní zkušební verzi.
Předpokládejme, že existuje seznam čísel v různých délkách, chcete přidat úvodní nuly, aby měly stejnou délku a vypadaly úhledně jako na obrázku níže. Zde jsou dva způsoby, jak tuto úlohu zvládnout.
11.1 Přidejte úvodní nuly do buněk pomocí funkce Formát buněk
Můžete použít Formát buněk funkce pro nastavení vlastního formátu pro opravu délky buňky přidáním úvodních nul.
1. Vyberte buňky, ke kterým chcete přidat úvodní nuly, a stiskněte Ctrl + 1 klíče k povolení Formát buněk dialog.
2. V Formát buněk dialogové okno pod Číslo kartu, vyberte Zvyk z Kategorie sekce.
3. Potom vpravo Styl sekce, typ 0000 do textového pole (chcete-li opravit délku čísla na 4-dight, napište 0000, můžete ji změnit na jinou délku, jako 5-dight, napište 00000), klikněte OK.
Potom se k číslům přidají úvodní nuly a ve stejné délce.
| Pozor: pokud délka původních dat překročí nastavenou délku, zobrazí původní data bez úvodních nul. |
11.2 Přidejte úvodní nuly do buněk pomocí vzorce
Pokud nechcete měnit původní data, můžete použít vzorec k přidání úvodních nul k číslům na jiných místech.
Zde jsou tři funkce, které vám mohou pomoci.
Formule1 funkce TEXT
TEXT(číslo "00…")
Formule2 SPRÁVNÁ funkce
RIGHT("00…"&číslo,délka)
Funkce Formula3 BASE
ZÁKLAD(číslo;10;délka)
Počet nul ve vzorci se rovná délce čísla.
Zde nastavíte 4-místné jako délku čísel, použijte prosím vzorce, jak je uvedeno níže:
= TEXT (B10, "0000")
=RIGHT("0000"&B10,4;XNUMX)
=BASE(B10,10,4;XNUMX;XNUMX)
Stisknout vstoupit a přetáhněte rukojeť automatického plnění dolů.
| Pozor: pokud délka původních dat překročí nastavenou délku, zobrazí původní data bez úvodních nul. |
Pokud chcete k číslům přidat koncové nuly za účelem vytvoření pevné délky, za předpokladu, že přidáte koncové nuly k číslům v buňce B3:B7, abyste je vytvořili v délce 5 číslic, jak je znázorněno na obrázku níže, můžete použít vzorec pro řešení úlohy .
Funkce REPT
číslo&REPT("0",délka-DÉLKA(číslo))
Ve vzorci argument "číslo" představuje číslo nebo odkaz na buňku, ke kterému chcete přidat koncové nuly, a argument "délka" je požadovaná pevná délka.
Použijte vzorec takto:
=B3&REPT("0";5-DÉLKA(B3))
Stisknout vstoupit a přetáhněte úchyt automatického vyplňování dolů k buňkám, které tento vzorec potřebují.
Pokud chcete exportovat nebo zkopírovat data z listu do poznámkového bloku, mohou být texty umístěny neuspořádaně, protože hodnoty buněk mají různé délky, aby vypadaly úhledně, jak je znázorněno na obrázku níže, musíte přidat mezeru na konci.
Pro vyřešení této úlohy vám může pomoci funkce REPT.
LEFT(text & REPT(" ",max_length);max_length)
Ve vzorci je argument "text" text nebo odkaz na buňku, ke kterému chcete přidat koncovou mezeru, a argument "max_length" je pevná délka, kterou chcete vytvořit texty, která musí být delší nebo rovna maximální délka použitých textů.
Chcete-li přidat koncovou mezeru k textům v buňce B3:C6, použijte níže uvedený vzorec:
=LEFT(B3 & REPT(" ";10);10)
Stisknout vstoupit a poté přetáhněte úchyt automatického vyplňování na všechny buňky, které tento vzorec potřebují.
V tomto případě, protože nejdelší použitý text má 9 znaků, použijeme zde 10.
Někdy je třeba přidat uvozovky nebo závorky kolem textu nebo čísel v buňkách, jak je znázorněno na níže uvedeném snímku obrazovky, kromě ručního psaní po jednom, zde jsou dvě metody v Excelu, které to mohou rychle vyřešit.
14.1 Přidejte uvozovky/závorky podle formátu buněk
Pokud chcete přidat uvozovky kolem textu v původních datech, může vám pomoci funkce Formát buněk v Excelu.
1. Vyberte buňky, do kterých chcete přidat uvozovky, stiskněte Ctrl + 1 klávesy pro povolení Formát buněk dialog.
2. V Formát buněk dialogové okno pod Číslo kartu, vyberte Zvyk ze seznamu Kategoriea poté přejděte do pravé části a zadejte "" "@" "" do Styl Textové pole. Klepněte na OK.
Text ve vybraných buňkách byl přidán do uvozovek.
Pokud chcete kolem textu přidat závorky, použijte v textovém poli Typ v dialogovém okně Formát buněk (@).
14.2 Přidejte uvozovky/závorky do vzorců
Vzorce lze také použít k přidání uvozovek k textu v jiných buňkách.
Použití & konektor
""""&text&""""
Or
znak(34)&text&znak(34)
V tomto případě text v buňce B11, použijte prosím následující vzorce:
=""""&B11&""""
Or
=char(34)&B11&char(34)
Stisknout vstoupit a přetažením rukojeti automatického vyplňování dolů vyplňte buňky.
Pokud chcete přidat závorky kolem textu, použijte vzorec jako "("&text&")".
14.3 Přidání uvozovek/závorek pomocí jazyka VBA
Pokud k vyřešení této úlohy potřebujete kód VBA, zde je VBA.
1. lis Další + F11 klávesy pro povolení Microsoft Visual Basic pro aplikace okno.
2. Poté v překryvném okně klikněte Vložit > Modul pro vložení nového prázdného modulu.
3. Zkopírujte a vložte níže uvedený kód VBA do nového modulu.
VBA: Přidejte uvozovky kolem textů
Sub addquotationmarksorbrackets()
'UpdatebyExtendOffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
Rng.Value = """" & Rng.Value & """"
Next
End Sub| Pozor: v kódovém skriptu, """" & Rng.Value & """" označuje přidat "" kolem textů, pokud chcete přidat závorky () kolem textu, změňte to na "(" & Rng.Value & ")". |
4. lis F5 klíč nebo klik Běh tlačítko v okně aktivujte kód VBA. Zobrazí se dialogové okno pro výběr buněk pro přidání uvozovek.
5. cvaknutí OK. Nyní byly u textů ve vybraných buňkách přidány uvozovky.
Někdy, abyste pomohli ostatním uživatelům lépe porozumět datům, možná budete muset do výsledků vzorce přidat text, jak je znázorněno na obrázku níže. Pro zahrnutí textu do vzorce můžete použít uvozovky obklopující text a přidat spojnici &, abyste spojili text a vzorec dohromady.
Případ 1 přidejte text před vzorec
"Dnes je "&TEXT(TODAY(),"dddd, mmmm dd.")
Případ 2 přidejte text za vzorec
TEXT(NOW(),"HH:MM:SS")&" je aktuální čas."
Případ 3 přidejte texty na dvě strany vzorce
"Dnes prodáno "&data&" kg."
Další výukové programy Excel: |
|
Spojte více sešitů/listů do jednoho Rozdělit text, číslo a buňky data (oddělit do více sloupců) Kombinujte obsah více buněk bez ztráty dat v Excelu Porovnejte dva sloupce pro shody a rozdíly v aplikaci Excel |
Nejlepší kancelářské nástroje produktivity
Kutools pro Excel řeší většinu vašich problémů a zvyšuje vaši produktivitu o 80%
- Super Formula Bar (snadno upravit více řádků textu a vzorce); Rozložení pro čtení (snadno číst a upravovat velké množství buněk); Vložit do filtrovaného rozsahu...
- Sloučit buňky / řádky / sloupce a uchovávání údajů; Rozdělit obsah buněk; Zkombinujte duplicitní řádky a součet / průměr... Zabraňte duplicitním buňkám; Porovnat rozsahy...
- Vyberte možnost Duplikovat nebo Jedinečný Řádky; Vyberte prázdné řádky (všechny buňky jsou prázdné); Super hledání a fuzzy hledání v mnoha sešitech; Náhodný výběr ...
- Přesná kopie Více buněk beze změny odkazu na vzorec; Automaticky vytvářet reference do více listů; Vložte odrážky, Zaškrtávací políčka a další ...
- Oblíbené a rychlé vkládání vzorců„Rozsahy, grafy a obrázky; Šifrovat buňky s heslem; Vytvořte seznam adresátů a posílat e-maily ...
- Extrahujte text, Přidat text, Odebrat podle pozice, Odebrat mezeru; Vytváření a tisk mezisoučtů stránkování; Převod mezi obsahem buněk a komentáři...
- Super filtr (uložit a použít schémata filtrů na jiné listy); Rozšířené řazení podle měsíce / týdne / dne, frekvence a dalších; Speciální filtr tučnou kurzívou ...
- Kombinujte sešity a pracovní listy; Sloučit tabulky na základě klíčových sloupců; Rozdělte data do více listů; Dávkový převod xls, xlsx a PDF...
- Seskupování kontingenčních tabulek podle číslo týdne, den v týdnu a další ... Zobrazit odemčené, zamčené buňky různými barvami; Zvýrazněte buňky, které mají vzorec / název...
- Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
- Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
- Zvyšuje vaši produktivitu o 50%a snižuje stovky kliknutí myší každý den!
