Excel adaugă text și număr în poziția specificată a celulei
În Excel, adăugarea de texte sau numere la celule este o activitate foarte comună. Cum ar fi adăugarea de spațiu între nume, adăugarea de prefix sau sufix la celule, adăugarea de liniuțe la numerele sociale. Aici, în acest tutorial, listează aproape toate scenariile de adăugare în Excel și vă oferă metodele corespunzătoare.
notițe
În acest tutorial, creez câteva exemple pentru a explica metodele, puteți schimba referințele de care aveți nevoie atunci când utilizați codul sau formulele VBA de mai jos, sau puteți descărca direct mostrele pentru a încerca metode.
Faceți clic pentru a descărca fișierul eșantion
Această parte listează diferite metode de adăugare a caracterelor la începutul fiecărei celule, așa cum se arată mai jos:
1.1 Adăugați la începutul celulelor folosind formula
Aici puteți alege una dintre cele trei formule de mai jos:
Formula 1 Operatorul de concatenare „&”
Uniți mai multe texte împreună printr-un caracter „&”.
„text”&celulă
Funcția Formula2 CONCATENATE
Funcția CONCATENATE este folosită pentru a uni texte împreună.
CONCATENATE(„text”,celulă)
Funcția Formula3 CONCAT
Aceasta este o funcție nouă care apare doar în Excel 2019, Office 365 și Excel online.
CONCAT(„text”,celulă)
În formulele generice: celula este referința de celulă la textul la care doriți să adăugați prefix, iar textul este textul care a fost adăugat la celulă.
Folosind exemplul de mai sus, puteți folosi formulele din bara de formule după cum urmează:
„&”
=$E$3&B3 or ="Iphone"&B3
Funcția CONCATENATE
=CONCATENATE($E$3,B3) or =CONCATENATE(„Iphone”, B3)
Funcția CONCAT
=CONCAT($E$3,B3) or =CONCAT("Iphone",B3)
Anunturi Intrați pentru a obține rezultatul, apoi trageți mânerul de completare automată în jos pentru a adăuga text la fiecare celulă din intervalul B3:B6.
| Atenție: Textul trebuie inclus între ghilimele duble sau formula returnează o valoare de eroare #NUME?. Dacă utilizați referința de celulă la un text, vă rugăm să nu uitați să utilizați referința absolută, puteți apăsa tasta F4 pentru a schimba referința relativă la referința absolută. |
1.2 Adăugați la începutul celulelor prin Flash Fill
Dacă sunteți în Excel 2013 sau versiuni ulterioare, noua funcție puternică, Flash Completați, poate completa celulele pe baza celulelor de mai sus pe care le-ați introdus automat.
Alegeți o celulă lângă primele date originale, introduceți manual primele date cu textul prefixului, așa cum se arată mai jos:
Apoi, în celula de mai jos, continuați să tastați a doua date cu textul prefix, în timpul tastării, se va afișa o listă de culoare gri, apăsați Intrați pentru a permite Flash Fill să umple automat datele.
Dacă lista de previzualizare nu se generează, mergeți la fila Acasă, apoi faceți clic Umple > Flash Completați pentru a o rula manual.
1.3 Adăugați la începutul celulelor utilizând codul VBA
Dacă sunteți familiarizat cu codul VBA, aici introduceți un cod VBA pentru a adăuga text la începutul fiecărei celule dintr-un interval.
1. Selectați o serie de celule la care doriți să adăugați textul prefixului, apoi apăsați Alt + F11 tastele pentru a activa Microsoft Visual Basic pentru aplicații fereastră.
2. Apoi, în fereastra care apare, faceți clic Insera > Module pentru a introduce un nou modul gol.
3. Copiați și inserați sub codul VBA în noul modul.
VBA: Adăugați la începutul celulelor
Sub AppendToExistingOnLeft()
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = "Iphone" & c.Value
Next
End Sub| Atenție: în scriptul de cod, iphone este textul pe care doriți să-l adăugați la începutul celulelor selectate, modificați-l în funcție de nevoile dvs. |
4. presa F5 tasta sau faceți clic Alerga buton 
în fereastră pentru a activa codul VBA.
Acum toate celulele selectate au fost adăugate textul „Iphone” la început.
Cu excepția adăugării de text la începutul celulelor, adăugarea de text la sfârșitul celulelor este, de asemenea, comună. Aici luăm unitatea de adăugare la sfârșitul fiecărei celule pentru exemple, după cum se arată în captura de ecran de mai jos:
2.1 Adăugați la sfârșitul celulelor folosind formula
Aici puteți alege una dintre cele trei formule de mai jos:
Formula 1 Operatorul de concatenare „&”
Uniți mai multe texte împreună printr-un caracter „&”.
Celulă&„text”
Funcția Formula2 CONCATENATE
Funcția CONCATENATE este folosită pentru a uni texte împreună.
CONCATENATE(celulă,„text”)
Funcția Formula3 CONCAT
Aceasta este o funcție nouă care apare doar în Excel 2019, Office 365 și Excel online.
CONCAT(celulă,„text”)
În formulele generice: celula este referința de celulă la textul la care doriți să adăugați sufix, iar textul este textul care a fost adăugat la celulă.
Folosind exemplul de mai sus, puteți folosi formulele din bara de formule după cum urmează:
„&”
=B3&$E$3 or =B3&"Kg"
Funcția CONCATENATE
=CONCATENATE(B3, $E$3) or =CONCATENATE(B3, „Kg”)
Funcția CONCAT
=CONCAT(B3, $E$3) or =CONCAT(B3, „Kg”)
Anunturi Intrați pentru a obține rezultatul, apoi trageți mânerul de completare automată în jos pentru a adăuga text la sfârșitul fiecărei celule din intervalul B3:B6.
| Atenție: Textul trebuie inclus între ghilimele duble sau formula returnează o valoare de eroare #NUME?. Dacă utilizați referința de celulă la un text, vă rugăm să nu uitați să utilizați referința absolută, puteți apăsa tasta F4 pentru a schimba referința relativă la referința absolută. |
2.2 Adăugați la sfârșitul celulelor prin Flash Fill
Dacă sunteți în Excel 2013 sau versiuni ulterioare, noua funcție puternică, Flash Completați, poate completa celulele pe baza celulelor de mai sus pe care le-ați introdus automat.
Alegeți o celulă lângă primele date originale, introduceți manual primele date cu textul sufixului, așa cum se arată mai jos:
Apoi activați celula de sub primul rezultat, faceți clic Date > Flash Completați, toate celulele de mai jos, inclusiv celula activă, vor fi completate automat.
2.3 Adăugați la sfârșitul celulelor utilizând codul VBA
Iată un cod VBA care poate adăuga text la sfârșitul fiecărei celule dintr-un interval.
1. Selectați o serie de celule la care doriți să adăugați textul sufixului, apoi apăsați Alt + F11 tastele pentru a activa Microsoft Visual Basic pentru aplicații fereastră.
2. Apoi, în fereastra care apare, faceți clic Insera > Module pentru a introduce un nou modul gol.
3. Copiați și inserați sub codul VBA în noul modul.
VBA: Adăugați la sfârșitul celulelor
Sub AppendToExistingOnRight()
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = c.Value & "Kg"
Next
End Sub| Atenție: în scriptul de cod, Kg este textul pe care doriți să îl adăugați la sfârșitul celulelor selectate, modificați-l în funcție de nevoile dvs. |
4. presa F5 tasta sau faceți clic Alerga buton în fereastră pentru a activa codul VBA.
Acum textul „Kg” a fost adăugat la sfârșitul fiecărei celule.
Pentru cei mai mulți dintre voi, adăugarea de texte la începutul sau la sfârșitul celulelor este ușoară, dar adăugarea de texte la mijlocul celulelor poate este oarecum dificilă. Luați exemplu, așa cum se arată în captura de ecran de mai jos, adăugând un delimitator, cum ar fi două puncte: la sfârșitul celui de-al doilea caracter al fiecărei celule din intervalul B3:B7.
3.1 Adăugați după al n-lea caracter al șirului prin formulă
Iată patru formule oferite pentru a vă ocupa de această muncă, alegeți una pe care să o utilizați:
Formula1 Combinați funcțiile STÂNGA și DREAPTA
LEFT(celula, n) & „text” & RIGHT(celulă, LEN(celulă) -n)
Formula2 COMBINAȚI funcțiile CONCATENATE(CONCAT), RIGHT și LEN
CONCATENATE(STÂNGA(celulă, n), „text”, DREAPTA(celulă, LEN(celulă) -n))
Or
CONCAT(STÂNGA(celulă, n), „text”, DREAPTA(celulă, LEN(celulă) -n))
Funcția Formula3 REPLACE
REPLACE(celulă, n+1, 0, „text”)
În formulele generice: celulă este referința de celulă la textul pe care doriți să-l adăugați text la mijloc, iar textul este textul care a fost adăugat la celulă, n este numărul care definește după ce caracter din șirul de text doriți să îl adăugați adaugă text.
Pentru rezolvarea sarcinii menționate mai sus, vă rugăm să utilizați formulele ca acestea:
Combinați funcțiile STÂNGA și DREAPTA
=STÂNGA(B3, 2) & „:” și DREAPTA(B3, LEN(B3) -2) or =STÂNGA(B3, 2) și $E$3 și DREAPTA(B3, LEN(B3) -2)
COMBINAȚI funcțiile CONCATENATE(CONCAT), RIGHT și LEN
=CONCATENATE(STÂNGA(B3, 2), „:”, DREAPTA(B3, LEN(B3) -2)) or =CONCAT (STÂNGA(B3, 2), „:”, DREAPTA(B3, LEN(B3) -2))
Funcția REPLACE
=ÎNLOCUITĂ(B3, 2+1, 0, ":") or =ÎNLOCUITĂ(B3, 2+1, 0, $E$3)
Anunturi Intrați pentru a obține rezultatul, apoi trageți mânerul de completare automată în jos pentru a adăuga text după al doilea caracter al fiecărei celule din intervalul B3:B7.
| Atenție: Textul trebuie inclus între ghilimele duble sau formula returnează o valoare de eroare #NUME?. Dacă utilizați referința de celulă la un text, vă rugăm să nu uitați să utilizați referința absolută, puteți apăsa tasta F4 pentru a schimba referința relativă la referința absolută. |
3.2 Adăugați după al n-lea caracter al șirului prin Flash Fill
Dacă sunteți în Excel 2013 sau versiuni ulterioare, Flash Completați, poate completa celulele pe baza celulelor de mai sus pe care le-ați introdus automat.
Alegeți o celulă lângă primele date originale, introduceți manual primele date cu două puncte după al doilea caracter, așa cum se arată mai jos:
Apoi activați celula de sub primul rezultat, apăsați Ctrl + E taste pentru activare Flash Completați comandă, toate celulele de mai jos, inclusiv celula activă, au fost completate automat.
| Atenție: Dacă sunteți în sistemul MAC, apăsați tastele Command + E. |
3.3 Adăugați după al n-lea caracter al șirului folosind codul VBA
Un cod VBA poate face și această treabă.
1. presa Alt + F11 tastele pentru a activa Microsoft Visual Basic pentru aplicații fereastră.
2. Apoi, în fereastra care apare, faceți clic Insera > Module pentru a introduce un nou modul gol.
3. Copiați și inserați sub codul VBA în noul modul.
VBA: Adăugați la mijlocul celulelor
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| Atenție: în scriptul de cod, 2 este numărul care definește poziția după care doriți să adăugați text, : este textul la care doriți să adăugați, 3 este suma lui 2 și lungimea textului pe care îl adăugați. Luați un alt exemplu, pentru a adăuga „și” după primul caracter al șirului „AB”, scriptul ar trebui să fie Rng.Value = VBA.Left(Rng.Value, 1) & "și" & VBA.Mid(Valoare.Rng, 4, VBA.Len(Rng.Value) - 1) |
4. presa F5 tasta sau faceți clic Alerga buton în fereastră pentru a activa codul VBA. Va apărea o fereastră de dialog pentru selectarea celulelor pentru a adăuga text.
5. clic OK. Acum textul „:” a fost adăugat după al doilea caracter al fiecărei celule din intervalul B19:B23.
4. Adăugați text cu Kutools pentru Excel (adăugați caractere într-o anumită poziție după cum aveți nevoie)
Dacă te-ai săturat de formule și VBA, poți încerca un instrument puternic și la îndemână, Kutools pentru Excel, care vă poate ajuta cu ea Adăuga text caracteristică.
Această funcție de adăugare a textului poate:
- Adăugați text înainte de primul caracter
- Adăugați text după ultimul caracter
- Adăugați text în pozițiile specificate
- Extensie
Adăugați text înaintea literelor mari
Adăugați text înainte de litere mici
Adăugați text înainte de literele mari/minuscule
Adăugați text înaintea caracterelor numerice
Faceți clic pentru a obține acum o perioadă de încercare gratuită de 30 de zile.
Selectați celulele pe care doriți să le adăugați text, apoi aplicați Adăugare text făcând clic Kutools > Text > Adăuga text.
4.1 Adăugați text înainte de primul caracter sau după ultimul caracter
În Adăuga text dialog, tastați textul pe care doriți să-l adăugați în celulele din Text casetă text, apoi bifați Înainte de primul personaj în Poziţie secţiune.
Clic Aplică or Ok pentru a termina adăugarea.
Pentru a adăuga text după ultimul caracter, tastați textul pe care doriți să îl adăugați în celulele din Text casetă text, apoi bifați După ultimul personaj în Poziţie secţiune.
Clic Aplică or Ok pentru a termina adăugarea.
4.2 Adăugați text la pozițiile specificate
Uneori, poate doriți să adăugați text în mijlocul celulei sau să adăugați text în mai multe poziții ale celulei. Presupunând că adăugăm separatorul „-” după a treia și a șaptea cifră a numerelor sociale, așa cum se arată mai jos:
În Adăuga text dialog, introduceți textul în Text casetă text și bifați Specifica opțiunea, apoi tastați pozițiile folosind virgulă pentru a le separa în caseta de text și apoi faceți clic Ok or Aplică.
4.3 Extensia utilitarului Add Text
Pe lângă adăugarea textului în pozițiile normale (început, sfârșit și poziția specificată), Adăuga text utilitarul acceptă și adăugarea de text
- Înainte de majuscule
- Înainte de litere mici
- Înainte de literele mari/minuscule
- Înainte de caractere numerice
În Adăuga text dialog, introduceți textul în Text casetă text și bifați Adăugați numai la opțiunea, apoi faceți clic pe meniul derulant de mai jos pentru a alege operația după cum aveți nevoie.
Această parte oferă formule pentru adăugarea de text înainte sau după primul text specific apărut în Excel.
5.1 Adăugați text înainte de primul text specific
De exemplu, doriți să adăugați textul „(vânzare)” înainte de primul text „preț” în celulă, așa cum se arată în capturile de ecran de mai jos:
În Excel, funcțiile ÎNLOCUIRE și ÎNLOCUIRE pot rezolva acest job.
Funcția REPLACE
REPLACE(celula,FIND("găsiți_text",celulă),0,"adăugați_text")
Funcția SUBSTITUTE
SUBSTITUTE(celula,"find_text","inlocuire_text",1)
În acest caz, vă rugăm să utilizați formulele de mai sus ca acestea:
=ÎNLOCUITĂ(B4,GĂSĂ(„preț”, B4),0,”(vânzare)”)
Or
=ÎNLOCUIT(B4,"preț","(de vânzare)preț",1)
Argumentele formulei în acest caz sunt
Celula: B4,
Find_text: preț,
Add_text: (vânzare),
Replace_text:preț (de vânzare).
Anunturi Intrați pentru a obține primul rezultat de adăugare, apoi trageți mânerul de completare automată peste celulele care au nevoie de această formulă.
5.2 Adăugați text după primul text specific
De exemplu, doriți să adăugați simbolul valutar „$” după primele două puncte „:” în celulă, așa cum se arată în capturile de ecran de mai jos:
În Excel, funcțiile ÎNLOCUIRE și ÎNLOCUIRE pot rezolva acest job.
Funcția REPLACE
REPLACE(celula,FIND("găsiți_text",celulă),găsește_text_lungime,"înlocuiți_text")
Funcția SUBSTITUTE
SUBSTITUTE(celulă,"găsește_text","înlocuiește_text")
În acest caz, vă rugăm să utilizați formulele de mai sus ca acestea:
=ÎNLOCUITĂ(B12,GĂSĂ(":",B12),1,":$")
Or
=ÎNLOCUIT(B12,":",":$")
Argumentele formulei în acest caz sunt
Celula: B12,
Find_text: :,
Înlocuiește_text::$.
Găsiți_lungimea_textului:1
Anunturi Intrați pentru a obține primul rezultat de adăugare, apoi trageți mânerul de completare automată peste celulele care au nevoie de această formulă.
În această parte a tutorialului, introduce trei metode diferite (formulă, Găsiți și înlocuiți, VBA) pentru a vă ajuta să adăugați caractere înainte sau după fiecare cuvânt al celulei.
| Adăugați caractere înaintea fiecărui cuvânt |
 |
