Az Excel szöveget és számot ad hozzá a cella megadott pozíciójához
Az Excelben nagyon gyakori feladat a szövegek vagy számok hozzáadása a cellákhoz. Például szóköz hozzáadása a nevek között, előtag vagy utótag hozzáadása a cellákhoz, kötőjelek hozzáadása a közösségi számokhoz. Ebben az oktatóanyagban szinte az összes forgatókönyvet felsorolja az Excelben való hozzáadáshoz, és megadja a megfelelő módszereket.
Megjegyzések
Ebben az oktatóanyagban létrehozok néhány példát a módszerek magyarázatára, módosíthatja a szükséges hivatkozásokat, ha az alábbi VBA-kódot vagy képleteket használja, vagy letöltheti a mintákat közvetlenül a módszerek kipróbálásához.
Kattintson a minta fájl letöltéséhez
Ez a rész különböző módszereket sorol fel arra, hogyan lehet karaktereket hozzáadni az egyes cellák elejéhez, ahogy az alábbi képernyőképen látható:
1.1 Adja hozzá a cellák elejéhez a képlet segítségével
Itt választhat az alábbi három képlet közül:
Formula1 "&" operátor összefűzése
Egyesítsen több szöveget egy „&” jellel.
"szöveg"&cella
Formula2 CONCATENATE funkció
A CONCATENATE funkció a szövegek összekapcsolására szolgál.
CONCATENATE("szöveg",cella)
Formula3 CONCAT funkció
Ez egy új funkció, amely csak az Excel 2019-ben, az Office 365-ben és az Excel online verziójában jelenik meg.
CONCAT("szöveg",cella)
Az általános képletekben: a cella annak a szövegnek a cellahivatkozása, amelyhez előtagot kíván adni, a szöveg pedig az a szöveg, amelyet a cellához használtak.
A fenti példa segítségével a képletsorban a következőképpen használhatja a képleteket:
"&"
= $3&B3 or ="Iphone"&B3
CONCATENATE funkció
=ÖSSZEFÜGGÉS ($E$3,B3) or =CONCATENATE("Iphone",B3)
CONCAT funkció
=CONCAT($E$3,B3) or =CONCAT("Iphone",B3)
nyomja meg belép gombot az eredmény eléréséhez, majd húzza le az automatikus kitöltési fogantyút, hogy szöveget adjon a B3:B6 tartomány minden cellájához.
| Figyelem: A szöveget idézőjelbe kell tenni, különben a képlet a #NAME? hibaértéket adja vissza. Ha cellahivatkozást használ egy szövegre, ne felejtse el használni az abszolút hivatkozást, az F4 billentyű lenyomásával módosíthatja a relatív hivatkozást abszolút hivatkozásra. |
1.2 Hozzáadás a cellák elejéhez Flash Fill segítségével
Ha az Excel 2013-as vagy újabb verzióit használja, a hatékony új funkció, Flash töltés, az automatikusan beírt fenti cellák alapján tudja kitölteni a cellákat.
Válasszon egy cellát az első eredeti adat mellett, és manuálisan írja be az első adatot az előtag szövegével az alábbiak szerint:
Ezután az alábbi cellában folytassa a második adat beírását előtag szöveggel, gépelés közben egy szürke színű lista jelenik meg, nyomja meg a belép gombot, hogy a Flash Fill automatikusan kitöltse az adatokat.
Ha az előnézeti lista nem jön létre, lépjen a Kezdőlap fülre, majd kattintson a gombra Tölt > Flash töltés manuálisan futtatni.
1.3 Adja hozzá a cellák elejéhez VBA kóddal
Ha ismeri a VBA-kódot, itt bemutatunk egy VBA-kódot, amellyel szöveget adhatunk a tartomány minden cellájához.
1. Jelölje ki azt a cellatartományt, amelyhez az előtag szövegét hozzá kívánja adni, majd nyomja meg a gombot más + F11 gombok a Microsoft Visual Basic for Applications ablak.
2. Ezután a felugró ablakban kattintson a gombra betétlap > Modulok üres új modul beillesztéséhez.
3. Másolja ki és illessze be az alábbi VBA-kódot az új modulba.
VBA: Hozzáadás a cellák elejéhez
Sub AppendToExistingOnLeft()
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = "Iphone" & c.Value
Next
End Sub| Figyelem: a kódszkriptben, iphone az a szöveg, amelyet a kijelölt cellák elejére szeretne hozzáadni, módosítsa igény szerint. |
4. nyomja meg F5 vagy kattintson futás gomb 
az ablakban a VBA kód aktiválásához.
Most az összes kijelölt cella elejére az "Iphone" szöveget adta.
A szöveg hozzáadása a cellák elejéhez szintén gyakori. Példákként az egyes cellák végéhez egy egység hozzáadását vesszük, az alábbi képernyőképen látható módon:
2.1 Adja hozzá a cellák végéhez képlet segítségével
Itt választhat az alábbi három képlet közül:
Formula1 "&" operátor összefűzése
Egyesítsen több szöveget egy „&” jellel.
Cell&"szöveg"
Formula2 CONCATENATE funkció
A CONCATENATE funkció a szövegek összekapcsolására szolgál.
ÖSSZEFÜGGÉS(cella"szöveg")
Formula3 CONCAT funkció
Ez egy új funkció, amely csak az Excel 2019-ben, az Office 365-ben és az Excel online verziójában jelenik meg.
CONCAT(cella"szöveg")
Az általános képletekben: a cella annak a szövegnek a cellahivatkozása, amelyhez utótagot kíván adni, a szöveg pedig az a szöveg, amelyet a cellához használtak.
A fenti példa segítségével a képletsorban a következőképpen használhatja a képleteket:
"&"
=B3&$E$3 or =B3&"Kg"
CONCATENATE funkció
=ÖSSZESÍTÉS (B3, $E$3) or =ÖSSZEFÜGGÉS (B3, "Kg")
CONCAT funkció
=CONCAT(B3, $E$3) or =CONCAT(B3; "Kg")
nyomja meg belép gombot az eredmény eléréséhez, majd húzza le az automatikus kitöltési fogantyút, hogy szöveget adjon a B3:B6 tartomány minden cellájának végéhez.
| Figyelem: A szöveget idézőjelbe kell tenni, különben a képlet a #NAME? hibaértéket adja vissza. Ha cellahivatkozást használ egy szövegre, ne felejtse el használni az abszolút hivatkozást, az F4 billentyű lenyomásával módosíthatja a relatív hivatkozást abszolút hivatkozásra. |
2.2 Hozzáadás a cellák végéhez Flash Fill segítségével
Ha az Excel 2013-as vagy újabb verzióit használja, a hatékony új funkció, Flash töltés, az automatikusan beírt fenti cellák alapján tudja kitölteni a cellákat.
Válasszon egy cellát az első eredeti adat mellett, és manuálisan írja be az első adatot az utótag szövegével az alábbiak szerint:
Ezután aktiválja az első eredmény alatti cellát, kattintson dátum > Flash töltés, minden alábbi cella, beleértve az aktív cellát is, automatikusan kitöltésre kerül.
2.3 Adja hozzá a cellák végéhez VBA kóddal
Itt van egy VBA-kód, amely szöveget adhat a tartomány minden cellájának végére.
1. Jelölje ki azt a cellatartományt, amelyhez az utótag szövegét hozzá kívánja adni, majd nyomja meg a gombot más + F11 gombok a Microsoft Visual Basic for Applications ablak.
2. Ezután a felugró ablakban kattintson a gombra betétlap > Modulok üres új modul beillesztéséhez.
3. Másolja ki és illessze be az alábbi VBA-kódot az új modulba.
VBA: Hozzáadás a cellák végéhez
Sub AppendToExistingOnRight()
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = c.Value & "Kg"
Next
End Sub| Figyelem: a kódszkriptben, Kg az a szöveg, amelyet a kijelölt cellák végére kíván adni, módosítsa igény szerint. |
4. nyomja meg F5 vagy kattintson futás gomb az ablakban a VBA kód aktiválásához.
Most a "Kg" szöveg került minden cella végére.
Legtöbbjük számára egyszerű a szövegek hozzáadása a cellák elejére vagy végére, de a cellák közepére talán kissé nehézkes. Vegyünk egy példát, ahogy az alábbi képernyőképen látható, és adjunk hozzá egy határolót, például kettőspontot : a B3:B7 tartomány minden cellájának második karakterének végéhez.
3.1 Adja hozzá a karakterlánc n-edik karakterét képlettel
Íme négy képlet a feladat elvégzéséhez, válasszon egyet, amelyet használni szeretne:
Formula1 A BAL és JOBB függvények kombinálása
BAL(cella, n) & "szöveg" & JOBB(cella, LEN(cella) -n)
Formula2 COMBINE CONCATENATE(CONCAT), RIGHT és LEN funkciókat
ÖSSZEFÜGGÉS(BAL(cella, n), "szöveg", JOBBRA(cella, LEN(cella) -n))
Or
CONCAT(BAL(cella, n), "szöveg", JOBBRA(cella, LEN(cella) -n))
Formula3 REPLACE függvény
REPLACE(cella, n+1, 0, "szöveg")
Az általános képletekben: a cella a cella hivatkozása arra a szövegre, amelyhez szöveget szeretne hozzáadni a közepén, és a szöveg az a szöveg, amelyet a cellához hozzáadtak, n az a szám, amely meghatározza, hogy a szövegben melyik karakter után kívánja hozzáadni a szöveget. szöveg hozzáadása.
A fent említett feladat megoldásához használja az alábbi képleteket:
A BAL és JOBB funkciók kombinálása
=BAL(B3) & ":" & JOBB(B2,HOSSZ(B3)-3) or =BAL(B3) & $E$2 & JOBB(B3,HOSSZ(B3)-3)
COMBINE CONCATENATE(CONCAT), RIGHT és LEN funkciókat
=ÖSSZESÍTÉS(BAL(B3, 2), ":", JOBBRA(B3, LEN(B3) -2)) or =CONCAT (BAL(B3, 2), ":", JOBB(B3, LEN(B3) -2))
REPLACE funkció
=CSERE(B3; 2+1; 0; ":") or =CSERE(B3, 2+1, 0, 3 USD)
nyomja meg belép gombot az eredmény eléréséhez, majd húzza le az automatikus kitöltési fogantyút, hogy szöveget adjon a B3:B7 tartomány egyes celláinak második karaktere után.
| Figyelem: A szöveget idézőjelbe kell tenni, különben a képlet a #NAME? hibaértéket adja vissza. Ha cellahivatkozást használ egy szövegre, ne felejtse el használni az abszolút hivatkozást, az F4 billentyű lenyomásával módosíthatja a relatív hivatkozást abszolút hivatkozásra. |
3.2 Adja hozzá a karakterlánc n-edik karakterét a Flash Fill segítségével
Ha az Excel 2013-as vagy újabb verzióit használja, a Flash töltés, az automatikusan beírt fenti cellák alapján tudja kitölteni a cellákat.
Válasszon egy cellát az első eredeti adat mellett, kézzel írja be az első adatot kettősponttal a második karakter után az alábbiak szerint:
Ezután aktiválja az első eredmény alatti cellát, nyomja meg a gombot Ctrl + E gombokat az aktiváláshoz Flash töltés parancsot, az alábbi cellák, beleértve az aktív cellát is, automatikusan kitöltésre kerültek.
| Figyelem: Ha MAC rendszert használ, nyomja meg a Command + E billentyűket. |
3.3 Adja hozzá a karakterlánc n-edik karakterét VBA kóddal
A VBA kód is képes erre a feladatra.
1. nyomja meg más + F11 gombok a Microsoft Visual Basic for Applications ablak.
2. Ezután a felugró ablakban kattintson a gombra betétlap > Modulok üres új modul beillesztéséhez.
3. Másolja ki és illessze be az alábbi VBA-kódot az új modulba.
VBA: Hozzáadás a cellák közepéhez
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| Figyelem: a kódszkriptben, 2 a szám azt a helyet határozza meg, amely után szöveget kíván hozzáadni, : az a szöveg, amelyhez hozzá szeretne adni, 3 2 és a hozzáadott szöveg hosszának összege. Vegyünk egy másik példányt, és az „and” karakterlánc hozzáadásához az „AB” karakterlánc első karaktere után a szkriptnek Rng.Value = VBA.Left(Rng.Value, 1) & "és a" & VBA.Mid(Rng.Value, 4, VBA.Len(Rng.Value) - 1) |
4. nyomja meg F5 vagy kattintson futás gomb az ablakban a VBA kód aktiválásához. Egy párbeszédablak jelenik meg a szöveg hozzáadásához szükséges cellák kiválasztásához.
5. kettyenés OK. Most a ":" szöveg a B19:B23 tartomány minden cellájának második karaktere után lett hozzáadva.
4. Adjon hozzá szöveget a Kutools for Excel segítségével (adjon karaktereket adott pozícióhoz, ha szüksége van rá)
Ha belefáradt a képletekbe és a VBA-ba, kipróbálhat egy hatékony és praktikus eszközt, Kutools az Excel számára, amely segíthet abban Szöveg hozzáadása funkciót.
Ez a szöveg hozzáadása funkció:
- Szöveg hozzáadása az első karakter elé
- Szöveg hozzáadása az utolsó karakter után
- Szöveg hozzáadása a megadott helyeken
- Kiterjesztés
Szöveg hozzáadása a nagybetűk elé
Szöveg hozzáadása kisbetűk elé
Szöveg hozzáadása a kis- és nagybetűk elé
Szöveg hozzáadása a numerikus karakterek elé
Kattintson a 30 napos ingyenes próbaverzióért most.
Jelölje ki a szöveget hozzáadni kívánt cellákat, majd kattintson a Szöveg hozzáadása lehetőségre Kutools > szöveg > Szöveg hozzáadása.
4.1 Szöveg hozzáadása az első karakter elé vagy az utolsó karakter után
A Szöveg hozzáadása párbeszédpanelen írja be a cellákhoz hozzáadni kívánt szöveget szöveg szövegdobozt, majd ellenőrizze Az első karakter előtt a pozíció szakasz.
Kattints alkalmaz or Ok hogy befejezzem a hozzáadást.
Ha szöveget szeretne hozzáadni az utolsó karakter után, írja be a cellákhoz hozzáadni kívánt szöveget szöveg szövegdobozt, majd ellenőrizze Utolsó karakter után a pozíció szakasz.
Kattints alkalmaz or Ok hogy befejezzem a hozzáadást.
4.2 Szöveg hozzáadása a megadott pozíció(k)hoz
Néha érdemes lehet szöveget hozzáadni a cella közepéhez, vagy szöveget adni a cella több pozíciójához. Tételezzük fel, hogy a közösségi számok harmadik és hetedik számjegye után „-” elválasztót adunk, ahogy az alábbi képernyőképen látható:
A Szöveg hozzáadása párbeszédablakba írja be a szöveget szöveg szövegmezőt, és ellenőrizze Adja meg opciót, majd vesszővel írja be a pozíciókat a szövegmezőbe, majd kattintson a gombra Ok or alkalmaz.
4.3 A Szöveg hozzáadása segédprogram kiterjesztése
Amellett, hogy szöveget ad hozzá a normál pozíciókhoz (kezdő, vég és megadott pozíció), a Szöveg hozzáadása segédprogram is támogatja a szöveg hozzáadását
- Nagybetűk előtt
- Kisbetűk előtt
- Kis-/nagybetűk előtt
- Numerikus karakterek előtt
A Szöveg hozzáadása párbeszédablakba írja be a szöveget szöveg szövegmezőt, és ellenőrizze Csak add hozzá opciót, majd kattintson az alábbi legördülő menüre a kívánt művelet kiválasztásához.
Ez a rész képleteket tartalmaz a szöveg hozzáadásához az Excelben először megjelenő konkrét szöveg előtt vagy után.
5.1 Szöveg hozzáadása az első konkrét szöveg elé
Például fel szeretné venni a „(akció)” szöveget az első „ár” szöveg elé a cellában, ahogy az alábbi képernyőképeken látható:
Az Excelben a REPLACE és a SUBSTITUTE függvényekkel lehet megoldani ezt a feladatot.
REPLACE funkció
REPLACE(cella,FIND("szöveg keresése",cella),0,"szöveg hozzáadása")
SUBSTITUTE funkció
SUBSTITUTE(cella"szöveg keresése","szöveg csere",1)
Ebben az esetben használja a fenti képleteket:
=CSERE(B4;KERESÉS("ár",B4),0,"(akció)")
Or
=SUBSTITUTE(B4"ár","(eladási)ár",1)
A formula argumentumai ebben az esetben a következők
cella: B4,
Find_text: ár,
Add_text: (akció),
Csere_szöveg:(akciós)ár.
nyomja meg belép gombot, hogy megkapja az első hozzáadási eredményt, majd húzza az automatikus kitöltési fogantyút a képletet igénylő cellák fölé.
5.2 Szöveg hozzáadása az első konkrét szöveg után
Például a "$" pénznemszimbólumot szeretné hozzáadni a ":" első kettőspont után a cellába, ahogy az alábbi képernyőképeken látható:
Az Excelben a REPLACE és a SUBSTITUTE függvényekkel lehet megoldani ezt a feladatot.
REPLACE funkció
REPLACE(cella,FIND("szöveg keresése",cella),szöveg_hossza,"szöveg cseréje")
SUBSTITUTE funkció
SUBSTITUTE(cella"szöveg keresése","szöveg csere")
Ebben az esetben használja a fenti képleteket:
=CSERE(B12;KERES(":",B12),1,":$")
Or
=SUBSTITUTE(B12,":",":$")
A formula argumentumai ebben az esetben a következők
cella: B12,
Szöveg keresése: :,
Csere_szöveg::$.
Find_text_length:1
nyomja meg belép gombot, hogy megkapja az első hozzáadási eredményt, majd húzza az automatikus kitöltési fogantyút a képletet igénylő cellák fölé.
Az oktatóanyag ezen részében három különböző módszert mutat be (képlet, Keresés és csere, VBA), amelyek segítségével karaktereket adhat hozzá a cellák minden szava elé vagy után.
| Adjon hozzá karaktereket minden szó elé |
 |
