Excel doda besedilo in številko na določeno mesto celice
V Excelu je dodajanje besedil ali številk celicam zelo pogosto delo. Na primer dodajanje presledka med imeni, dodajanje predpone ali pripone celicam, dodajanje pomišljajev družbenim številkam. Tukaj v tej vadnici so navedeni skoraj vsi scenariji dodajanja v Excelu in za vas na voljo ustrezne metode.
Opombe
V tej vadnici sem ustvaril nekaj primerov za razlago metod, lahko spremenite reference, ki jih potrebujete, ko uporabljate spodnjo kodo VBA ali formule, ali pa lahko prenesete vzorce za neposredno preizkušanje metod.
Kliknite za prenos vzorčne datoteke
Ta del navaja različne metode za dodajanje znakov na začetek vsake celice, kot je prikazano na spodnjem posnetku zaslona:
1.1 Dodajte na začetek celic s formulo
Tukaj lahko izberete eno od spodnjih treh formul:
Operater združevanja formule 1 "&"
Povežite več besedil skupaj z znakom ampersanda "&".
"besedilo" & celico
Funkcija CONCATENATE Formula2
Funkcija CONCATENATE se uporablja za združevanje besedil.
CONCATENATE("besedilo", celica)
Funkcija Formula3 CONCAT
To je nova funkcija, ki se pojavlja samo v Excelu 2019, Office 365 in Excelu na spletu.
CONCAT("besedilo", celica)
V splošnih formulah: celica je sklic na celico na besedilo, ki mu želite dodati predpono, in besedilo je besedilo, ki je bilo uporabljeno za dodajanje celici.
Z uporabo zgornjega primera lahko uporabite formule v vrstici s formulo, kot so:
"&"
=$E$3&B3 or "Iphone" in B3
Funkcija CONCATENATE
=VEŽI ($E$3,B3) or =CONCATENATE("Iphone",B3)
Funkcija CONCAT
=CONCAT($E$3,B3) or =CONCAT("Iphone",B3)
Pritisnite Vnesite tipko, da dobite rezultat, nato povlecite ročico samodejnega izpolnjevanja navzdol, da dodate besedilo v vsako celico obsega B3:B6.
| pozornost: Besedilo mora biti zaprto v dvojnih narekovajih ali formula vrne vrednost napake #NAME?. Če uporabljate sklicevanje na celico za besedilo, ne pozabite uporabiti absolutne reference, lahko pritisnete tipko F4, da spremenite relativno sklic na absolutno referenco. |
1.2 Dodajte na začetek celic s Flash Fill
Če uporabljate Excel 2013 ali novejše različice, je zmogljiva nova funkcija, Flash Fill, lahko zapolni celice na podlagi zgornjih celic, ki ste jih vnesli samodejno.
Izberite celico poleg prvih izvirnih podatkov, ročno vnesite prve podatke z besedilom predpone, kot je prikazano spodaj:
Nato v spodnji celici nadaljujte z vnašanjem drugih podatkov s predpono, med tipkanjem se bo prikazal seznam v sivi barvi, pritisnite Vnesite tipko, da omogočite, da Flash Fill samodejno zapolni podatke.
Če se seznam za predogled ne ustvari, pojdite na zavihek Domov in kliknite Izpolnite > Flash Fill da ga zaženete ročno.
1.3 Dodajte na začetek celic z uporabo kode VBA
Če poznate kodo VBA, tukaj predstavlja kodo VBA za dodajanje besedila na začetek vsake celice v obsegu.
1. Izberite obseg celic, ki jim želite dodati besedilo predpone, nato pritisnite druga + F11 tipke, da omogočite Microsoft Visual Basic za aplikacije okno.
2. Nato v pojavnem oknu kliknite Vstavi > Moduli da vstavite prazen nov modul.
3. Kopirajte in prilepite pod kodo VBA v nov modul.
VBA: Dodaj na začetek celic
Sub AppendToExistingOnLeft()
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = "Iphone" & c.Value
Next
End Sub| pozornost: v skriptu kode, iphone je besedilo, ki ga želite dodati na začetek izbranih celic, spremenite ga po svojih potrebah. |
4. Pritisnite F5 tipko ali kliknite Run Gumb 
v oknu, da aktivirate kodo VBA.
Zdaj je vsem izbranim celicam na začetku dodano besedilo "Iphone".
Razen dodajanja besedila na začetek celic je običajno tudi dodajanje besedila na konec celic. Tukaj vzamemo dodajanje enote na koncu vsake celice za primere, kot je prikazano spodaj:
2.1 Dodajte na konec celic s formulo
Tukaj lahko izberete eno od spodnjih treh formul:
Operater združevanja formule 1 "&"
Povežite več besedil skupaj z znakom ampersanda "&".
Celica&"besedilo"
Funkcija CONCATENATE Formula2
Funkcija CONCATENATE se uporablja za združevanje besedil.
CONCATENATE(celica,"besedilo")
Funkcija Formula3 CONCAT
To je nova funkcija, ki se pojavlja samo v Excelu 2019, Office 365 in Excelu na spletu.
CONCAT(celica,"besedilo")
V splošnih formulah: celica je sklic na celico na besedilo, ki mu želite dodati pripono, in besedilo je besedilo, ki se uporablja za dodajanje celici.
Z uporabo zgornjega primera lahko uporabite formule v vrstici s formulo, kot so:
"&"
=B3&$E$3 or =B3&"Kg"
Funkcija CONCATENATE
=VEŽI (B3, $E$3) or =VEŽI (B3, "Kg")
Funkcija CONCAT
=CONCAT(B3, $E$3) or =CONCAT(B3, "Kg")
Pritisnite Vnesite tipko, da dobite rezultat, nato povlecite ročico samodejnega izpolnjevanja navzdol, da dodate besedilo na konec vsake celice obsega B3:B6.
| pozornost: Besedilo mora biti zaprto v dvojnih narekovajih ali formula vrne vrednost napake #NAME?. Če uporabljate sklicevanje na celico za besedilo, ne pozabite uporabiti absolutne reference, lahko pritisnete tipko F4, da spremenite relativno sklic na absolutno referenco. |
2.2 Dodajte na konec celic s Flash Fill
Če uporabljate Excel 2013 ali novejše različice, je zmogljiva nova funkcija, Flash Fill, lahko zapolni celice na podlagi zgornjih celic, ki ste jih vnesli samodejno.
Izberite celico poleg prvih izvirnih podatkov, ročno vnesite prve podatke s pripono, kot je prikazano spodaj:
Nato aktivirajte celico pod prvim rezultatom, kliknite datum > Flash Fill, bodo vse spodnje celice, vključno z aktivno celico, samodejno zapolnjene.
2.3 Dodajte na konec celic z uporabo kode VBA
Tukaj je koda VBA, ki lahko doda besedilo na konec vsake celice v obsegu.
1. Izberite obseg celic, ki jim želite dodati pripono, nato pritisnite druga + F11 tipke, da omogočite Microsoft Visual Basic za aplikacije okno.
2. Nato v pojavnem oknu kliknite Vstavi > Moduli da vstavite prazen nov modul.
3. Kopirajte in prilepite pod kodo VBA v nov modul.
VBA: Dodaj na konec celic
Sub AppendToExistingOnRight()
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = c.Value & "Kg"
Next
End Sub| pozornost: v skriptu kode, Kg je besedilo, ki ga želite dodati na konec izbranih celic, spremenite ga po svojih potrebah. |
4. Pritisnite F5 tipko ali kliknite Run Gumb v oknu, da aktivirate kodo VBA.
Zdaj je bilo na koncu vsake celice dodano besedilo "Kg".
Za večino od vas je dodajanje besedil na začetek ali konec celic enostavno, vendar je dodajanje besedil na sredino celic morda nekoliko težko. Vzemite primer, kot je prikazano na spodnjem posnetku zaslona, in dodajte ločilo, kot je dvopičje: na koncu drugega znaka vsake celice v obsegu B3:B7.
3.1 Dodajte po n-em znaku niza s formulo
Tukaj so štiri formule, ki so vam na voljo za to nalogo, izberite eno za uporabo:
Formula1 Združite funkcije LEVO in DESNO
LEVO(celica, n) & "besedilo" & DESNO(celica, LEN(celica) -n)
Funkcije Formula2 COMBINE CONCATENATE(CONCAT), DESNO in LEN
VEŽI (LEVO(celica, n), "besedilo", DESNO(celica, LEN(celica) -n))
Or
CONCAT(LEVO(celica, n), "besedilo", DESNO(celica, LEN(celica) -n))
Funkcija Formula3 REPLACE
REPLACE(celica, n+1, 0, "besedilo")
V splošnih formulah: celica je sklic na celico na besedilo, ki mu želite dodati besedilo na sredini, in besedilo je besedilo, ki se uporablja za dodajanje celici, n je številka, ki določa, za katerim znakom v besedilnem nizu želite dodaj besedilo.
Za reševanje zgoraj navedenega dela uporabite naslednje formule:
Združite funkcije LEVO in DESNO
=LEVO(B3, 2) & ":" & DESNO(B3, LEN(B3) -2) or =LEVO(B3, 2) & $E$3 & DESNO (B3, LEN(B3) -2)
ZDRUŽUJ funkcije CONCATENATE(CONCAT), DESNO in LEN
=VEŽI(LEVO(B3, 2), ":", DESNO(B3, LEN(B3) -2)) or =CONCAT (LEVO(B3, 2), ":", DESNO(B3, LEN(B3) -2))
Funkcija REPLACE
=ZAMENJAJ(B3, 2+1, 0, ":") or =ZAMENJAJ(B3, 2+1, 0, $E$3)
Pritisnite Vnesite tipko, da dobite rezultat, nato povlecite ročico samodejnega izpolnjevanja navzdol, da dodate besedilo po drugem znaku vsake celice obsega B3:B7.
| pozornost: Besedilo mora biti zaprto v dvojnih narekovajih ali formula vrne vrednost napake #NAME?. Če uporabljate sklicevanje na celico za besedilo, ne pozabite uporabiti absolutne reference, lahko pritisnete tipko F4, da spremenite relativno sklic na absolutno referenco. |
3.2 Dodajte po n-em znaku niza s funkcijo Flash Fill
Če uporabljate Excel 2013 ali novejše različice, Flash Fill, lahko zapolni celice na podlagi zgornjih celic, ki ste jih vnesli samodejno.
Izberite celico poleg prvih izvirnih podatkov, ročno vnesite prve podatke z dvopičjem za drugim znakom, kot je prikazano spodaj:
Nato aktivirajte celico pod prvim rezultatom, pritisnite Ctrl + E tipke za aktiviranje Flash Fill vse spodnje celice, vključno z aktivno celico, so bile samodejno izpolnjene.
| pozornost: Če ste v sistemu MAC, pritisnite tipke Command + E. |
3.3 Dodajte po n-em znaku niza z uporabo kode VBA
To delo lahko opravi tudi koda VBA.
1. Pritisnite druga + F11 tipke, da omogočite Microsoft Visual Basic za aplikacije okno.
2. Nato v pojavnem oknu kliknite Vstavi > Moduli da vstavite prazen nov modul.
3. Kopirajte in prilepite pod kodo VBA v nov modul.
VBA: Dodaj na sredino celic
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| pozornost: v skriptu kode, 2 je številka določa položaj, za katerim želite dodati besedilo, : je besedilo, ki mu ga želite dodati, 3 je vsota 2 in dolžine besedila, ki ga dodate. Vzemite drug primer, da dodate "in" za prvi znak niza "AB", mora biti skript Rng.Value = VBA.Left(Rng.Value, 1) & "in" & VBA.Mid(Rng.Value, 4, VBA.Len(Rng.Value) - 1) |
4. Pritisnite F5 tipko ali kliknite Run Gumb v oknu, da aktivirate kodo VBA. Pojavi se pogovorno okno za izbiro celic za dodajanje besedila.
5. klik OK. Zdaj je bilo za drugim znakom vsake celice v obsegu B19:B23 dodano besedilo ":".
Če ste naveličani formul in VBA, lahko preizkusite zmogljivo in priročno orodje, Kutools za Excel, ki vam lahko pomaga pri svojem dodajanje besedila lastnost.
Ta funkcija dodajanja besedila lahko:
- Dodajte besedilo pred prvim znakom
- Dodajte besedilo za zadnjim znakom
- Dodajte besedilo na določena mesta
- Podaljšanje
Dodajte besedilo pred velikimi črkami
Dodajte besedilo pred male črke
Dodajte besedilo pred velike/male črke
Dodajte besedilo pred številske znake
Kliknite za 30-dnevno brezplačno preskusno obdobje.
Izberite celice, v katere želite dodati besedilo, nato s klikom uporabite možnost Dodaj besedilo Kutools > Besedilo > dodajanje besedila.
4.1 Dodajte besedilo pred prvim znakom ali za zadnjim znakom
v dodajanje besedila vnesite besedilo, ki ga želite dodati celicam v Besedilo besedilno polje, nato preverite Pred prvim znakom v položaj oddelek.
klik Uporabi or Ok za dokončanje dodajanja.
Če želite dodati besedilo za zadnjim znakom, vnesite besedilo, ki ga želite dodati celicam v Besedilo besedilno polje, nato preverite Po zadnjem znaku v položaj oddelek.
klik Uporabi or Ok za dokončanje dodajanja.
4.2 Dodajte besedilo na določeno(-e) mesto(-e)
Včasih boste morda želeli dodati besedilo na sredino celice ali dodati besedilo na več položajev celice. Predpostavimo, da dodamo ločilo "-" po tretji in sedmi števki družbenih številk, kot je prikazano na spodnjem posnetku zaslona:
v dodajanje besedila pogovorno okno, vnesite besedilo Besedilo besedilno polje in preverite Določite možnost, nato vnesite položaje z vejico, da jih ločite v polje z besedilom, in kliknite Ok or Uporabi.
4.3 Razširitev pripomočka Dodaj besedilo
Poleg dodajanja besedila na običajne položaje (začetek, konec in določen položaj), dodajanje besedila pripomoček podpira tudi dodajanje besedila
- Pred velikimi črkami
- Pred malimi črkami
- Pred velikimi/malimi črkami
- Pred številskimi znaki
v dodajanje besedila pogovorno okno, vnesite besedilo Besedilo besedilno polje in preverite Samo doda možnost, nato kliknite spodnji spustni meni, da izberete operacijo, ki jo potrebujete.
Ta del vsebuje formule za dodajanje besedila pred ali po prvem prikazanem določenem besedilu v Excelu.
5.1 Dodajte besedilo pred prvim določenim besedilom
Na primer, želite dodati besedilo "(prodaja)" pred prvim besedilom "cena" v celici, kot so prikazani spodnji posnetki zaslona:
V Excelu lahko to nalogo rešita funkciji REPLACE in SUBSTITUTE.
Funkcija REPLACE
REPLACE(celica, FIND("najdi_besedilo",celica),0,"dodaj_besedilo")
Funkcija SUBSTITUTE
SUBSTITUTE(celica,"najdi_besedilo","replace_text",1)
V tem primeru uporabite zgornje formule, kot so:
=ZAMENJAJ(B4,NAJDI("cena",B4),0,"(razprodaja)")
Or
=NADOMESTEK(B4,"cena","(prodajna)cena",1)
Argumenti formule v tem primeru so
celica: B4,
Find_text: cena,
Dodaj_besedilo: (razprodaja),
Replace_text:(prodajna)cena.
Pritisnite Vnesite tipko, da dobite prvi rezultat dodajanja, nato povlecite ročico samodejnega izpolnjevanja čez celice, ki potrebujejo to formulo.
5.2 Dodajte besedilo za prvim določenim besedilom
Na primer, želite dodati simbol valute "$" za prvim dvopičjem ":" v celici, kot so prikazani spodnji posnetki zaslona:
V Excelu lahko to nalogo rešita funkciji REPLACE in SUBSTITUTE.
Funkcija REPLACE
REPLACE(cell, FIND("najdi_besedilo",cell),find_text_length,"replace_text")
Funkcija SUBSTITUTE
SUBSTITUTE(celica,"najdi_besedilo","replace_text")
V tem primeru uporabite zgornje formule, kot so:
=ZAMENJAJ(B12,NAJDI(":",B12),1,":$")
Or
=NADOMEST (B12,":",":$")
Argumenti formule v tem primeru so
celica: B12,
Find_text: :,
Zamenjaj_besedilo::$.
Najdi dolžino_besedila:1
Pritisnite Vnesite tipko, da dobite prvi rezultat dodajanja, nato povlecite ročico samodejnega izpolnjevanja čez celice, ki potrebujejo to formulo.
V tem delu vadnice uvaja tri različne metode (formula, Najdi in zamenjaj, VBA), ki vam pomagajo dodajati znake pred ali za vsako besedo celice.
| Pred vsako besedo dodajte znake |
 |