| Adăugați caractere după fiecare cuvânt |
 |
6.1 Adăugați caracter(e) înainte sau după fiecare cuvânt prin formulă
Funcția SUBSTITUTE poate fi folosită pentru a rezolva acest job, formulele generale ca acestea:
Adăugați înaintea fiecărui cuvânt
"caracter"&SUBSTITUTE(șir, " "," caracter")
Adaugă după fiecare cuvânt
SUBSTITUTE(șir," ","caracter ")&"caracter"
Presupunând că adăugați „+” înainte de fiecare cuvânt din lista B3:B6, vă rugăm să utilizați formula astfel:
="+"&SUBSTITUT(B3, " "," +")
Anunturi Intrați tasta pentru a obține primul rezultat, apoi trageți mânerul de umplere automată în jos pentru a obține toate rezultatele.
Presupunând că adăugăm ($) după fiecare cuvânt din lista B11:B14, vă rugăm să utilizați formula de mai jos:
=ÎNLOCUIT(B11," ","($) ")&"($)"
Anunturi Intrați tasta pentru a obține primul rezultat, apoi trageți mânerul de umplere automată în jos pentru a obține toate rezultatele.
6.2 Adăugați caracter(e) înainte sau după fiecare cuvânt prin Căutare și înlocuire
Dacă doriți să adăugați caractere înaintea fiecărui cuvânt, cu excepția primului, sau să adăugați caractere după fiecare cuvânt, excluzând ultimul, așa cum se arată în captura de ecran de mai jos, Excel încorporat Găsiți și înlocuiți utilitatea poate face față acestei sarcini.
Adăugați înaintea fiecărui cuvânt, excluzând primul
Adăugați după fiecare cuvânt, excluzând ultimul
1. Selectați celulele pe care doriți să le adăugați caractere înainte sau după fiecare cuvânt, apoi apăsați Ctrl + H tastele pentru a activa Găsiți și înlocuiți dialog.
2. Introduceți un spațiu în Gaseste ce caseta text, apoi tastați noile caractere cu spațiu Înlocui cu caseta de text și, în sfârșit, faceți clic pe Înlocuiește-le pe toate butonul.
| Ziua Operației | General | Exemplu |
| Adăugați caractere înaintea fiecărui cuvânt | Adăugați caractere noi după un spațiu | Adăugați (sale) înainte de fiecare cuvânt |
| Ziua Operației | General | Exemplu |
| Adăugați caractere după fiecare cuvânt | Personaje noi au urmat cu un spațiu | Adăugați - după fiecare cuvânt |
Aici oferă două coduri VBA pentru adăugarea de caractere înainte sau după fiecare cuvânt, vă rugăm să rulați codul urmând pașii de mai jos.
1. presa Alt + F11 tastele pentru a activa Microsoft Visual Basic pentru aplicații fereastră.
2. În fereastra popping, faceți clic pe Insera > Module pentru a introduce un nou modul gol.
3. Copiați și inserați codul VBA de mai jos în modul.
Adăugați înainte de fiecare cuvânt
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 SubAdaugă după fiecare cuvânt
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. presa F5 pentru a rula codul (sau puteți face clic pe Alerga buton ), apare o casetă de dialog care vă solicită să selectați celulele continue pentru a funcționa, faceți clic OK.
5. În al doilea dialog pop-out, tastați caracterele pe care doriți să le adăugați, faceți clic OK.
Apoi caracterele vor fi adăugate la începutul sau la sfârșitul fiecărui cuvânt.
Uneori, poate doriți să adăugați text între fiecare caracter. Aici, în această parte, listează două scenarii, unul este să adăugați text între fiecare caracter, altul este să adăugați spațiu între fiecare număr.
7.1 Adăugați text între fiecare caracter/cifră cu funcția definită de utilizator
Pentru a adăuga caractere între fiecare caracter dintr-un șir, funcția definită de utilizator vă poate ajuta.
În acest caz, adăugăm un spațiu între fiecare caracter.
1. presa Alt + F11 tastele pentru a activa Microsoft Visual Basic pentru aplicații fereastră.
2. În fereastra popping, faceți clic pe Insera > Module pentru a introduce un nou modul gol.
3. Copiați și inserați codul VBA de mai jos în modul.
Adăugați text între caractere
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. Salvați codul și închideți fereastra pentru a reveni la foaia de lucru, apoi tastați formula de mai jos într-o celulă în care doriți să plasați rezultatul adăugat.
=AdaugăText(B3)
B3 este celula care conține șirul pe care doriți să-l adăugați spațiu între caractere.
5. presa Intrați tasta pentru a obține rezultatul, trageți mânerul de umplere automată peste celule pentru a obține alte rezultate după cum aveți nevoie.
|
Atenție: 1） În codul VBA, puteți modifica "" la alte texte după cum aveți nevoie, presupunând că îl schimbați în "-" a adăuga – între caractere. 2） Codul va adăuga noul text după fiecare caracter. Puteți elimina ultimul text adăugat dacă nu aveți nevoie utilizând formula de mai jos. Pentru mai multe detalii despre formulă, vă rugăm să vizitați: Cum să eliminați primul sau ultimul N caractere dintr-o celulă sau șir în Excel? LEFT(celula,LEN(celula)-lungime_text) |
7.2 Adăugați spațiu între fiecare număr cu formulă
Dacă doriți să adăugați spațiu între cifrele unui șir care este o valoare numerică, puteți utiliza funcția TEXT.
| Atenție: numerele de interval între care doriți să adăugați spațiu trebuie să fie de aceeași lungime, în caz contrar, unele rezultate pot fi incorecte. |
Presupunând că aici este o listă de numere de 8 cifre în intervalul B10:B12, vă rugăm să selectați o celulă care va plasa rezultatul, tastați formula de mai jos:
=TEXT(B10,"# # # # # # # #")
Anunturi Intrați tasta, apoi trageți mânerul de completare automată peste alte celule.
Dacă lungimea numerelor este 5, formula va fi modificată ca =TEXT(B10,"# # # # #").
Dacă doriți să adăugați caracter(e) între fiecare cuvânt, presupunând că adăugați spațiu între nume, nume, nume așa cum se arată mai jos, puteți utiliza o funcție definită de utilizator.
1. presa Alt + F11 tastele pentru a activa Microsoft Visual Basic pentru aplicații fereastră.
2. În fereastra popping, faceți clic pe Insera > Module pentru a introduce un nou modul gol.
3. Copiați și inserați codul VBA de mai jos în modul.
Adăugați caractere între cuvinte
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. Salvați codul și închideți fereastra pentru a reveni la foaia de lucru, apoi tastați formula de mai jos într-o celulă în care doriți să plasați rezultatul adăugat.
=Adăugați caractere (B3)
B3 este celula care conține șirul pe care doriți să îl adăugați spațiu între cuvinte.
5. presa Intrați tasta pentru a obține rezultatul, trageți mânerul de umplere automată peste celule pentru a obține alte rezultate după cum aveți nevoie.
| Atenție: în codul VBA, puteți modifica "" la alte texte după cum aveți nevoie, presupunând că îl schimbați în "-" a adăuga – între cuvinte. |
Pentru a adăuga caractere specifice între texte și numere, așa cum se arată în captura de ecran de mai jos, aici oferă două metode.
9.1 Adăugați caracter între texte și numere cu formula
Dacă textele sunt în fața numerelor, formula generică este
TRIM(ÎNLOCUITĂ(șir,MIN(GĂSĂ({1,2,3,4,5,6,7,8,9,0},șir&"1234567890")),0,"-"))
Folosind cazul de mai sus ca exemplu, vă rugăm să utilizați formula astfel:
=TRIM(REPLACE(B4,MIN(FIND({1,2,3,4,5,6,7,8,9,0},B4&"1234567890")),0,"-"))
Anunturi Intrați pentru a obține primul rezultat, apoi trageți mânerul de umplere automată peste alte celule pentru a completa această formulă.
Dacă numerele sunt în fața textelor, formula generică este
LEFT(șir,SUM(LEN(șir)-LEN(SUBSTITUTE(șir,{0,1,2,3,4,5,6,7,8,9},""))))&"."&DREAPTA (șir, LEN(B13)- SUM(LEN(șir)-LEN(SUBSTITUTE(șir, {0,1,2,3,4,5,6,7,8,9}, ""))))
Folosind cazul de mai sus ca exemplu, vă rugăm să utilizați formula astfel:
=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}, ""))))
Anunturi Intrați pentru a obține primul rezultat, apoi trageți mânerul de umplere automată peste alte celule pentru a completa această formulă.
9.2 Adăugați caracter între texte și numere cu Flash Fill
Dacă sunteți în Excel 2013 sau versiuni ulterioare, Flash Completați de asemenea, vă poate ajuta să adăugați caractere între texte și numere.
Lângă datele originale, tastați noile date la care au fost adăugate caracterele dintre texte și numere. Apoi apăsați tasta Enter pentru a merge la următoarea celulă a primului rezultat.
Și apăsați Ctrl + E taste pentru a activa Flash Fill.
Extensie
Acest tutorial enumeră, de asemenea, câteva scenarii despre adăugarea de text pe care le putem întâlni în viața de zi cu zi sau în muncă.
Uneori, trebuie să adăugați separatori, cum ar fi liniuțe, la o serie de numere de telefon sau numere sociale, așa cum se arată mai jos. Tastarea manuală va fi încercată dacă sunt necesare sute de numere care trebuie adăugate. În această parte, se prezintă trei moduri trucuri de a gestiona rapid această lucrare.
10.1 Adăugați liniuțe la numere după formulă
Iată două formule care pot adăuga rapid liniuțe la numere. Pentru a adăuga liniuțe după al treilea și al șaselea număr din celula B3, vă rugăm să utilizați formula după cum urmează:
Funcția REPLACE
=ÎNLOCUITĂ(ÎNLOCUITĂ(B3,4,0,"-"),8,0,"-")
Funcția TEXT
=TEXT(B3,"???-???-??? ")
Anunturi Intrați pentru a obține primul rezultat, apoi trageți mânerul de umplere automată peste alte celule.
|
Atenție: Puteți schimba argumentele formulelor de mai sus după cum aveți nevoie. Presupunând că adaugă "-" după a doua, a patra și a șasea cifră, puteți utiliza formulele ca: =ÎNLOCUIT(ÎNLOCUIT(ÎNLOCUIT(B3,3,0,"-"),6,0,"-"),9,0,"-") =TEXT(B3,"??-??-??-??? ") |
10.2 Adăugați liniuțe la numere după formatul celulelor
Pentru a adăuga direct liniuțe la numerele din celulele originale, puteți utiliza Celule de format caracteristică.
1. Selectați numerele la care doriți să adăugați liniuțe, apoi apăsați Ctrl + 1 tastele pentru a activa Celule de format dialog.
2. În Celule de format dialog, sub Număr fila, alegeți pachet personalizat de la Categorii secţiune.
3. Apoi în dreapta Tip secțiune, tastați ###-###-### în caseta de text, faceți clic pe OK.
Acum numerele selectate au fost adăugate liniuțe.
10.3 Adăugați liniuțe la numărul de telefon prin Adăugați text din Kutools pentru Excel
Dacă aveţi Kutools pentru Excel instalat în Excel, the Adăuga text caracteristica vă poate face, de asemenea, o favoare.
1. Selectați celulele care conțin numerele necesare pentru a fi adăugate liniuțe, faceți clic Kutools > Text > Adaugă text.
2. În Adăuga text dialog, introduceți liniuța sau alt separator Text casetă text, alegeți Specifica opțiunea, apoi tastați pozițiile după care doriți să adăugați liniuțe și separați-le prin virgule în caseta text de mai jos.
3. clic Ok sau Aplicați. Apoi numerele selectate au fost adăugate liniuțe.
Descărcare gratuită Adaugă text pentru o perioadă de încercare de 30 de zile.
Presupunând că există o listă de numere de lungimi diferite, doriți să adăugați zerouri de început pentru a le face să aibă aceeași lungime și să arate bine așa cum se arată în captura de ecran de mai jos. Iată două metode oferite pentru a vă ocupa de lucrare.
11.1 Adăugați zerouri de început la celule cu caracteristica Format Cells
Aveți posibilitatea să utilizați Celule de format caracteristică pentru a seta un format personalizat pentru a fixa lungimea celulei adăugând zerouri de început.
1. Selectați celulele pe care doriți să le adăugați zerouri inițiale, apoi apăsați Ctrl + 1 taste pentru activare Celule de format dialog.
2. În Celule de format dialog, sub Număr fila, alegeți pachet personalizat de la Categorii secţiune.
3. Apoi în dreapta Tip secțiune, tastați 0000 în caseta de text (pentru a fixa lungimea numărului la 4-stăpâni, tastați 0000, îl puteți schimba la altă lungime, cum ar fi 5-stăpâni, tastați 00000), faceți clic pe OK.
Apoi numerele au fost adăugate cu zerouri de început și de aceeași lungime.
| Atenție: dacă lungimea datelor originale depășește lungimea setată, va afișa datele originale fără zerouri înainte. |
11.2 Adăugați zerouri de început la celulele cu formulă
Dacă nu doriți să modificați datele originale, puteți utiliza formula pentru a adăuga zerouri de început la numerele din alte locații.
Iată trei funcții care vă pot ajuta.
Funcția Formula1 TEXT
TEXT(număr, „00…”)
Funcția Formula2 RIGHT
DREAPTA("00..."&număr, lungime)
Funcția Formula3 BASE
BAZĂ(număr, 10, lungime)
Numărul de zerouri din formulă este egal cu lungimea numărului.
Aici setați 4 cifre ca lungime a numerelor, vă rugăm să utilizați formulele de mai jos:
= TEXT (B10, "0000")
=DREAPTA ("0000"&B10,4)
=BAZĂ(B10,10,4)
Anunturi Intrați tasta și trageți în jos mânerul de umplere automată.
| Atenție: dacă lungimea datelor originale depășește lungimea setată, va afișa datele originale fără zerouri înainte. |
Dacă doriți să adăugați zerouri finale numerelor pentru a face o lungime fixă, presupunând că adăugați zerouri finale la numerele din celula B3:B7 pentru a le face în lungime de 5 cifre, așa cum se arată mai jos, puteți aplica o formulă pentru a face față sarcinii. .
Funcția REPT
număr&REPT ("0", lungime-LEN(număr))
În formulă, argumentul „număr” reprezintă numărul sau o referință de celulă la care doriți să adăugați zerouri finale, iar argumentul „lungime” este lungimea fixă pe care o doriți.
Vă rugăm să utilizați formula astfel:
=B3&REPT(„0”,5-LEN(B3))
Anunturi Intrați tasta și trageți mânerul de umplere automată în jos până la celulele care au nevoie de această formulă.
Dacă doriți să exportați sau să copiați date dintr-o foaie de lucru într-un bloc de note, textele pot fi plasate neîngrijit, deoarece valorile celulelor sunt în lungimi diferite, pentru a le face să arate bine, așa cum se arată în captura de ecran de mai jos, trebuie să adăugați spațiu de sfârșit.
Pentru rezolvarea acestei sarcini, funcția REPT vă poate face o favoare.
LEFT(text & REPT(" ",lungime_max), lungime_max)
În formulă, argumentul „text” este textul sau referința de celulă la care doriți să adăugați spațiu de sfârșit, iar argumentul „max_length” este lungimea fixă pe care doriți să le faceți textele, care trebuie să fie mai lungă sau egală cu lungimea maximă a textelor utilizate.
Pentru a adăuga un spațiu final la textele din celula B3:C6, vă rugăm să utilizați formula de mai jos:
=STÂNGA(B3 și REPT(" ",10),10)
Anunturi Intrați tasta, apoi trageți mânerul de umplere automată peste toate celulele necesare acestei formule.
În acest caz, deoarece cel mai lung dintre textul folosit este de 9 caractere, vom folosi 10 aici.
Uneori, trebuie să adăugați ghilimele sau paranteze în jurul textului sau numerelor din celule, așa cum se arată mai jos, în captura de ecran, cu excepția tastării manuale una câte una, aici sunt două metode în Excel care o pot rezolva rapid.
14.1 Adăugați ghilimele/paranteze în jurul formatului celulelor
Dacă doriți să adăugați ghilimele în jurul textului în datele originale, funcția Formatare celule din Excel vă poate ajuta.
1. Selectați celulele pe care doriți să le adăugați ghilimele, apăsați Ctrl + 1 tastele pentru a activa Celule de format dialog.
2. În Celule de format dialog, sub Număr fila, alegeți pachet personalizat din lista de Categorii, apoi mergeți la secțiunea din dreapta pentru a tasta "''" @ "''" în Tip casetă de text. Clic OK.
Textul din celulele selectate a fost adăugat cu ghilimele.
Dacă doriți să adăugați paranteze în jurul textului, în caseta text Tip din caseta de dialog Formatare celule, utilizați (@).
14.2 Adăugați ghilimele/paranteze în jurul formulelor
Formulele pot fi folosite și pentru a adăuga ghilimele textului din alte celule.
Utilizarea & conector
""""&text&""""
Or
char(34)&text&char(34)
În acest caz, text în celula B11, vă rugăm să utilizați formulele ca acestea:
=""""&B11&""""
Or
=car(34)&B11&car(34)
Anunturi Intrați tasta și trageți mânerul de umplere automată în jos pentru a umple celulele.
Dacă doriți să adăugați paranteze în jurul textului, utilizați formula ca "("&text&")".
14.3 Adăugați ghilimele/paranteze prin VBA
Dacă aveți nevoie de cod VBA pentru a rezolva această lucrare, iată un VBA.
1. presa Alt + F11 tastele pentru a activa Microsoft Visual Basic pentru aplicații fereastră.
2. Apoi, în fereastra care apare, faceți clic Insera > Module pentru a introduce un nou modul gol.
3. Copiați și inserați sub codul VBA în noul modul.
VBA: Adăugați ghilimele în jurul textelor
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| Atenție: în scriptul de cod, """" & Rng.Value & """" indică adăugarea „” în jurul textelor, dacă doriți să adăugați paranteze () în jurul textului, schimbați-l în „(” și Valoare Rng. & „)”. |
4. presa F5 tasta sau faceți clic Alerga buton în fereastră pentru a activa codul VBA. Va apărea o fereastră de dialog pentru selectarea celulelor pentru a adăuga ghilimele.
5. clic OK. Acum ghilimelele au fost adăugate în jurul textelor din celulele selectate.
Uneori, pentru a ajuta alți utilizatori să înțeleagă mai bine datele, poate fi necesar să adăugați text în rezultatele formulei, așa cum se arată mai jos. Pentru a include text în formulă, puteți folosi ghilimelele care înconjoară textul și puteți adăuga conectorul & pentru a combina textul și formula împreună.
Cazul 1 adaugă text înainte de formulă
„Astăzi este „&TEXT(TODAY(),”dddd, mmmm dd.”)
Cazul 2 adaugă text după formulă
TEXT(ACUM(),"HH:MM:SS")&" este ora curentă."
Cazul 3 adaugă texte pe două părți ale formulei
„Azi vândut „&date&” kg.”
Mai multe tutoriale Excel: |
|
Combinați mai multe cărți de lucru/fișe de lucru într-una singură Divizarea celulelor text, număr și dată (separați în mai multe coloane) Combinați conținutul mai multor celule fără a pierde date în Excel Comparați două coloane pentru potriviri și diferențe în Excel |
Cele mai bune instrumente de productivitate Office
Kutools pentru Excel vă rezolvă majoritatea problemelor și vă crește productivitatea cu 80%
- Super Formula Bar (editați cu ușurință mai multe linii de text și formulă); Layout de citire (citiți și editați cu ușurință un număr mare de celule); Lipiți la interval filtrat...
- Merge celule / rânduri / coloane și păstrarea datelor; Conținut de celule divizate; Combinați rânduri duplicate și sumă / medie... Prevenirea celulelor duplicate; Comparați gamele...
- Selectați Duplicat sau Unic Rânduri; Selectați Rânduri goale (toate celulele sunt goale); Super Find și Fuzzy Find în multe cărți de lucru; Selectare aleatorie ...
- Copie exactă Mai multe celule fără modificarea referinței formulelor; Creați automat referințe la foi multiple; Introduceți gloanțe, Casete de selectare și multe altele ...
- Formule favorite și inserare rapidă, Gama, Diagrame și Imagini; Criptați celulele cu parola; Creați o listă de corespondență și trimiteți e-mailuri ...
- Extrageți textul, Adăugați text, eliminați după poziție, Eliminați spațiul; Creați și imprimați subtotaluri de paginare; Convertiți conținutul dintre celule și comentarii...
- Super Filtru (salvați și aplicați scheme de filtrare altor foi); Sortare avansată după lună / săptămână / zi, frecvență și multe altele; Filtru special cu bold, italic ...
- Combinați cărți de lucru și foi de lucru; Merge Tables pe baza coloanelor cheie; Împărțiți datele în mai multe foi; Conversia în loturi xls, xlsx și PDF...
- Gruparea tabelului pivot după numărul săptămânii, ziua săptămânii și multe altele ... Afișați celulele deblocate, blocate prin diferite culori; Evidențiați celulele care au formulă / nume...
- Activați editarea și citirea cu file în Word, Excel, PowerPoint, Publisher, Access, Visio și Project.
- Deschideți și creați mai multe documente în filele noi ale aceleiași ferestre, mai degrabă decât în ferestrele noi.
- Vă crește productivitatea cu 50% și reduce sute de clicuri de mouse pentru dvs. în fiecare zi!