| Adjon hozzá karaktereket minden szó után |
 |
6.1 Adjon hozzá karaktere(ke)t minden szó elé vagy után képlet segítségével
A SUBSTITUTE függvény használható ennek a feladatnak a megoldására, az alábbi általános képletekkel:
Add hozzá minden szó elé
"karakter"&SUBSTITUTE(karakterlánc, " "," karakter")
Add hozzá minden szó után
SUBSTITUTE(karakterlánc", ","karakter ")&"karakter"
Tegyük fel, hogy a B3:B6 listában minden szó elé "+"-t ad, használja a következő képletet:
="+"&SUBSTITUTE(B3, " "," +")
nyomja meg belép gombot az első eredmény eléréséhez, majd húzza le az automatikus kitöltési fogantyút az összes eredmény megjelenítéséhez.
Tételezzük fel, hogy a B11:B14 lista minden szava után ($) kerül hozzáadásra, kérjük, használja az alábbi képletet:
=SUBSTITUTE(B11," ","($) ")&"($)"
nyomja meg belép gombot az első eredmény eléréséhez, majd húzza le az automatikus kitöltési fogantyút az összes eredmény megjelenítéséhez.
6.2 Karakter(ek) hozzáadása minden szó elé vagy után a Keresés és csere funkcióval
Ha karaktereket szeretne hozzáadni minden egyes szó elé, kivéve az elsőt, vagy karaktereket szeretne hozzáadni minden egyes szóhoz, kivéve az utolsót az alábbi képernyőképen látható módon, az Excel beépített Keresés és csere segédprogram tudja kezelni ezt a munkát.
Adja hozzá minden szóhoz, az elsőt kivéve
Adja hozzá minden szó után, kivéve az utolsót
1. Jelölje ki azokat a cellákat, amelyekbe karaktereket szeretne hozzáadni az egyes szavak elé vagy után, majd nyomja meg a gombot Ctrl + H gombok a Keresés és csere párbeszéd.
2. Írjon be egy szóközt a Mit keres szövegdobozba, majd szóközzel írja be az új karaktereket Cserélje ki szövegdobozt, és végül kattintson a Az összes cseréje gombot.
| Művelet | általános | Példa |
| Adjon hozzá karaktereket minden szó elé | Új karakterek hozzáadása a szóköz után | Add (eladás) minden szó előtt |
| Művelet | általános | Példa |
| Adjon hozzá karaktereket minden szó után | Az új karaktereket szóköz követi | Add - minden szó után |
Itt két VBA-kód található a karakterek hozzáadásához minden szó előtt vagy után. Futtassa a kódot az alábbi lépések szerint.
1. nyomja meg más + F11 gombok a Microsoft Visual Basic for Applications ablak.
2. A felbukkanó ablakban kattintson a gombra betétlap > Modulok új üres modul beillesztéséhez.
3. Másolja ki és illessze be az alábbi VBA kódot a modulba.
Add hozzá minden szó előtt
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 SubAdd hozzá minden szó után
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. nyomja meg F5 gombot a kód futtatásához (vagy kattintson rá futás gomb ), megjelenik egy párbeszédpanel, amely arra kéri, hogy válasszon ki folyamatos cellákat a munkavégzéshez, kattintson OK.
5. A második felugró párbeszédpanelen írja be a hozzáadni kívánt karaktereket, majd kattintson OK.
Ezután a karakterek minden szó elejére vagy végére kerülnek hozzáadásra.
Néha érdemes lehet szöveget hozzáadni az egyes karakterek közé. Ebben a részben két forgatókönyvet sorol fel, az egyik az, hogy minden karakter közé szöveget, a másik pedig szóközt ír be az egyes számok közé.
7.1 Szöveg hozzáadása minden karakter/számjegy közé Felhasználó által meghatározott funkcióval
Ha karaktereket szeretne hozzáadni egy karakterlánc minden karakteréhez, a Felhasználó által meghatározott funkció segíthet.
Ebben az esetben minden karakter közé szóközt adunk.
1. nyomja meg más + F11 gombok a Microsoft Visual Basic for Applications ablak.
2. A felbukkanó ablakban kattintson a gombra betétlap > Modulok új üres modul beillesztéséhez.
3. Másolja ki és illessze be az alábbi VBA kódot a modulba.
Szöveg hozzáadása a karakterek közé
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. Mentse el a kódot, és zárja be az ablakot, hogy visszatérjen a munkalaphoz, majd írja be az alábbi képletet egy cellába, amelybe el szeretné helyezni a hozzáadási eredményt.
=Szöveg hozzáadása(B3)
A B3 az a cella, amely azt a karakterláncot tartalmazza, amelyet szóközzel szeretne hozzáadni a karakterek közé.
5. nyomja meg belép gombot az eredmény eléréséhez, húzza az automatikus kitöltési fogantyút a cellák fölé, hogy szükség szerint további eredményeket kapjon.
|
Figyelem: 1) A VBA kódban módosíthatja a "" szükség szerint más szövegekre, tegyük fel, hogy módosítsa erre "-" a karakterek közé - hozzáadásához. 2) A kód minden karakter után új szöveget ad hozzá. Az alábbi képlet segítségével eltávolíthatja az utoljára hozzáadott szöveget, ha nem szükséges. A képletről további részletekért látogasson el a következő oldalra: LEFT(cella,LEN(cella)-szöveg_hossza) |
7.2 Adjon szóközt minden szám közé a képlettel
Ha szóközt szeretne hozzáadni egy karakterlánc számjegyei közé, amely numerikus érték, használhatja a SZÖVEG funkciót.
| Figyelem: a szóközt hozzáadni kívánt tartományok számának azonos hosszúságúnak kell lennie, különben egyes eredmények helytelenek lehetnek. |
Tegyük fel, hogy itt van egy 8 jegyű számok listája a B10:B12 tartományban, kérjük, válasszon ki egy cellát, amely elhelyezi az eredményt, és írja be az alábbi képletet:
=SZÖVEG(B10,"# # # # # # # #")
nyomja meg belép gombot, majd húzza az automatikus kitöltési fogantyút a többi cellára.
Ha a számok hossza 5, a képlet a következőre módosul =SZÖVEG(B10,"# # # # #").
Ha karakter(eke)t szeretne hozzáadni minden szó közé, feltéve, hogy szóközt kell hozzáadni az első, középső és vezetéknevek közé, ahogy az alábbi képernyőképen látható, használhat egy Felhasználó által meghatározott funkciót.
1. nyomja meg más + F11 gombok a Microsoft Visual Basic for Applications ablak.
2. A felbukkanó ablakban kattintson a gombra betétlap > Modulok új üres modul beillesztéséhez.
3. Másolja ki és illessze be az alábbi VBA kódot a modulba.
Karakterek hozzáadása a szavak közé
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. Mentse el a kódot, és zárja be az ablakot, hogy visszatérjen a munkalaphoz, majd írja be az alábbi képletet egy cellába, amelybe el szeretné helyezni a hozzáadási eredményt.
=Karakterek hozzáadása (B3)
A B3 az a cella, amely azt a karakterláncot tartalmazza, amelyhez szóközt szeretne hozzáadni.
5. nyomja meg belép gombot az eredmény eléréséhez, húzza az automatikus kitöltési fogantyút a cellák fölé, hogy szükség szerint további eredményeket kapjon.
| Figyelem: a VBA kódban módosíthatja a "" szükség szerint más szövegekre, tegyük fel, hogy módosítsa erre "-" hozzátenni - a szavak közé. |
Ha konkrét karakter(ek)et szeretne hozzáadni a szövegek és a számok közé, az alábbi képernyőképen látható módon, itt két módszer található.
9.1 Karakter hozzáadása a szövegek és számok közé képlet segítségével
Ha a szövegek a számok előtt vannak, akkor az általános képlet az
TRIM(REPLACE(karakterlánc,MIN(KERESÉS({1,2,3,4,5,6,7,8,9,0},karakterlánc&"1234567890")),0,"-"))
A fenti esetet példaként használva használja a képletet a következőképpen:
=TRIM(REPLACE(B4,MIN(FIND({1,2,3,4,5,6,7,8,9,0},B4&"1234567890")),0,"-"))
nyomja meg belép gombot az első eredmény eléréséhez, majd húzza az automatikus kitöltési fogantyút a többi cellára a képlet kitöltéséhez.
Ha a számok a szövegek elején vannak, akkor az általános képlet az
LEFT(karakterlánc,SZUM(HOSSZ(karakterlánc)-LEN(SUBSTITUTE(karakterlánc,{0,1,2,3,4,5,6,7,8,9},""))))&"."&JOBB (karakterlánc,HOSSZ(B13)- SUM(HOSSZ(karakterlánc)-HOSSZ(SUBSTITUTE(karakterlánc, {0,1,2,3,4,5,6,7,8,9}, ""))))
A fenti esetet példaként használva használja a következő képletet:
=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}, ""))))
nyomja meg belép gombot az első eredmény eléréséhez, majd húzza az automatikus kitöltési fogantyút a többi cellára a képlet kitöltéséhez.
9.2 Karakter hozzáadása a szövegek és számok közé a Flash Fill funkcióval
Ha az Excel 2013-as vagy újabb verzióit használja, a Flash töltés abban is segíthet, hogy karakter(ek)et adjon a szövegek és számok közé.
Az eredeti adatok mellé írja be azokat az új adatokat, amelyek a szövegek és a számok közé a karakter(eke)t hozzáadták. Ezután nyomja meg az Enter billentyűt, hogy az első eredmény következő cellájára lépjen.
És nyomja meg Ctrl + E gombokat a Flash Fill engedélyezéséhez.
Kiterjesztés
Ez az oktatóanyag néhány olyan forgatókönyvet is felsorol a szöveg hozzáadásával kapcsolatban, amelyekkel találkozhatunk mindennapi életünkben vagy munkánkban.
Néha elválasztójeleket, például kötőjeleket kell hozzáadnia a telefonszámok vagy közösségi számok sorozatához, amint az alábbi képernyőképen látható. Ha több száz számot kell hozzáadni, akkor a rendszer megpróbálja kézi gépelni. Ebben a részben három trükköt mutat be ennek a feladatnak a gyors kezeléséhez.
10.1 Adjon hozzá kötőjeleket a számokhoz képlet segítségével
Íme két képlet, amellyel gyorsan kötőjeleket adhatunk a számokhoz. Ha kötőjelet szeretne hozzáadni a harmadik és hatodik szám után a B3 cellában, használja a következő képletet:
REPLACE funkció
=CSERE(REPLACE(B3,4,0;8,0;XNUMX"-"),XNUMX"-")
TEXT funkció
=SZÖVEG(B3,"???-???-??? ")
nyomja meg belép gombot az első eredmény eléréséhez, majd húzza az automatikus kitöltési fogantyút a többi cellára.
|
Figyelem: A fenti képletek argumentumait szükség szerint módosíthatja. Tételezzük fel hozzá "-" a második, negyedik és hatodik számjegy után a következő képleteket használhatja: =CSERE(CSERE(CSERE(B3,3,0;6,0;9,0"-"),XNUMX"-"),XNUMX"-") =SZÖVEG(B3,"??-??-??-??? ") |
10.2 Adjon hozzá kötőjeleket a számokhoz Cellák formázásával
Ha közvetlenül kötőjelet szeretne hozzáadni az eredeti cellákban lévő számokhoz, használja a Cellák formázása funkciót.
1. Válassza ki azokat a számokat, amelyekhez kötőjelet szeretne hozzáadni, majd nyomja meg a gombot Ctrl + 1 gombok a Cellák formázása párbeszéd.
2. Ban,-ben Cellák formázása párbeszédpanel alatt Szám lapot választani szokás tól Kategória szakasz.
3. Majd a jobb oldalon típus szakasz, típus ###-###-### kattintson a szövegmezőbe OK.
Most a kiválasztott számokhoz kötőjeleket adtunk.
10.3 Adjon kötőjelet a telefonszámhoz a Kutools for Excel szövegének hozzáadása segítségével
Ha van Kutools az Excel számára Excelbe telepítve, a Szöveg hozzáadása funkció is tehet egy szívességet.
1. Jelölje ki a kötőjelek hozzáadásához szükséges számokat tartalmazó cellákat, kattintson a gombra Kutools > szöveg > Szöveg hozzáadása.
2. Ban,-ben Szöveg hozzáadása párbeszédpanelen írja be a kötőjelet vagy más elválasztót szöveg szövegmezőbe, válassza a lehetőséget Adja meg opciót, majd írja be azokat a helyeket, amelyek után kötőjelet szeretne hozzáadni, és válassza el őket vesszővel az alábbi szövegmezőbe.
3. kettyenés Ok vagy Alkalmaz. Ezután a kiválasztott számokhoz kötőjeleket adtunk.
Ingyenes letöltés Szöveg hozzáadása 30 napos próbaverzióhoz.
Tételezzük fel, hogy létezik különböző hosszúságú számok listája, akkor kezdő nullákat szeretne hozzáadni, hogy azok ugyanolyan hosszúak legyenek, és jól nézzenek ki, mint az alábbi képernyőképen. Íme két módszer a feladat kezeléséhez.
11.1 Adjon hozzá vezető nullákat a cellákhoz a Cellák formázása funkcióval
Használhatja a Cellák formázása funkció egyéni formátum beállításához a cella hosszának rögzítéséhez kezdő nullák hozzáadásával.
1. Jelölje ki azokat a cellákat, amelyekhez a kezdő nullákat kíván hozzáadni, majd nyomja meg a gombot Ctrl + 1 gombokat az engedélyezéshez Cellák formázása párbeszéd.
2. Ban,-ben Cellák formázása párbeszédpanel alatt Szám lapot választani szokás tól Kategória szakasz.
3. Majd a jobb oldalon típus szakasz, típus 0000 a szövegmezőbe (a szám hosszának 4 naposra rögzítéséhez írja be a 0000-et, módosíthatja más hosszúságúra, például 5 naposra, írja be a 00000-et), kattintson OK.
Ezután a számokat hozzá kell adni a kezdő nullákhoz és azonos hosszúságban.
| Figyelem: ha az eredeti adat hossza meghaladja a beállított hosszt, akkor az eredeti adatot nullák nélkül jeleníti meg. |
11.2 Adjon hozzá vezető nullákat a cellákhoz a képlettel
Ha nem akarja megváltoztatni az eredeti adatokat, a képlet segítségével kezdő nullákat adhat hozzá más helyeken lévő számokhoz.
Három funkció segíthet Önnek.
Formula1 TEXT függvény
SZÖVEG(szám"00…")
Formula2 RIGHT funkció
RIGHT("00…"&szám,hossz)
Formula3 BASE függvény
ALAP(szám,10,hossz)
A képletben a nullák száma megegyezik a szám hosszával.
Itt 4 számjegyet állít be a számok hosszának, kérjük, használja az alábbi képleteket:
= SZÖVEG (B10, "0000")
=JOBBRA("0000"&B10,4)
=ALAP(B10,10,4;XNUMX;XNUMX)
nyomja meg belép gombot, és húzza lefelé az automatikus kitöltési kart.
| Figyelem: ha az eredeti adat hossza meghaladja a beállított hosszt, akkor az eredeti adatot nullák nélkül jeleníti meg. |
Ha záró nullákat szeretne hozzáadni a számokhoz, hogy fix hosszúságú legyen, feltételezve, hogy a B3:B7 cellában lévő számokhoz záró nullákat kell hozzáadni, hogy azok 5 számjegyűek legyenek, ahogy az alábbi képernyőképen látható, alkalmazhat egy képletet a feladat kezelésére. .
REPT funkció
szám&REPT("0",hossz-HOSSZ(szám))
A képletben a „szám” argumentum azt a számot vagy cellahivatkozást jelöli, amelyhez nullákat kíván hozzáadni, a „hosszúság” argumentum pedig a kívánt rögzített hosszúságot.
Kérjük, használja a következő képletet:
=B3&REPT("0",5-LEN(B3))
nyomja meg belép gombot, és húzza le az automatikus kitöltési fogantyút azokhoz a cellákhoz, amelyeknek szüksége van erre a képletre.
Ha adatokat szeretne exportálni vagy másolni egy munkalapról egy jegyzettömbre, akkor a szövegek rendezetlenül helyezkedhetnek el, mivel a cellaértékek különböző hosszúságúak, hogy az alábbi képernyőképen láthatóan jól nézzenek ki, záró szóközt kell hozzáadnia.
Ennek a feladatnak a megoldásához a REPT funkció tehet egy szívességet.
LEFT(szöveg & REPT(" ",max_hossz),max_hossz)
A képletben a "text" argumentum az a szöveg vagy cellahivatkozás, amelyhez szóközt kíván hozzáadni, a "max_length" argumentum pedig az a rögzített hosszúság, amellyel a szövegeket el szeretné készíteni, és amelynek hosszabbnak vagy egyenlőnek kell lennie, mint a a felhasznált szövegek maximális hossza.
Ha szóközt szeretne hozzáadni a B3:C6 cellában lévő szövegekhez, használja az alábbi képletet:
=BAL(B3 & REPT(" ",10),10)
nyomja meg belép gombot, majd húzza az automatikus kitöltési fogantyút minden olyan cellára, amelyre ez a képlet szükséges.
Ebben az esetben, mivel a használt szövegek közül a leghosszabb 9 karakterből áll, itt 10 karaktert használunk.
Néha idézőjeleket vagy zárójeleket kell hozzáadnia a szöveghez vagy a számokhoz a cellákban, amint az alábbi képernyőképen látható, kivéve az egyesével történő kézi gépelést, az Excel két módszere gyorsan megoldhatja.
14.1 Adjon idézőjeleket/zárójeleket a Cellák formázásával
Ha idézőjeleket szeretne írni az eredeti adatok szövegébe, az Excel Cellák formázása funkciója segíthet.
1. Jelölje ki azokat a cellákat, amelyekhez idézőjeleket szeretne hozzáadni, majd nyomja meg a gombot Ctrl + 1 gombok a Cellák formázása párbeszéd.
2. Ban,-ben Cellák formázása párbeszéd, alatt Szám lapot választani szokás a Kategória, majd lépjen a jobb oldali részbe a gépeléshez "" "" @ "" "" be a típus szövegdoboz. Kattintson a gombra OK.
A kijelölt cellák szövege idézőjelek közé került.
Ha zárójeleket szeretne hozzáadni a szöveghez, a Cellák formázása párbeszédpanel Típus szövegmezőjében használja a (@).
14.2 Idézőjelek/zárójelek hozzáadása képletekkel
A képletek arra is használhatók, hogy idézőjeleket adjunk a többi cellában lévő szöveghez.
& csatlakozó használata
""""&szöveg&""""
Or
karakter(34)&szöveg&karakter(34)
Ebben az esetben írja be a szöveget a B11-es cellába, használja a következő képleteket:
=""""&B11&""""
Or
=char(34)&B11&char(34)
nyomja meg belép gombot, és húzza le az automatikus kitöltési fogantyút a cellák kitöltéséhez.
Ha zárójeleket szeretne hozzáadni a szöveghez, használja a következő képletet: "("&szöveg&")".
14.3 Idézőjelek/zárójelek hozzáadása a VBA segítségével
Ha VBA-kódra van szüksége a feladat megoldásához, itt van egy VBA.
1. nyomja meg más + F11 gombok a Microsoft Visual Basic for Applications ablak.
2. Ezután a felugró ablakban kattintson a gombra betétlap > Modulok üres új modul beillesztéséhez.
3. Másolja ki és illessze be az alábbi VBA-kódot az új modulba.
VBA: Tegyen idézőjeleket a szövegek köré
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| Figyelem: a kódszkriptben, """" & Rng.Value & """" azt jelzi, hogy "" karaktert adjon a szöveg köré, ha zárójelet () szeretne hozzáadni a szöveghez, módosítsa "(" & Rng.Value & ")". |
4. nyomja meg F5 vagy kattintson futás gomb az ablakban a VBA kód aktiválásához. Megjelenik egy párbeszédpanel, ahol kiválaszthatja a cellákat az idézőjelek hozzáadásához.
5. kettyenés OK. Most idézőjelek kerültek a szövegek köré a kijelölt cellákban.
Néha előfordulhat, hogy az alábbi képernyőképen látható módon szöveget kell hozzáadnia a képlet eredményeihez, hogy más felhasználók jobban megértsék az adatokat. Ha szöveget szeretne beilleszteni a képletbe, használhatja a szöveget körülvevő idézőjeleket, és hozzáadhatja a & összekötőt a szöveg és a képlet kombinálásához.
1. eset Szöveg hozzáadása a képlet elé
"Ma "&SZÖVEG(TODAY(),"dddd, mmmm nn.")
A 2. eset után adjon hozzá szöveget képlet
SZÖVEG(NOW(),"ÓÓ:PP:PP")&" az aktuális idő."
3. eset szövegek hozzáadása a képlet két oldalához
"Ma értékesített "&data&" kg."
További Excel oktatóanyagok: |
|
Több munkafüzet/munkalap egyesítése egybe Szöveg-, szám- és dátumcellák felosztása (több oszlopra szétválasztás) Kombinálja több cella tartalmát adatvesztés nélkül az Excelben Hasonlítson össze két oszlopot az egyezések és különbségek tekintetében az Excelben |
A legjobb irodai termelékenységi eszközök
A Kutools for Excel megoldja a legtöbb problémát, és 80%-kal növeli a termelékenységet
- Szuper Formula Bár (könnyedén szerkeszthet több szöveget és képletet); Olvasás elrendezés (könnyen olvasható és szerkeszthető nagyszámú cella); Beillesztés a Szűrt tartományba...
- Cellák / sorok / oszlopok egyesítése és az adatok megőrzése; Osztott cellák tartalma; Kombinálja az ismétlődő sorokat és az Összeg / Átlagot... megakadályozza az ismétlődő cellákat; Hasonlítsa össze a tartományokat...
- Válassza a Másolat vagy az Egyedi lehetőséget Sorok; Válassza az Üres sorok lehetőséget (az összes cella üres); Super Find és Fuzzy Find sok munkafüzetben; Véletlenszerű kiválasztás ...
- Pontos másolás Több cella a képletreferencia megváltoztatása nélkül; Automatikus referenciák létrehozása több lapra; Helyezze be a golyókat, Jelölőnégyzetek és még sok más ...
- Kedvenc és gyorsan beszúrható képletek, Tartományok, diagramok és képek; Cellák titkosítása jelszóval; Levelezőlista létrehozása és e-maileket küldeni ...
- Kivonat szöveg, Szöveg hozzáadása, Eltávolítás pozíció szerint, Hely eltávolítása; Hozz létre és nyomtasson személyhívó részösszegeket; Konvertálás a cellatartalom és a megjegyzések között...
- Szuper szűrő (mentse el és alkalmazza a szűrősémákat más lapokra); Haladó rendezés hónap / hét / nap, gyakoriság és egyebek szerint; Speciális szűrő félkövér, dőlt betűvel ...
- Kombinálja a munkafüzeteket és a munkalapokat; Táblázatok egyesítése kulcsoszlopok alapján; Az adatok felosztása több lapra; Kötegelt konvertálás xls, xlsx és PDF...
- Pivot tábla csoportosítás hét száma, a hét napja és egyebek ... Mutassa a Feloldott, Zárt cellákat különböző színekkel; Jelölje ki azokat a cellákat, amelyeknek képlete / neve van...
- Füles szerkesztés és olvasás engedélyezése Wordben, Excelben és PowerPointban, Publisher, Access, Visio és Project.
- Több dokumentum megnyitása és létrehozása ugyanazon ablak új lapjain, mint új ablakokban.
- 50% -kal növeli a termelékenységet, és naponta több száz kattintással csökkenti az egér kattintását!