| Za vsako besedo dodajte znake |
 |
6.1 Dodajte znak(e) pred ali za vsako besedo s formulo
Funkcijo SUBSTITUTE lahko uporabite za reševanje tega opravila, splošne formule so naslednje:
Dodajte pred vsako besedo
"znak"&SUBSTITUTE(niz, " "," znak")
Dodajte za vsako besedo
SUBSTITUTE(niz," ","znak")&"znak"
Če pred vsako besedo na seznamu B3:B6 dodate "+", uporabite formulo na naslednji način:
="+"&NAMESNIK(B3, " "," +")
Pritisnite Vnesite tipko, da dobite prvi rezultat, nato povlecite ročico za samodejno izpolnjevanje navzdol, da dobite vse rezultate.
Če za vsako besedo seznama B11:B14 dodate ($), uporabite formulo, kot je prikazano spodaj:
=NADOMEST (B11," ","($) ")&"($)"
Pritisnite Vnesite tipko, da dobite prvi rezultat, nato povlecite ročico za samodejno izpolnjevanje navzdol, da dobite vse rezultate.
6.2 Dodajte znak(e) pred ali za vsako besedo z možnostjo Najdi in zamenjaj
Če želite dodati znake pred vsako besedo, razen prve, ali dodati znake za vsako besedo, razen zadnje, kot je prikazano na spodnjem posnetku zaslona, je vgrajen Excel Poišči in zamenjaj pripomoček lahko opravi to nalogo.
Dodajte pred vsako besedo, razen prve
Dodajte za vsako besedo, razen zadnje
1. Izberite celice, ki jim želite dodati znake pred ali za vsako besedo, nato pritisnite Ctrl + H tipke, da omogočite Poišči in zamenjaj dialog.
2. Vnesite presledek Našli kaj besedilno polje, nato vnesite nove znake s presledkom Zamenjaj z besedilno polje in na koncu kliknite Zamenjaj vse gumb.
| operacija | splošno | Primer |
| Pred vsako besedo dodajte znake | Dodajte nove znake za presledkom | Dodaj (prodaja) pred vsako besedo |
| operacija | splošno | Primer |
| Za vsako besedo dodajte znake | Novi znaki sledijo s presledkom | Dodaj - po vsaki besedi |
Tukaj sta na voljo dve kodi VBA za dodajanje znakov pred ali za vsako besedo, zaženite kodo tako, da sledite spodnjim korakom.
1. Pritisnite druga + F11 tipke, da omogočite Microsoft Visual Basic za aplikacije okno.
2. V pojavnem oknu kliknite Vstavi > Moduli da vstavite nov prazen modul.
3. Kopirajte in prilepite pod kodo VBA v modul.
Dodaj pred vsako besedo
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 SubDodajte po vsaki besedi
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. Pritisnite F5 tipko za zagon kode (lahko pa kliknete Run Gumb ), se prikaže pogovorno okno, ki vas prosi, da izberete neprekinjene celice za delovanje, kliknite OK.
5. V drugem pojavnem pogovornem oknu vnesite znake, ki jih želite dodati, kliknite OK.
Nato bodo znaki dodani na sprednji ali konec vsake besede.
Včasih boste morda želeli dodati besedilo med vsake znake. Tukaj v tem delu navaja dva scenarija, eden je dodati besedilo med vsak znak, drugi pa je dodati presledek med vsako številko.
7.1 Dodajte besedilo med vsak znak/številko z uporabniško definirano funkcijo
Če želite med vsakim znakom v nizu dodati znake, vam lahko pomaga uporabniško definirana funkcija.
V tem primeru med vsakim znakom dodamo presledek.
1. Pritisnite druga + F11 tipke, da omogočite Microsoft Visual Basic za aplikacije okno.
2. V pojavnem oknu kliknite Vstavi > Moduli da vstavite nov prazen modul.
3. Kopirajte in prilepite pod kodo VBA v modul.
Dodajte besedilo med znake
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. Shranite kodo in zaprite okno, da se vrnete na delovni list, nato vnesite spodnjo formulo v celico, v katero želite postaviti rezultat dodajanja.
=Dodaj besedilo(B3)
B3 je celica, ki vsebuje niz, ki mu želite dodati presledek med znaki.
5. Pritisnite Vnesite tipko, da dobite rezultat, povlecite ročico za samodejno izpolnjevanje čez celice, da dobite druge rezultate, kot jih potrebujete.
|
pozornost: 1） V kodi VBA lahko spremenite "" na druga besedila, kot jih potrebujete, predpostavljamo, da ga spremenite v "-" dodati - med znaki. 2） Koda bo dodala novo besedilo za vsakim znakom. Zadnje dodano besedilo lahko odstranite, če ga ne potrebujete, s spodnjo formulo. Za več podrobnosti o formuli obiščite: Kako odstraniti prvi ali zadnji N znak iz celice ali niza v Excel? LEFT(celica,LEN(celica)-dolžina_besedila) |
7.2 Dodajte presledek med vsako številko s formulo
Če želite med števkami niza dodati presledek, ki je številska vrednost, lahko uporabite funkcijo BESEDILO.
| pozornost: številke obsega, med katerim želite dodati presledek, morajo biti enake dolžine, sicer bodo nekateri rezultati morda napačni. |
Predpostavimo, da je tukaj seznam 8-mestnih številk v obsegu B10:B12, izberite celico, ki bo postavila rezultat, vnesite spodnjo formulo:
=TEXT(B10,"# # # # # # # #")
Pritisnite Vnesite tipko, nato povlecite ročico samodejnega izpolnjevanja čez druge celice.
Če je dolžina števil 5, se formula spremeni kot =BESEDILO(B10,"# # # # #").
Če želite dodati znak(e) med vsako besedo, predpostavimo, da dodate presledek med imenom, srednjim in priimkom, kot je prikazano na spodnji sliki zaslona, lahko uporabite uporabniško definirano funkcijo.
1. Pritisnite druga + F11 tipke, da omogočite Microsoft Visual Basic za aplikacije okno.
2. V pojavnem oknu kliknite Vstavi > Moduli da vstavite nov prazen modul.
3. Kopirajte in prilepite pod kodo VBA v modul.
Dodajte znake med besedami
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. Shranite kodo in zaprite okno, da se vrnete na delovni list, nato vnesite spodnjo formulo v celico, v katero želite postaviti rezultat dodajanja.
=Dodaj znake (B3)
B3 je celica, ki vsebuje niz, ki mu želite dodati presledek med besedami.
5. Pritisnite Vnesite tipko, da dobite rezultat, povlecite ročico za samodejno izpolnjevanje čez celice, da dobite druge rezultate, kot jih potrebujete.
| pozornost: v kodi VBA lahko spremenite "" na druga besedila, kot jih potrebujete, predpostavljamo, da ga spremenite v "-" dodati - med besedami. |
Če želite dodati določene znake med besedila in številke, kot je prikazano na spodnjem posnetku zaslona, sta tukaj na voljo dva načina.
9.1 Dodajte znak med besedila in številke s formulo
Če so besedila pred številkami, je generična formula
TRIM(REPLACE(string,MIN(NAJDI({1,2,3,4,5,6,7,8,9,0},string&"1234567890")),0,"-"))
Kot primer uporabite zgornji primer, uporabite formulo, kot je ta:
=TRIM(REPLACE(B4,MIN(FIND({1,2,3,4,5,6,7,8,9,0},B4&"1234567890")),0,"-"))
Pritisnite Vnesite tipko, da dobite prvi rezultat, nato povlecite ročico za samodejno izpolnjevanje čez druge celice, da izpolnite to formulo.
Če so številke pred besedili, je generična formula
LEFT(niz,SUM(LEN(niz)-LEN(NADOMEST(niz,{0,1,2,3,4,5,6,7,8,9},""))))&"."&RIGHT (niz,LEN(B13)- SUM(LEN(niz)-LEN(SUBSTITUTE(niz, {0,1,2,3,4,5,6,7,8,9}, ""))))
Če uporabite zgornji primer kot primer, uporabite formulo, kot je ta:
=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}, ""))))
Pritisnite Vnesite tipko, da dobite prvi rezultat, nato povlecite ročico za samodejno izpolnjevanje čez druge celice, da izpolnite to formulo.
9.2 Dodajte znak med besedila in številke s Flash Fill
Če uporabljate Excel 2013 ali novejše različice, Flash Fill prav tako vam lahko pomaga pri dodajanju znakov med besedila in številke.
Poleg izvirnih podatkov vnesite nove podatke, ki so jim dodali znak(e) med besedila in številke. Nato pritisnite tipko Enter, da se premaknete na naslednjo celico prvega rezultata.
In pritisnite Ctrl + E tipke za omogočanje Flash Fill.
Podaljšanje
Ta vadnica navaja tudi nekaj scenarijev o dodajanju besedila, ki ga lahko srečamo v vsakdanjem življenju ali delu.
Včasih morate seriji telefonskih številk ali družabnih številk dodati ločila, kot so črtice, kot je prikazano na spodnjem posnetku zaslona. Ročno vnašanje bo poskusno, če je treba dodati na stotine številk. V tem delu predstavlja tri trike, kako hitro opraviti to delo.
10.1 Številkam dodajte pomišljaje s formulo
Tukaj sta dve formuli, ki lahko hitro dodata pomišljaje številkam. Če želite dodati pomišljaje po tretji in šesti številki v celici B3, uporabite formulo, kot je ta:
Funkcija REPLACE
=ZAMENJAJ(ZAMENJAJ(B3,4,0,"-"),8,0,"-")
TEXT funkcija
=BESEDILO(B3,"???-???-??? ")
Pritisnite Vnesite tipko, da dobite prvi rezultat, nato povlecite ročico za samodejno izpolnjevanje čez druge celice.
|
pozornost: Argumente zgornjih formul lahko po potrebi spremenite. Predpostavimo, da dodate "-" po drugi, četrti in šesti števki lahko uporabite formule kot: =ZAMENJAJ(ZAMENJAJ(ZAMENJAJ(B3,3,0,"-"),6,0,"-"),9,0,"-") =BESEDILO(B3,"??-??-??-???") |
10.2 Številkam dodajte črtice z obliko celic
Če želite številkam v izvirnih celicah neposredno dodati pomišljaje, lahko uporabite Oblikuj celice lastnost.
1. Izberite številke, ki jim želite dodati pomišljaje, nato pritisnite Ctrl + 1 tipke, da omogočite Oblikuj celice dialog.
2. V Ljubljani Oblikuj celice pod pogovornim oknom Število izberite jeziček po meri Iz Kategorija oddelek.
3. Nato v desni tip odsek, tip ###-###-### v besedilno polje kliknite OK.
Zdaj so izbrane številke dodane pomišljaje.
10.3 Dodajte pomišljaje telefonski številki z dodajanjem besedila Kutools for Excel
Če imate Kutools za Excel nameščen v Excelu, dodajanje besedila funkcija vam lahko tudi naredi uslugo.
1. Izberite celice, ki vsebujejo številke, ki jih je treba dodati pomišljajem, kliknite Kutools > Besedilo > Dodaj besedilo.
2. V Ljubljani dodajanje besedila pogovorno okno, vnesite pomišljaj ali drugo ločilo Besedilo besedilo, izberite Določite možnost, nato v spodnje besedilno polje vnesite položaje, za katerimi želite dodati pomišljaje, in jih ločite z vejicami.
3. klik Ok ali Uporabi. Nato so bile izbrane številke dodane črtice.
Brezplačno prenesite Add Text za 30-dnevno preskusno različico.
Predpostavimo, da obstaja seznam številk različnih dolžin, želite dodati vodilne ničle, da bodo enake dolžine in izgledajo lepo, kot je prikazano na spodnji sliki zaslona. Tukaj sta na voljo dva načina za obvladovanje dela.
11.1 Dodajte vodilne ničle celicam s funkcijo Oblikuj celice
Lahko uporabite Oblikuj celice funkcija za nastavitev oblike po meri za določitev dolžine celice z dodajanjem vodilnih ničel.
1. Izberite celice, ki jim želite dodati vodilne ničle, nato pritisnite Ctrl + 1 tipke za omogočanje Oblikuj celice dialog.
2. V Ljubljani Oblikuj celice pod pogovornim oknom Število izberite jeziček po meri Iz Kategorija oddelek.
3. Nato v desni tip odsek, tip 0000 v polje z besedilom (če želite določiti dolžino številke na 4-dight, vnesite 0000, lahko jo spremenite v drugo dolžino, na primer 5-dight, vnesite 00000), kliknite OK.
Nato so bile številke dodane vodilne ničle in v enaki dolžini.
| pozornost: če dolžina izvirnih podatkov presega nastavljeno dolžino, bodo prikazani izvirni podatki brez začetnih ničel. |
11.2 Dodajte vodilne ničle celicam s formulo
Če ne želite spremeniti prvotnih podatkov, lahko s formulo dodate vodilne ničle številkam na drugih lokacijah.
Tu so vam lahko v pomoč tri funkcije.
Funkcija BESEDILO formule 1
TEXT(številka,"00...")
Funkcija Formula2 DESNA
DESNO("00..."&številka, dolžina)
Funkcija Formula3 BASE
BASE(številka,10,dolžina)
Število ničel v formuli je enako dolžini števila.
Tukaj nastavite 4-mestno dolžino številk, uporabite spodnje formule:
= BESEDILO (B10, "0000")
=DESNO("0000"&B10,4)
=OSNOVA(B10,10,4;XNUMX;XNUMX)
Pritisnite Vnesite tipko in povlecite ročico za samodejno polnjenje navzdol.
| pozornost: če dolžina izvirnih podatkov presega nastavljeno dolžino, bodo prikazani izvirni podatki brez začetnih ničel. |
Če želite številkam dodati končne ničle za določitev fiksne dolžine, če predpostavimo, da dodate končne ničle številkam v celici B3:B7, da jih naredite v 5-mestni dolžini, kot je prikazano na spodnjem posnetku zaslona, lahko uporabite formulo za obravnavo opravila .
Funkcija REPT
številka&REPT("0", dolžina-LEN(številka))
V formuli argument "številka" predstavlja številko ali referenco celice, ki ji želite dodati končne ničle, argument "dolžina" pa je določena dolžina, ki jo želite.
Prosimo, uporabite naslednjo formulo:
=B3&REPT("0",5-LEN(B3))
Pritisnite Vnesite tipko in povlecite ročico za samodejno izpolnjevanje navzdol do celic, ki potrebujejo to formulo.
Če želite izvoziti ali kopirati podatke z delovnega lista v beležnico, so lahko besedila postavljena neurejeno, saj so vrednosti celic različnih dolžin, zato morate dodati presledek na koncu, da bodo videti čedne, kot je prikazano na spodnjem posnetku zaslona.
Za reševanje tega dela vam lahko funkcija REPT naredi uslugo.
LEVO(besedilo & REPT(" ",max_length),max_length)
V formuli je argument "besedilo" besedilo ali sklic na celico, ki ji želite dodati končni presledek, argument "max_length" pa je fiksna dolžina, za katero želite narediti besedila, ki mora biti daljša ali enaka največja dolžina uporabljenih besedil.
Če želite besedilom v celici B3:C6 dodati presledek na koncu, uporabite spodnjo formulo:
=LEVO(B3 & REPT(" ",10),10)
Pritisnite Vnesite tipko, nato povlecite ročico za samodejno izpolnjevanje na vse celice, ki potrebujejo to formulo.
V tem primeru, ker je najdaljše uporabljeno besedilo 9-znakovno, tukaj uporabimo 10.
Včasih morate okoli besedila ali številk v celicah dodati narekovaje ali oklepaje, kot je prikazano na spodnjem posnetku zaslona, razen ročnega tipkanja enega za drugim, tukaj sta dva načina v Excelu, ki lahko to hitro rešita.
14.1 Dodajte narekovaje/oklepaje po Oblikuj celice
Če želite v izvirne podatke dodati narekovaje okoli besedila, vam lahko pomaga funkcija Oblikovanje celic v Excelu.
1. Izberite celice, ki jim želite dodati narekovaje, pritisnite Ctrl + 1 tipke, da omogočite Oblikuj celice dialog.
2. V Ljubljani Oblikuj celice pogovorno okno, pod Število izberite jeziček po meri s seznama Kategorija, nato pojdite na desni razdelek, da vnesete "''" @ "''" v tip besedilno polje. Kliknite OK.
Besedilo v izbranih celicah je bilo dodano z narekovaji.
Če želite okoli besedila dodati oklepaje, v besedilnem polju Tip pogovornega okna Oblikovanje celic uporabite (@).
14.2 Dodajte narekovaje/oklepaje po formulah
Formule se lahko uporabljajo tudi za dodajanje narekovajev besedilu v drugih celicah.
Uporaba & konektor
""""&besedilo&""""
Or
char(34)&besedilo&char(34)
V tem primeru besedilo v celici B11 uporabite formule, kot so:
=""""&B11&""""
Or
=char(34)&B11&char(34)
Pritisnite Vnesite tipko in povlecite ročico za samodejno izpolnjevanje navzdol, da zapolnite celice.
Če želite dodati oklepaje okoli besedila, uporabite formulo kot "("&besedilo&")".
14.3 VBA dodajte narekovaje/oklepaje
Če potrebujete kodo VBA za rešitev tega opravila, je tukaj VBA.
1. Pritisnite druga + F11 tipke, da omogočite Microsoft Visual Basic za aplikacije okno.
2. Nato v pojavnem oknu kliknite Vstavi > Moduli da vstavite prazen nov modul.
3. Kopirajte in prilepite pod kodo VBA v nov modul.
VBA: Dodajte narekovaje okoli besedil
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| pozornost: v skriptu kode, """" & Rng.Value & """" označuje, da dodate "" okoli besedil, če želite dodati oklepaje () okoli besedila, ga spremenite v "(" & Rng.Value & ")". |
4. Pritisnite F5 tipko ali kliknite Run Gumb v oknu, da aktivirate kodo VBA. Pojavi se pogovorno okno za izbiro celic za dodajanje narekovajev.
5. klik OK. Zdaj so bili okoli besedil v izbranih celicah dodani narekovaji.
Včasih boste morda morali v rezultate formule dodati besedilo, da bi drugim uporabnikom pomagali bolje razumeti podatke, kot je prikazano na spodnjem posnetku zaslona. Za vključitev besedila v formulo lahko uporabite narekovaje, ki obdajajo besedilo, in dodate povezovalnik &, da združite besedilo in formulo skupaj.
Primer 1 dodajte besedilo pred formulo
"Danes je "&BESEDILO(TODAY(),"dddd, mmmm dd.")
Primer 2 dodajte besedilo za Formula
TEXT(NOW(),"HH:MM:SS")&" je trenutni čas."
Primer 3 dodajte besedila na dveh straneh formule
"Danes prodanih "&data&" kg."
Več vadnic za Excel: |
|
Združite več delovnih zvezkov/delovnih listov v enega Razdelite besedilo, številko in datumske celice (ločite v več stolpcev) Združite vsebino več celic brez izgube podatkov v Excelu Primerjajte dva stolpca za ujemanja in razlike v Excelu |
Najboljša orodja za pisarniško produktivnost
Kutools za Excel rešuje večino vaših težav in poveča vašo produktivnost za 80%
- Vrstica Super Formula (enostavno urejanje več vrstic besedila in formule); Bralna postavitev (enostavno branje in urejanje velikega števila celic); Prilepite v filtrirani obseg...
- Združi celice / vrstice / stolpce in vodenje podatkov; Vsebina razdeljenih celic; Združite podvojene vrstice in vsoto / povprečje... prepreči podvojene celice; Primerjaj obsege...
- Izberite Duplicate ali Unique Vrstice; Izberite prazne vrstice (vse celice so prazne); Super Find in Fuzzy Find v mnogih delovnih zvezkih; Naključna izbira ...
- Natančna kopija Več celic brez spreminjanja sklica formule; Samodejno ustvarjanje referenc na več listov; Vstavi oznake, Potrditvena polja in še več ...
- Priljubljene in hitro vstavite formule, Obsegi, grafikoni in slike; Šifriraj celice z geslom; Ustvari poštni seznam in pošiljanje e-pošte ...
- Izvleček besedila, Dodaj besedilo, Odstrani po položaju, Odstrani presledek; Ustvari in natisni vmesne seštevke strani Pretvarjanje med vsebino celic in komentarji...
- Super filter (shranite in uporabite sheme filtrov za druge liste); Napredno razvrščanje glede na mesec / teden / dan, pogostost in drugo; Poseben filter s krepko, ležeče ...
- Združite delovne zvezke in delovne liste; Spoji tabele na podlagi ključnih stolpcev; Razdelite podatke na več listov; Paketna pretvorba xls, xlsx in PDF...
- Razvrščanje vrtilne tabele po številka tedna, dan v tednu in še več ... Prikaži odklenjene, zaklenjene celice po različnih barvah; Označite celice s formulo / imenom...
- Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
- Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
- Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!
