Excel aggiunge testo e numero nella posizione specificata della cella
In Excel, l'aggiunta di testi o numeri alle celle è un lavoro molto comune. Come aggiungere spazio tra i nomi, aggiungere prefisso o suffisso alle celle, aggiungere trattini ai numeri sociali. Qui in questo tutorial, elenca quasi tutti gli scenari di aggiunta in Excel e fornisce i metodi corrispondenti per te.
Note:
In questo tutorial creo alcuni esempi per spiegare i metodi, puoi modificare i riferimenti di cui hai bisogno quando usi il codice o le formule VBA sotto, oppure puoi scaricare gli esempi per provare direttamente i metodi.
Fare clic per scaricare il file di esempio
Questa parte elenca diversi metodi per aggiungere caratteri all'inizio di ogni cella come mostrato nell'immagine seguente:
1.1 Aggiungi all'inizio delle celle usando la formula
Qui puoi scegliere una delle tre formule seguenti:
Formula1 Operatore di concatenazione "&"
Unisci più testi insieme da una e commerciale "&".
"testo"&cella
Formula2 funzione CONCATENA
La funzione CONCATENA viene utilizzata per unire i testi.
CONCATENATE("testo",cella)
Funzione CONCAT Formula3
Questa è una nuova funzione che appare solo in Excel 2019, Office 365 ed Excel online.
CONCAT("testo",cella)
Nelle formule generiche: cell è il riferimento di cella al testo a cui si desidera aggiungere il prefisso e text è il testo utilizzato per aggiungere alla cella.
Usando l'esempio sopra, puoi usare le formule nella barra della formula come queste:
"&"
=$E$3&B3 or ="Iphone"&B3
Funzione CONCATENATE
=CONCATENA($E$3,B3) or =CONCATENA("Iphone",B3)
Funzione CONCAT
=CONC.($E$3;B3) or =CONCAT("Iphone",B3)
Rassegna Stampa entrare per ottenere il risultato, quindi trascina il quadratino di riempimento automatico verso il basso per aggiungere testo a ciascuna cella dell'intervallo B3:B6.
| Attenzione: il testo deve essere racchiuso tra virgolette oppure la formula restituisce un valore di errore #NOME?. Se si utilizza il riferimento di cella a un testo, ricordarsi di utilizzare il riferimento assoluto, è possibile premere il tasto F4 per modificare il riferimento relativo in riferimento assoluto. |
1.2 Aggiungi all'inizio delle celle con Flash Fill
Se utilizzi Excel 2013 o versioni successive, la nuova potente funzionalità, Riempimento flash, può riempire le celle in base alle celle precedenti che hai inserito automaticamente.
Scegli una cella accanto ai primi dati originali, digita manualmente i primi dati con il testo del prefisso come mostrato di seguito:
Quindi nella cella sottostante, continua a digitare i secondi dati con il prefisso di testo, durante la digitazione verrà visualizzato un elenco in colore grigio, premere entrare per consentire a Flash Fill di riempire automaticamente i dati.
Se l'elenco di anteprima non viene generato, vai alla scheda Home e quindi fai clic Riempire > Riempimento flash per eseguirlo manualmente.
1.3 Aggiungi all'inizio delle celle usando il codice VBA
Se hai familiarità con il codice VBA, qui introduce un codice VBA per aggiungere testo all'inizio di ogni cella in un intervallo.
1. Seleziona un intervallo di celle a cui desideri aggiungere il testo del prefisso, quindi premi altro + F11 tasti per abilitare il Microsoft Visual Basic, Applications Edition finestra.
2. Quindi, nella finestra che si apre, fare clic su inserire > Moduli per inserire un nuovo modulo vuoto.
3. Copia e incolla sotto il codice VBA nel nuovo modulo.
VBA: aggiungi all'inizio delle celle
Sub AppendToExistingOnLeft()
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = "Iphone" & c.Value
Next
End Sub| Attenzione: nello script del codice, iPhone è il testo che vuoi aggiungere all'inizio delle celle selezionate, cambialo in base alle tue esigenze. |
4. Stampa F5 tasto o fare clic Correre pulsante 
nella finestra per attivare il codice VBA.
Ora a tutte le celle selezionate è stato aggiunto il testo "Iphone" all'inizio.
Ad eccezione dell'aggiunta di testo all'inizio delle celle, è comune anche l'aggiunta di testo alla fine delle celle. Qui prendiamo l'aggiunta di unità alla fine di ogni cella per esempi come mostrato nell'immagine seguente:
2.1 Aggiungi alla fine delle celle usando la formula
Qui puoi scegliere una delle tre formule seguenti:
Formula1 Operatore di concatenazione "&"
Unisci più testi insieme da una e commerciale "&".
Cella&"testo"
Formula2 funzione CONCATENA
La funzione CONCATENA viene utilizzata per unire i testi.
CONCATENATE(cella,"testo")
Funzione CONCAT Formula3
Questa è una nuova funzione che appare solo in Excel 2019, Office 365 ed Excel online.
CONCAT(cella,"testo")
Nelle formule generiche: cell è il riferimento di cella al testo a cui vuoi aggiungere il suffisso e text è il testo che è stato aggiunto alla cella.
Usando l'esempio sopra, puoi usare le formule nella barra della formula come queste:
"&"
=B3&$E$3 or =B3&"Kg"
Funzione CONCATENATE
=CONCATENA(B3, $E$3) or =CONCATENA(B3, "Kg")
Funzione CONCAT
=CONC.(B3; $E$3) or =CONCAT(B3, "Kg")
Rassegna Stampa entrare per ottenere il risultato, quindi trascina il quadratino di riempimento automatico verso il basso per aggiungere il testo alla fine di ogni cella dell'intervallo B3:B6.
| Attenzione: il testo deve essere racchiuso tra virgolette oppure la formula restituisce un valore di errore #NOME?. Se si utilizza il riferimento di cella a un testo, ricordarsi di utilizzare il riferimento assoluto, è possibile premere il tasto F4 per modificare il riferimento relativo in riferimento assoluto. |
2.2 Aggiungi alla fine delle celle con Flash Fill
Se utilizzi Excel 2013 o versioni successive, la nuova potente funzionalità, Riempimento flash, può riempire le celle in base alle celle precedenti che hai inserito automaticamente.
Scegli una cella accanto ai primi dati originali, digita manualmente i primi dati con il testo del suffisso come mostrato di seguito:
Quindi attiva la cella sotto il primo risultato, fai clic Dati > Riempimento flash, tutte le celle sottostanti, inclusa la cella attiva, verranno riempite automaticamente.
2.3 Aggiungi alla fine delle celle usando il codice VBA
Ecco un codice VBA in grado di aggiungere testo alla fine di ogni cella in un intervallo.
1. Seleziona un intervallo di celle a cui desideri aggiungere il testo del suffisso, quindi premi altro + F11 tasti per abilitare il Microsoft Visual Basic, Applications Edition finestra.
2. Quindi, nella finestra che si apre, fare clic su inserire > Moduli per inserire un nuovo modulo vuoto.
3. Copia e incolla sotto il codice VBA nel nuovo modulo.
VBA: aggiungi alla fine delle celle
Sub AppendToExistingOnRight()
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = c.Value & "Kg"
Next
End Sub| Attenzione: nello script del codice, Kg è il testo che vuoi aggiungere alla fine delle celle selezionate, cambialo in base alle tue esigenze. |
4. Stampa F5 tasto o fare clic Correre pulsante nella finestra per attivare il codice VBA.
Ora il testo "Kg" è stato aggiunto alla fine di ogni cella.
Per la maggior parte di voi, aggiungere testi all'inizio o alla fine delle celle è facile, ma aggiungere testi al centro delle celle forse è alquanto difficile. Prendi l'esempio, come mostrato nell'immagine sottostante, aggiungendo un delimitatore, come i due punti: alla fine del secondo carattere di ciascuna cella nell'intervallo B3:B7.
3.1 Aggiungi dopo l'ennesimo carattere di stringa per formula
Ecco quattro formule fornite per gestire questo lavoro, scegline una da utilizzare:
Formula1 Combina le funzioni SINISTRA e DESTRA
SINISTRA(cella, n) & "testo" & DESTRA(cella, LEN(cella) -n)
Formula2 COMBINA le funzioni CONCATENATE(CONCAT), RIGHT e LEN
CONCATENATE(LEFT(cella, n), "testo", DESTRA(cella, LEN(cella) -n))
Or
CONCAT(LEFT(cella, n), "testo", RIGHT(cella, LEN(cella) -n))
Formula3 funzione SOSTITUZIONE
REPLACE(cella, n+1, 0, "testo")
Nelle formule generiche: cell è il riferimento di cella al testo a cui vuoi aggiungere il testo al centro e text è il testo che è stato utilizzato per aggiungere alla cella, n è il numero che definisce dopo quale carattere nella stringa di testo vuoi Aggiungi testo.
Per risolvere il lavoro sopra menzionato, utilizzare le formule come queste:
Combina le funzioni SINISTRA e DESTRA
=SINISTRA(B3, 2) & ":" & DESTRA(B3, LUNGHEZZA(B3) -2) or =SINISTRA(B3, 2) & $E$3 & DESTRA(B3, LUNGHEZZA(B3) -2)
COMBINA le funzioni CONCATENATE(CONCAT), RIGHT e LEN
=CONCATENA(SINISTRA(B3, 2), ":", DESTRA(B3, LUNGHEZZA(B3) -2)) or =CONCAT (SINISTRA(B3, 2), ":", DESTRA(B3, LUNGHEZZA(B3) -2))
Funzione REPLACE
=SOSTITUIRE(B3, 2+1, 0, ":") or =SOSTITUIRE(B3, 2+1, 0, $E$3)
Rassegna Stampa entrare per ottenere il risultato, quindi trascina il quadratino di riempimento automatico verso il basso per aggiungere il testo dopo il secondo carattere di ciascuna cella dell'intervallo B3:B7.
| Attenzione: il testo deve essere racchiuso tra virgolette oppure la formula restituisce un valore di errore #NOME?. Se si utilizza il riferimento di cella a un testo, ricordarsi di utilizzare il riferimento assoluto, è possibile premere il tasto F4 per modificare il riferimento relativo in riferimento assoluto. |
3.2 Aggiungi dopo l'ennesimo carattere di stringa tramite Flash Fill
Se utilizzi Excel 2013 o versioni successive, il Riempimento flash, può riempire le celle in base alle celle precedenti che hai inserito automaticamente.
Scegli una cella accanto ai primi dati originali, digita manualmente i primi dati con due punti dopo il secondo carattere come mostrato di seguito:
Quindi attiva la cella sotto il primo risultato, premi Ctrl + E chiavi da attivare Riempimento flash comando, tutte le celle sottostanti inclusa la cella attiva sono state riempite automaticamente.
| Attenzione: Se utilizzi un sistema MAC, premi i tasti Comando + E. |
3.3 Aggiungi dopo l'ennesimo carattere di stringa usando il codice VBA
Anche un codice VBA può fare questo lavoro.
1. Stampa altro + F11 tasti per abilitare il Microsoft Visual Basic, Applications Edition finestra.
2. Quindi, nella finestra che si apre, fare clic su inserire > Moduli per inserire un nuovo modulo vuoto.
3. Copia e incolla sotto il codice VBA nel nuovo modulo.
VBA: aggiungi al centro delle celle
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| Attenzione: nello script del codice, 2 è il numero definisce la posizione dopo la quale si desidera aggiungere il testo, : è il testo a cui vuoi aggiungere, 3 è la somma di 2 e la lunghezza del testo che aggiungi. Prendi un'altra istanza, per aggiungere "e" dopo il primo carattere della stringa "AB", lo script dovrebbe essere Rng.Value = VBA.Left(Rng.Value, 1) & "ed" & VBA.Mid(Rng.Value, 4, VBA.Len(Rng.Valore) - 1) |
4. Stampa F5 tasto o fare clic Correre pulsante nella finestra per attivare il codice VBA. Viene visualizzata una finestra di dialogo per selezionare le celle per aggiungere testo.
5. Clic OK. Ora il testo ":" è stato aggiunto dopo il secondo carattere di ogni cella nell'intervallo B19:B23.
4. Aggiungi testo con Kutools per Excel (aggiungi caratteri alla posizione specifica di cui hai bisogno)
Se sei stanco di formule e VBA, puoi provare uno strumento potente e maneggevole, Kutools for Excel, che può aiutarti con il suo aggiungi testo caratteristica.
Questa funzione Aggiungi testo può:
- Aggiungi testo prima del primo carattere
- Aggiungi testo dopo l'ultimo carattere
- Aggiungi testo in posizioni specificate
- Estensione
Aggiungi il testo prima delle lettere maiuscole
Aggiungi il testo prima delle lettere minuscole
Aggiungi il testo prima delle lettere maiuscole/minuscole
Aggiungi il testo prima dei caratteri numerici
Fai clic per ottenere ora una prova gratuita di 30 giorni.
Seleziona le celle a cui vuoi aggiungere del testo, quindi applica Aggiungi testo facendo clic Kutools > Testo > aggiungi testo.
4.1 Aggiungere testo prima del primo carattere o dopo l'ultimo carattere
Nel aggiungi testo finestra di dialogo, digitare il testo che si desidera aggiungere alle celle nella Testo casella di testo, quindi selezionare Prima del primo personaggio nel Posizione .
Clicchi APPLICA or Ok per finire l'aggiunta.
Per aggiungere del testo dopo l'ultimo carattere, digita il testo che vuoi aggiungere alle celle in Testo casella di testo, quindi selezionare Dopo l'ultimo personaggio nel Posizione .
Clicchi APPLICA or Ok per finire l'aggiunta.
4.2 Aggiungi testo in una o più posizioni specificate
A volte, potresti voler aggiungere del testo al centro della cella o aggiungere del testo in diverse posizioni della cella. Supponendo di aggiungere il separatore "-" dopo la terza e la settima cifra dei numeri sociali come mostrato nell'immagine seguente:
Nel aggiungi testo finestra di dialogo, digitare il testo in Testo casella di testo e seleziona Specificare opzione, quindi digitare le posizioni utilizzando la virgola per separare nella casella di testo e quindi fare clic Ok or APPLICA.
4.3 Estensione dell'utilità Aggiungi testo
Oltre ad aggiungere testo nelle posizioni normali (inizio, fine e posizione specificata), il aggiungi testo l'utilità supporta anche l'aggiunta di testo
- Prima delle lettere maiuscole
- Prima delle lettere minuscole
- Prima delle lettere maiuscole/minuscole
- Prima dei caratteri numerici
Nel aggiungi testo finestra di dialogo, digitare il testo in Testo casella di testo e seleziona Aggiungi solo a opzione, quindi fare clic sul menu a discesa sottostante per scegliere l'operazione di cui hai bisogno.
Questa parte fornisce formule sull'aggiunta di testo prima o dopo il primo testo specifico apparso in Excel.
5.1 Aggiungere del testo prima del primo testo specifico
Ad esempio, vuoi aggiungere il testo "(vendita)" prima del primo testo "prezzo" nella cella come mostrato negli screenshot seguenti:
In Excel, le funzioni SOSTITUZIONE e SOSTITUZIONE possono risolvere questo lavoro.
Funzione REPLACE
SOSTITUISCI(cella,TROVA("trova_testo",cella),0,"aggiungi_testo")
Funzione SOSTITUISCI
SOSTITUZIONE(cella,"find_text","replace_text",1)
In questo caso, utilizzare le formule di cui sopra come queste:
=SOSTITUIRE(B4,TROVA("prezzo",B4),0,"(vendita)")
Or
=SOSTITUZIONE(B4,"prezzo","(vendita)prezzo",1)
Gli argomenti della formula in questo caso sono
Cella: B4,
Trova_testo: prezzo,
Add_text: (vendita),
Replace_text:(vendita)prezzo.
Rassegna Stampa entrare per ottenere il primo risultato di aggiunta, quindi trascina il quadratino di riempimento automatico sulle celle che necessitano di questa formula.
5.2 Aggiungi testo dopo il primo testo specifico
Ad esempio, vuoi aggiungere il simbolo di valuta "$" dopo i primi due punti ":" nella cella come mostrato nelle schermate seguenti:
In Excel, le funzioni SOSTITUZIONE e SOSTITUZIONE possono risolvere questo lavoro.
Funzione REPLACE
REPLACE(cell,FIND("find_text",cell),find_text_length,"replace_text")
Funzione SOSTITUISCI
SOSTITUZIONE(cella,"find_text","replace_text")
In questo caso, utilizzare le formule di cui sopra come queste:
=SOSTITUIRE(B12,TROVA(":",B12),1,:$")
Or
=SOSTITUZIONE(B12,":",":$")
Gli argomenti della formula in questo caso sono
Cella: B12,
Trova_testo: ::
Sostituisci_testo::$.
Trova_lunghezza_testo:1
Rassegna Stampa entrare per ottenere il primo risultato di aggiunta, quindi trascina il quadratino di riempimento automatico sulle celle che necessitano di questa formula.
In questa parte del tutorial, vengono introdotti tre diversi metodi (formula, Trova e sostituisci, VBA) per aiutarti ad aggiungere caratteri prima o dopo ogni parola di cella.
| Aggiungi caratteri prima di ogni parola |
 |
| Aggiungi caratteri dopo ogni parola |
 |
6.1 Aggiungi carattere(i) prima o dopo ogni parola per formula
La funzione SOSTITUZIONE può essere utilizzata per risolvere questo lavoro, le formule generali come queste:
Aggiungi prima di ogni parola
"carattere"&SOSTITUTO(stringa, " "," carattere")
Aggiungi dopo ogni parola
SUBSTITUTE(string," ","character ")&"character"
Supponendo di aggiungere "+" prima di ogni parola nell'elenco di B3:B6, utilizzare la formula come questa:
="+"&SOSTITUZIONE(B3, " "," +")
Rassegna Stampa entrare per ottenere il primo risultato, quindi trascina il quadratino di riempimento automatico verso il basso per ottenere tutti i risultati.
Supponendo di aggiungere ($) dopo ogni parola dell'elenco B11:B14, utilizzare la formula come di seguito:
=SOSTITUZIONE(B11," ","($) ")&"($)"
Rassegna Stampa entrare per ottenere il primo risultato, quindi trascina il quadratino di riempimento automatico verso il basso per ottenere tutti i risultati.
6.2 Aggiungi caratteri prima o dopo ogni parola con Trova e sostituisci
Se vuoi aggiungere caratteri prima di ogni parola esclusa la prima, o aggiungere caratteri dopo ogni parola esclusa l'ultima come mostrato nell'immagine sottostante, il built-in di Excel Trova e sostituisci l'utilità può gestire questo lavoro.
Aggiungi prima di ogni parola esclusa la prima
Aggiungi dopo ogni parola esclusa l'ultima
1. Seleziona le celle a cui vuoi aggiungere caratteri prima o dopo ogni parola, quindi premi Ctrl + H tasti per abilitare il Trova e sostituisci dialogo.
2. Digitare uno spazio in Trova quello che casella di testo, quindi digita i nuovi caratteri con spazio all'interno Sostituire con casella di testo e infine fare clic su Sostituisci tutto pulsante.
| Funzionamento | Generale | Esempio |
| Aggiungi caratteri prima di ogni parola | Aggiungi nuovi caratteri dopo uno spazio | Aggiungi (vendita) prima di ogni parola |
| Funzionamento | Generale | Esempio |
| Aggiungi caratteri dopo ogni parola | Nuovi caratteri seguiti da uno spazio | Aggiungi - dopo ogni parola |
Qui fornisce due codici VBA per aggiungere caratteri prima o dopo ogni parola, eseguire il codice seguendo i passaggi seguenti.
1. Stampa altro + F11 tasti per abilitare il Microsoft Visual Basic, Applications Edition finestra.
2. Nella finestra a comparsa, fare clic su inserire > Moduli per inserire un nuovo modulo vuoto.
3. Copia e incolla sotto il codice VBA nel modulo.
Aggiungi prima di ogni parola
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 SubAggiungi dopo ogni parola
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. Stampa F5 chiave per eseguire il codice (oppure è possibile fare clic su Correre pulsante ), viene visualizzata una finestra di dialogo in cui viene chiesto di selezionare le celle continue su cui lavorare, fare clic OK.
5. Nella seconda finestra di dialogo a comparsa, digitare i caratteri che si desidera aggiungere, fare clic OK.
Quindi i caratteri verranno aggiunti all'inizio o alla fine di ogni parola.
A volte, potresti voler aggiungere del testo tra ogni carattere. Qui in questa parte, elenca due scenari, uno è aggiungere testo tra ogni carattere, un altro è aggiungere spazio tra ogni numero.
7.1 Aggiungi testo tra ogni carattere/cifra con la funzione definita dall'utente
Per aggiungere caratteri tra ogni carattere in una stringa, la funzione definita dall'utente può aiutarti.
In questo caso, aggiungiamo uno spazio tra ogni carattere.
1. Stampa altro + F11 tasti per abilitare il Microsoft Visual Basic, Applications Edition finestra.
2. Nella finestra a comparsa, fare clic su inserire > Moduli per inserire un nuovo modulo vuoto.
3. Copia e incolla sotto il codice VBA nel modulo.
Aggiungi testo tra i caratteri
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 il codice e chiudi la finestra per tornare al foglio di lavoro, quindi digita la formula sottostante in una cella in cui desideri inserire il risultato dell'aggiunta.
=Aggiungi testo(B3)
B3 è la cella che contiene la stringa a cui vuoi aggiungere spazio tra i caratteri.
5. Stampa entrare per ottenere il risultato, trascina il quadratino di riempimento automatico sulle celle per ottenere altri risultati di cui hai bisogno.
|
Attenzione: 1） Nel codice VBA, puoi modificare il "" ad altri testi di cui hai bisogno, supponendo di cambiarlo in "-" aggiungere - tra i caratteri. 2） Il codice aggiungerà il nuovo testo dopo ogni carattere. Puoi rimuovere l'ultimo testo aggiunto se non è necessario utilizzando la formula sottostante. Per maggiori dettagli sulla formula, visitare: Come rimuovere il primo o l'ultimo N caratteri da una cella o una stringa in Excel? SINISTRA(cella,LEN(cella)-lunghezza_testo) |
7.2 Aggiungi spazio tra ogni numero con la formula
Se vuoi aggiungere spazio tra le cifre di una stringa che è un valore numerico, puoi usare la funzione TESTO.
| Attenzione: i numeri dell'intervallo tra i quali si desidera aggiungere spazio devono avere la stessa lunghezza, altrimenti alcuni risultati potrebbero non essere corretti. |
Supponendo che qui sia un elenco di numeri a 8 cifre nell'intervallo B10: B12, seleziona una cella che posizionerà il risultato, digita la formula seguente:
=TESTO(B10,"# # # # # # # #")
Rassegna Stampa entrare tasto, quindi trascina la maniglia di riempimento automatico su altre celle.
Se la lunghezza dei numeri è 5, la formula verrà modificata come =TESTO(B10,"# # # # #").
Se vuoi aggiungere caratteri tra ogni parola, supponendo di aggiungere spazio tra nome, secondo e cognome come mostrato nell'immagine sottostante, puoi utilizzare una funzione definita dall'utente.
1. Stampa altro + F11 tasti per abilitare il Microsoft Visual Basic, Applications Edition finestra.
2. Nella finestra a comparsa, fare clic su inserire > Moduli per inserire un nuovo modulo vuoto.
3. Copia e incolla sotto il codice VBA nel modulo.
Aggiungi caratteri tra le parole
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 il codice e chiudi la finestra per tornare al foglio di lavoro, quindi digita la formula sottostante in una cella in cui desideri inserire il risultato dell'aggiunta.
=Aggiungi caratteri(B3)
B3 è la cella che contiene la stringa a cui vuoi aggiungere spazio tra le parole.
5. Stampa entrare per ottenere il risultato, trascina il quadratino di riempimento automatico sulle celle per ottenere altri risultati di cui hai bisogno.
| Attenzione: nel codice VBA, puoi modificare il "" ad altri testi di cui hai bisogno, supponendo di cambiarlo in "-" aggiungere - tra le parole. |
Per aggiungere caratteri specifici tra testi e numeri come mostrato nell'immagine sottostante, qui vengono forniti due metodi.
9.1 Aggiungi carattere tra testi e numeri con formula
Se i testi sono davanti ai numeri, la formula generica lo è
TRIM(REPLACE(string,MIN(TROVA({1,2,3,4,5,6,7,8,9,0},string&"1234567890")),0,"-"))
Usando il caso sopra come esempio, si prega di utilizzare la formula come questa:
=TRIM(REPLACE(B4,MIN(FIND({1,2,3,4,5,6,7,8,9,0},B4&"1234567890")),0,"-"))
Rassegna Stampa entrare per ottenere il primo risultato, quindi trascina il quadratino di riempimento automatico su altre celle per riempire questa formula.
Se i numeri sono davanti ai testi, la formula generica lo è
LEFT(string,SUM(LEN(string)-LEN(SUBSTITUTE(string,{0,1,2,3,4,5,6,7,8,9},""))))&"."&RIGHT (stringa,LEN(B13)- SUM(LEN(stringa)-LEN(SOSTITUTO(stringa, {0,1,2,3,4,5,6,7,8,9}, ""))))
Usando il caso sopra come esempio, usa la formula come questa:
=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}, ""))))
Rassegna Stampa entrare per ottenere il primo risultato, quindi trascina il quadratino di riempimento automatico su altre celle per riempire questa formula.
9.2 Aggiungi carattere tra testi e numeri con Flash Fill
Se utilizzi Excel 2013 o versioni successive, il Riempimento flash può anche aiutarti ad aggiungere caratteri tra testi e numeri.
Accanto ai dati originali, digitare i nuovi dati a cui sono stati aggiunti i caratteri tra testi e numeri. Quindi premere il tasto Invio per passare alla cella successiva del primo risultato.
E premere Ctrl + E tasti per abilitare il riempimento flash.
Estensione
Questo tutorial elenca anche alcuni scenari sull'aggiunta di testo che potremmo incontrare nella nostra vita quotidiana o lavorativa.
A volte, è necessario aggiungere separatori come trattini a una serie di numeri di telefono o numeri sociali come mostrato nell'immagine sottostante. La digitazione manuale verrà tentata se sono necessarie centinaia di numeri da aggiungere. In questa parte, vengono introdotti tre trucchi per gestire rapidamente questo lavoro.
10.1 Aggiungi trattini ai numeri per formula
Ecco due formule che possono aggiungere rapidamente trattini ai numeri. Per aggiungere trattini dopo il terzo e il sesto numero nella cella B3, utilizzare la formula come segue:
Funzione REPLACE
=SOSTITUIRE(SOSTITUIRE(B3,4,0,"-"),8,0,"-")
Funzione TEXT
=TESTO(B3,"???-???-???")
Rassegna Stampa entrare per ottenere il primo risultato, quindi trascina il quadratino di riempimento automatico su altre celle.
|
Attenzione: Puoi modificare gli argomenti delle formule sopra di cui hai bisogno. Supponendo di aggiungere "-" dopo la seconda, quarta e sesta cifra, puoi utilizzare le formule come: =SOSTITUIRE(SOSTITUIRE(SOSTITUIRE(B3,3,0,"-"),6,0,"-"),9,0,"-") =TESTO(B3,"??-??-??-???") |
10.2 Aggiungi trattini ai numeri tramite Formato celle
Per aggiungere direttamente dei trattini ai numeri nelle celle originali, puoi usare il formato celle caratteristica.
1. Selezionare i numeri a cui si desidera aggiungere dei trattini, quindi premere Ctrl + 1 tasti per abilitare il formato celle dialogo.
2. Nel formato celle dialogo, sotto il Numero scheda, scegliere Custom dal Categoria .
3. Poi a destra Tipologia sezione, tipo ####-###-### nella casella di testo, fare clic su OK.
Ora i numeri selezionati sono stati aggiunti dei trattini.
10.3 Aggiungi trattini al numero di telefono aggiungendo testo di Kutools per Excel
Se si dispone di Kutools for Excel installato in Excel, il aggiungi testo caratteristica può anche farti un favore.
1. Seleziona le celle che contengono i numeri necessari per aggiungere i trattini, fai clic Kutools > Testo > Aggiungi testo.
2. Nel aggiungi testo finestra di dialogo, digitare il trattino o un altro separatore in Testo casella di testo, scegli Specificare opzione, quindi digita le posizioni in cui desideri aggiungere trattini e separale con virgole nella casella di testo sottostante.
3. Clic Ok o Applica. Quindi i numeri selezionati sono stati aggiunti dei trattini.
Download gratuito Aggiungi testo per 30 giorni di prova.
Supponendo che ci sia un elenco di numeri di lunghezze diverse, vuoi aggiungere zeri iniziali per renderli della stessa lunghezza e apparire ordinati come mostrato nell'immagine sottostante. Ecco due metodi forniti per gestire il lavoro.
11.1 Aggiungi zeri iniziali alle celle con la funzione Formato celle
È possibile utilizzare il formato celle funzione per impostare un formato personalizzato per correggere la lunghezza della cella aggiungendo zeri iniziali.
1. Seleziona le celle a cui vuoi aggiungere zeri iniziali, quindi premi Ctrl + 1 tasti per abilitare formato celle dialogo.
2. Nel formato celle dialogo, sotto il Numero scheda, scegliere Custom dal Categoria .
3. Poi a destra Tipologia sezione, tipo 0000 nella casella di testo (per fissare la lunghezza del numero a 4-dight, digitare 0000, è possibile cambiarlo in un'altra lunghezza, ad esempio 5-dight, digitare 00000), fare clic su OK.
Quindi i numeri sono stati aggiunti zeri iniziali e della stessa lunghezza.
| Attenzione: se la lunghezza dei dati originali supera la lunghezza impostata, visualizzerà i dati originali senza zeri iniziali. |
11.2 Aggiungi zeri iniziali alle celle con la formula
Se non si desidera modificare i dati originali, è possibile utilizzare la formula per aggiungere zeri iniziali ai numeri in altre posizioni.
Ecco tre funzioni che possono aiutarti.
Funzione TESTO Formula1
TESTO(numero,"00…")
Formula2 funzione DESTRA
RIGHT("00..."&numero,lunghezza)
Funzione BASE Formula3
BASE(numero,10,lunghezza)
Il numero di zeri nella formula è uguale alla lunghezza del numero.
Qui imposti 4 cifre come lunghezza dei numeri, utilizza le formule come di seguito:
= TESTO (B10, "0000")
=DESTRA("0000"&B10,4)
=BASE(B10,10,4;XNUMX;XNUMX)
Rassegna Stampa entrare premere il tasto e trascinare la maniglia di riempimento automatico verso il basso.
| Attenzione: se la lunghezza dei dati originali supera la lunghezza impostata, visualizzerà i dati originali senza zeri iniziali. |
Se vuoi aggiungere zeri finali ai numeri per creare una lunghezza fissa, supponendo di aggiungere zeri finali ai numeri nella cella B3: B7 per renderli con una lunghezza di 5 cifre come mostrato nell'immagine sottostante, puoi applicare una formula per gestire il lavoro .
funzione REPT
numero&REPT("0",lunghezza-LEN(numero))
Nella formula, l'argomento "numero" rappresenta il numero o un riferimento di cella a cui si desidera aggiungere zeri finali e l'argomento "lunghezza" è la lunghezza fissa desiderata.
Si prega di utilizzare la formula come questa:
=B3&REPT("0",5-LEN(B3))
Rassegna Stampa entrare premere il tasto e trascinare la maniglia di riempimento automatico verso il basso sulle celle che necessitano di questa formula.
Se desideri esportare o copiare dati da un foglio di lavoro a un blocco note, i testi possono essere posizionati in modo disordinato poiché i valori delle celle hanno lunghezze diverse, per farli sembrare ordinati come mostrato nell'immagine sottostante, è necessario aggiungere spazio finale.
Per risolvere questo lavoro, la funzione REPT può farti un favore.
SINISTRA(testo & REPT(" ",lunghezza_max),lunghezza_max)
Nella formula, l'argomento "testo" è il testo o il riferimento di cella a cui vuoi aggiungere lo spazio finale, e l'argomento "lunghezza_max" è la lunghezza fissa che vuoi realizzare per i testi, che deve essere maggiore o uguale al lunghezza massima dei testi utilizzati.
Per aggiungere spazio finale ai testi nella cella B3: C6, utilizzare la formula seguente:
=SINISTRA(B3 & RIPETI(" ",10),10)
Rassegna Stampa entrare chiave, quindi trascina il quadratino di riempimento automatico su tutte le celle necessarie per questa formula.
In questo caso, poiché il testo più lungo utilizzato è di 9 caratteri, qui utilizziamo 10.
A volte, è necessario aggiungere virgolette o parentesi attorno al testo o ai numeri nelle celle come mostrato nell'immagine sottostante, ad eccezione della digitazione manuale uno per uno, ecco due metodi in Excel che possono risolverlo rapidamente.
14.1 Aggiungi virgolette/parentesi attorno a Format Cells
Se desideri aggiungere virgolette attorno al testo nei dati originali, la funzione Formato celle in Excel può aiutarti.
1. Seleziona le celle a cui vuoi aggiungere le virgolette, premi Ctrl + 1 tasti per abilitare il formato celle dialogo.
2. Nel formato celle dialogo, sotto Numero scheda, scegliere Custom dalla lista di Categoria, quindi vai alla sezione destra per digitare "''" @ "''" nella Tipologia casella di testo. Clic OK.
Il testo nelle celle selezionate è stato aggiunto tra virgolette.
Se vuoi aggiungere parentesi attorno al testo, nella casella di testo Tipo della finestra di dialogo Formato celle, usa (@).
14.2 Aggiungere virgolette/parentesi attorno alle formule
Le formule possono anche essere utilizzate per aggiungere virgolette al testo in altre celle.
Utilizzo di & connettore
""""&testo&""""
Or
char(34)&testo&char(34)
In questo caso, testo nella cella B11, utilizza le formule come queste:
=""""&B11&""""
Or
=carattere(34)&B11&carattere(34)
Rassegna Stampa entrare premere il tasto e trascinare la maniglia di riempimento automatico verso il basso per riempire le celle.
Se vuoi aggiungere parentesi attorno al testo, usa la formula come "("&testo&")".
14.3 Aggiungi virgolette/parentesi attorno a VBA
Se hai bisogno del codice VBA per risolvere questo lavoro, ecco un VBA.
1. Stampa altro + F11 tasti per abilitare il Microsoft Visual Basic, Applications Edition finestra.
2. Quindi, nella finestra che si apre, fare clic su inserire > Moduli per inserire un nuovo modulo vuoto.
3. Copia e incolla sotto il codice VBA nel nuovo modulo.
VBA: aggiungi virgolette attorno ai testi
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| Attenzione: nello script del codice, """" & Rng.Valore & """" indica di aggiungere "" attorno al testo, se vuoi aggiungere parentesi () attorno al testo, cambialo in "(" & Rng.Valore & ")". |
4. Stampa F5 tasto o fare clic Correre pulsante nella finestra per attivare il codice VBA. Viene visualizzata una finestra di dialogo per selezionare le celle per aggiungere virgolette.
5. Clic OK. Ora le virgolette sono state aggiunte intorno ai testi nelle celle selezionate.
A volte, per aiutare altri utenti a comprendere meglio i dati, potrebbe essere necessario aggiungere del testo nei risultati della formula come mostrato nell'immagine sottostante. Per includere il testo nella formula, puoi utilizzare le virgolette che circondano il testo e aggiungere il connettore & per combinare il testo e la formula insieme.
Caso 1 aggiunge testo prima della formula
"Oggi è "&TEXT(TODAY(),"dddd, mmmm dd.")
Caso 2 aggiungi testo dopo formula
TEXT(NOW(),"HH:MM:SS")&" è l'ora corrente."
Il caso 3 aggiunge testi su due lati della formula
"Oggi venduto "&data&" kg."
Altri tutorial di Excel: |
|
Combina più cartelle di lavoro/fogli di lavoro in uno Dividi celle di testo, numero e data (separate in più colonne) Combina il contenuto di più celle senza perdere dati in Excel Confronta due colonne per corrispondenze e differenze in Excel |
I migliori strumenti per la produttività in ufficio
Kutools per Excel risolve la maggior parte dei tuoi problemi e aumenta la tua produttività dell'80%
- Bar Super Formula (modifica facilmente più righe di testo e formula); Layout di lettura (leggi e modifica facilmente un gran numero di celle); Incolla su intervallo filtrato...
- Unisci celle / righe / colonne e conservazione dei dati; Contenuto delle celle divise; Combina righe duplicate e somma / media... Impedisci celle duplicate; Confronta intervalli...
- Seleziona Duplica o Unico Righe; Seleziona Righe vuote (tutte le celle sono vuote); Super Find e Fuzzy Find in molte cartelle di lavoro; Selezione casuale ...
- Copia esatta Più celle senza modificare il riferimento della formula; Riferimenti di creazione automatica a più fogli; Inserisci punti elenco, Caselle di controllo e altro ...
- Formule preferite e di inserimento rapido, Intervalli, grafici e immagini; Crittografa celle con password; Crea mailing list e invia email ...
- Estrai testo, Aggiungi testo, Rimuovi per posizione, Rimuovi spazio; Creare e stampare totali parziali di paging; Converti contenuto e commenti tra celle...
- Super filtro (salva e applica schemi di filtri ad altri fogli); Ordinamento avanzato per mese / settimana / giorno, frequenza e altro; Filtro speciale in grassetto, corsivo ...
- Combina cartelle di lavoro e fogli di lavoro; Unisci tabelle in base a colonne chiave; Suddividi i dati in più fogli; Conversione in batch xls, xlsx e PDF...
- Raggruppamento tabelle pivot per numero della settimana, giorno della settimana e altro ... Mostra celle sbloccate e bloccate da diversi colori; Evidenzia le celle che hanno formula / nome...
- Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Apri e crea più documenti in nuove schede della stessa finestra, piuttosto che in nuove finestre.
- Aumenta la produttività del 50% e riduce ogni giorno centinaia di clic del mouse!
