Excel fügt Text und Zahl an einer bestimmten Position in die Zelle ein

In Excel ist das Hinzufügen von Texten oder Zahlen zu Zellen eine sehr häufige Aufgabe. Zum Beispiel das Einfügen von Leerzeichen zwischen Namen, das Hinzufügen von Präfixen oder Suffixen zu Zellen oder das Einfügen von Bindestrichen in Sozialversicherungsnummern. In diesem Tutorial werden nahezu alle denkbaren Szenarien für das Hinzufügen in Excel aufgelistet und die entsprechenden Methoden dafür bereitgestellt.

Hinweis

In diesem Tutorial erstelle ich einige Beispiele, um die Methoden zu erklären. Sie können die Referenzen nach Bedarf ändern, wenn Sie den folgenden VBA-Code oder die Formeln verwenden, oder Sie können die Beispiele herunterladen, um die Methoden direkt auszuprobieren.

Klicken Sie hier, um die Beispieldatei herunterzuladen

Dieser Teil listet verschiedene Methoden zum Hinzufügen von Zeichen am Anfang jeder Zelle auf, wie im folgenden Screenshot gezeigt:

1.1 Am Anfang von Zellen mit einer Formel hinzufügen

Hier können Sie eine der folgenden drei Formeln auswählen:

Formel1 Verkettungsoperator "&"

Verbinden Sie mehrere Texte durch das Et-Zeichen "&".

"Text"&Zelle

Formel2 VERKETTEN-Funktion

Die CONCATENATE-Funktion wird verwendet, um Texte zusammenzufügen.

VERKETTEN("Text",Zelle)

Formel3 CONCAT-Funktion

Dies ist eine neue Funktion, die nur in Excel 2019, Office 365 und Excel Online verfügbar ist.

VERKETTEN("text";Zelle)

In den generischen Formeln: Zelle ist die Zellreferenz auf den Text, dem Sie ein Präfix hinzufügen möchten, und Text ist der Text, der zur Zelle hinzugefügt wird.

Anhand des obigen Beispiels können Sie die Formeln in der Formel-Leiste wie folgt verwenden:

"&"

=$E$3&B3 oder ="Iphone"&B3

VERKETTEN-Funktion

=VERKETTEN($E$3;B3) oder =VERKETTEN("Iphone";B3)

KONKAT-Funktion

=VERKETTEN($E$3;B3) oder =VERKETTEN("Iphone";B3)

Drücken Sie Eingeben Taste, um das Ergebnis zu erhalten, dann den Ausfüllkursor nach unten ziehen, um Text zu jeder Zelle im Bereich B3:B6 hinzuzufügen.

1.2 Am Anfang von Zellen durch Flash-Auffüllen hinzufügen

Wenn Sie Excel 2013 oder neuere Versionen verwenden, kann die leistungsstarke neue Funktion Flash Fill die Zellen basierend auf den oben eingegebenen Zellen automatisch ausfüllen.

Wählen Sie eine Zelle neben den ersten Originaldaten aus und geben Sie die ersten Daten manuell mit dem Präfix-Text wie unten gezeigt ein:

Geben Sie dann in der Zelle darunter den zweiten Datensatz mit dem Präfix-Text weiter ein. Während der Eingabe wird eine graue Liste angezeigt, drücken Sie Eingeben Taste, um das automatische Ausfüllen der Daten durch Flash Fill zu ermöglichen.

Wenn die Vorschau-Liste nicht generiert wird, gehen Sie bitte zur Registerkarte Start, und klicken Sie dann auf Ausfüllen > Schnellausfüllen, um es manuell auszuführen.

1.3 Am Anfang von Zellen hinzufügen durch Verwendung von VBA-Code

Wenn Sie mit VBA-Code vertraut sind, wird hier ein VBA-Code vorgestellt, um Text am Anfang jeder Zelle in einem Bereich hinzuzufügen.

1. Wählen Sie einen Zellbereich aus, dem Sie den Präfixtext hinzufügen möchten, und drücken Sie dann die Tasten Alt + F11 , um das Fenster Microsoft Visual Basic for Applications zu öffnen.

2. Klicken Sie dann im erscheinenden Fenster auf Einfügen > Modul um ein leeres neues Modul einzufügen.

3. Kopieren Sie den folgenden VBA-Code und fügen Sie ihn in das neue Modul ein.

VBA: Am Anfang von Zellen hinzufügen

Sub AppendToExistingOnLeft()
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = "Iphone" & c.Value
Next
End Sub

4. Drücken Sie F5 Taste drücken oder klicken Ausführen Schaltfläche  im Fenster, um den VBA-Code zu aktivieren.

Jetzt wurde allen ausgewählten Zellen der Text "Iphone" am Anfang hinzugefügt.

Neben dem Hinzufügen von Text am Anfang von Zellen ist es auch üblich, Text am Ende von Zellen hinzuzufügen. Hier nehmen wir das Hinzufügen einer Einheit am Ende jeder Zelle als Beispiel, wie im folgenden Screenshot gezeigt:

2.1 Am Ende von Zellen mit einer Formel hinzufügen

Hier können Sie eine der folgenden drei Formeln auswählen:

Formel1 Verkettungsoperator "&"

Verbinden Sie mehrere Texte durch das Et-Zeichen "&".

Zelle&"Text"

Formel2 VERKETTEN-Funktion

Die CONCATENATE-Funktion wird verwendet, um Texte zusammenzufügen.

VERKETTEN(Zelle,"Text")

Formel3 CONCAT-Funktion

Dies ist eine neue Funktion, die nur in Excel 2019, Office 365 und Excel Online erscheint.

VERKETTEN(Zelle,"Text")

In den generischen Formeln: Zelle ist der Zellbezug auf den Text, dem Sie ein Suffix hinzufügen möchten, und Text ist der Text, der zur Zelle hinzugefügt wird.

Anhand des obigen Beispiels können Sie die Formeln in der Formel-Leiste wie folgt verwenden:

"&"

=B3&$E$3 oder =B3&"Kg"

VERKETTEN-Funktion

=VERKETTEN(B3; $E$3) oder =VERKETTEN(B3; "Kg")

CONCAT-Funktion

=VERKETTEN(B3; $E$3) oder =VERKETTEN(B3; "Kg")

Drücken Eingeben Taste, um das Ergebnis zu erhalten, dann den Autoausfüllkursor nach unten ziehen, um Text an das Ende jeder Zelle im Bereich B3:B6 hinzuzufügen.

2.2 Am Ende der Zellen durch Blitzfüllung hinzufügen

Wenn Sie Excel 2013 oder neuere Versionen verwenden, kann die leistungsstarke neue Funktion „Flash Fill“ die Zellen basierend auf den oben eingegebenen Zellen automatisch ausfüllen.

Wählen Sie eine Zelle neben den ersten Originaldaten aus und geben Sie die ersten Daten manuell mit dem Suffix-Text wie unten gezeigt ein:

Aktivieren Sie dann die Zelle unter dem ersten Ergebnis und klicken Sie auf Daten > Blitzfüllung, werden alle darunterliegenden Zellen einschließlich der aktiven Zelle automatisch ausgefüllt.

2.3 Am Ende von Zellen hinzufügen durch Verwendung von VBA-Code

Hier ist ein VBA-Code, der Text am Ende jeder Zelle in einem Bereich hinzufügen kann.

1. Wählen Sie einen Zellbereich aus, dem Sie den Suffix-Text hinzufügen möchten, und drücken Sie dann die Tasten Alt + F11 , um das Fenster Microsoft Visual Basic for Applications zu öffnen.

2. Klicken Sie dann im Popup-Fenster auf Einfügen > Modul um ein leeres neues Modul einzufügen.

3. Kopieren Sie den folgenden VBA-Code und fügen Sie ihn in das neue Modul ein.

VBA: Am Ende der Zellen hinzufügen

Sub AppendToExistingOnRight()
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = c.Value & "Kg"
Next
End Sub

4. Drücken Sie F5 Taste oder Klick Ausführen Schaltfläche  im Fenster, um den VBA-Code zu aktivieren.

Der Text "Kg" wurde nun am Ende jeder Zelle hinzugefügt.

Für die meisten von Ihnen ist es einfach, Texte am Anfang oder Ende von Zellen hinzuzufügen, aber das Hinzufügen von Texten in die Mitte der Zellen könnte etwas schwieriger sein. Zum Beispiel, wie im Screenshot unten gezeigt, ein Trennzeichen, wie Doppelpunkt : am Ende des zweiten Zeichens jeder Zelle im Bereich B3:B7 hinzuzufügen.

3.1 Hinzufügen nach dem n-ten Zeichen einer Zeichenfolge per Formel

Hier sind vier Formeln für Sie bereitgestellt, um diese Aufgabe zu bewältigen; wählen Sie eine aus, um sie zu verwenden:

Formel1 Kombination der LINKS- und RECHTS-Funktionen

LINKS(Zelle, n) & "Text" & RECHTS(Zelle, LÄNGE(Zelle) -n)

Formel2 KOMBINIEREN VERKETTEN (CONCAT), RECHTS und LÄNGE Funktionen

VERKETTEN(LINKS(Zelle, n), "Text", RECHTS(Zelle, LÄNGE(Zelle) -n))

Oder

VERKETTEN(LINKS(Zelle, n), "Text", RECHTS(Zelle, LÄNGE(Zelle) -n))

Formel3 ERSETZEN-Funktion

ERSETZE(Zelle, n+1, 0, "Text")

In den generischen Formeln: Zelle ist der Zellbezug auf den Text, in den Sie Text in der Mitte einfügen möchten, und Text ist der Text, der zur Zelle hinzugefügt werden soll. n ist die Zahl, die definiert, nach welchem Zeichen in der Zeichenfolge Sie Text hinzufügen möchten.

Um die oben genannte Aufgabe zu lösen, verwenden Sie bitte die folgenden Formeln:

Kombinieren Sie die Funktionen LINKS und RECHTS

=LINKS(B3; 2) & ":" & RECHTS(B3; LÄNGE(B3) -2) oder =LINKS(B3; 2) & $E$3 & RECHTS(B3; LÄNGE(B3) -2)

KOMBINIEREN SIE DIE FUNKTIONEN VERKETTEN (CONCAT), RECHTS und LÄNGE

=VERKETTEN(TEIL(B3; 1; 2); ":"; TEIL(B3; LÄNGE(B3) -1)) oder =VERKETTEN(TEIL(B3; 1; 2); ":"; TEIL(B3; LÄNGE(B3) -1))

ERSETZEN-Funktion

=ERSETZEN(B3; 2+1; 0; ":") oder =ERSETZEN(B3; 2+1; 0; $E$3)

Drücken Eingeben Taste, um das Ergebnis zu erhalten, dann den Autoausfüllkursor nach unten ziehen, um Text nach dem zweiten Zeichen jeder Zelle im Bereich B3:B7 hinzuzufügen.

3.2 Hinzufügen nach dem n-ten Zeichen einer Zeichenfolge durch Flash-Auffüllen

Wenn Sie Excel 2013 oder neuere Versionen verwenden, kann die Funktion ' Flash Fill' die Zellen basierend auf den oben eingegebenen Zellen automatisch ausfüllen.

Wählen Sie eine Zelle neben den ersten Originaldaten aus, und geben Sie die ersten Daten manuell ein, gefolgt von einem Doppelpunkt nach dem zweiten Zeichen, wie unten gezeigt:

Aktivieren Sie dann die Zelle unter dem ersten Ergebnis und drücken Sie Strg + E Tasten zum Aktivieren Blitzfüllung Befehl, alle darunterliegenden Zellen einschließlich der aktiven Zelle wurden automatisch ausgefüllt.

3.3 Hinzufügen nach dem n-ten Zeichen einer Zeichenfolge mithilfe von VBA-Code

Ein VBA-Code kann diese Aufgabe ebenfalls erledigen.

1. Drücken Sie die Tasten Alt + F11 , um das Microsoft Visual Basic for Applications-Fenster zu öffnen.

2. Klicken Sie dann im Popup-Fenster auf Einfügen > Modul um ein leeres neues Modul einzufügen.

3. Kopieren Sie den folgenden VBA-Code und fügen Sie ihn in das neue Modul ein.

VBA: In die Mitte von Zellen einfügen

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

4. Drücken Sie F5 Taste drücken oder klicken Ausführen Schaltfläche  im Fenster, um den VBA-Code zu aktivieren. Ein Dialogfenster öffnet sich zur Auswahl der Zellen, in die Text hinzugefügt werden soll.

5. Klicken Sie auf OK. Jetzt wurde der Text ":" nach dem zweiten Zeichen in jeder Zelle im Bereich B19:B23 hinzugefügt.

Wenn Sie von Formeln und VBA genug haben, können Sie ein leistungsstarkes und praktisches Tool ausprobieren: Kutools für Excel, was Ihnen mit seinen helfen kann Text hinzufügen Funktion.

Diese Funktion zum Hinzufügen von Text kann:

• Text vor dem ersten Zeichen hinzufügen
• Text nach dem letzten Zeichen hinzufügen
• Text an bestimmten Positionen hinzufügen
• Erweiterung
  Text vor Großbuchstaben hinzufügen
  Text vor Kleinbuchstaben hinzufügen
  Text vor Groß- und Kleinbuchstaben hinzufügen
  Text vor numerischen Zeichen hinzufügen

Wählen Sie die Zellen aus, denen Sie Text hinzufügen möchten, und wenden Sie die Funktion Text hinzufügen an, indem Sie darauf klicken. Kutools > Text > Text hinzufügen.

4.1 Text vor dem ersten Zeichen oder nach dem letzten Zeichen hinzufügen

In der Text hinzufügen Dialogfeld, geben Sie den Text ein, den Sie zu den Zellen hinzufügen möchten, in die Text Textfeld, dann überprüfen Vor dem ersten Zeichen im Position Abschnitt.

Klicken Anwenden oder Ok um das Hinzufügen abzuschließen.

Um Text nach dem letzten Zeichen hinzuzufügen, geben Sie den Text, den Sie zu den Zellen hinzufügen möchten, in die Text Textfeld, dann überprüfen Nach dem letzten Zeichen im Position Abschnitt.

Klicken Anwenden oder Ok um das Hinzufügen abzuschließen.

4.2 Text an bestimmter Position hinzufügen

Manchmal möchten Sie vielleicht Text in die Mitte einer Zelle einfügen oder Text an mehrere Positionen innerhalb der Zelle hinzufügen. Angenommen, Sie fügen den Trenner "-" nach der dritten und siebten Ziffer der Sozialversicherungsnummern ein, wie im folgenden Screenshot gezeigt:

In der Text hinzufügen Dialog, geben Sie den Text ein in Text Textfeld, und überprüfen Angeben Option, dann die Positionen mit Komma getrennt in das Textfeld eingeben und anschließend klicken Ok oder Anwenden.

4.3 Erweiterung des Text hinzufügen-Tools

Neben dem Hinzufügen von Text an normalen Positionen (Anfang, Ende und bestimmte Position) unterstützt das Hilfsprogramm 'Text hinzufügen' auch das Hinzufügen von Text.

• Vor Großbuchstaben
• Vor Kleinbuchstaben
• Vor Groß- und Kleinbuchstaben
• Vor Zahlen

In der Text hinzufügen Dialog, geben Sie den Text ein in Text Textfeld, und überprüfen Erweitert Option, dann klicken Sie auf das Dropdown-Menü unten, um die gewünschte Operation auszuwählen.

Abgesehen von der Funktion 'Text hinzufügen' bietet Kutools für Excel über 300 weitere nützliche Funktionen, die Ihnen bei verschiedenen Aufgaben in Excel helfen können. Laden Sie es jetzt kostenlos herunter.

Dieser Teil enthält Formeln zum Hinzufügen von Text vor oder nach dem ersten Vorkommen eines bestimmten Textes in Excel.

5.1 Text vor dem ersten spezifischen Text hinzufügen

Sie möchten beispielsweise den Text "(sale)" vor dem ersten Text "price" in der Zelle hinzufügen, wie in den folgenden Screenshots gezeigt:

In Excel können die Funktionen ERSETZEN und SUBSTITUTE diese Aufgabe lösen.

ERSETZEN-Funktion

ERSETZE(Zelle;FINDEN("find_text";Zelle);0;"add_text")

ERSETZEN-Funktion

ERSETZEN(Zelle,"find_text","replace_text",1)

In diesem Fall verwenden Sie bitte die oben genannten Formeln wie folgt:

=ERSETZEN(B4;FINDEN("price";B4);0;"(sale)")

Oder

=WECHSELN(B4,"Preis","(Verkauf)Preis",1)

Die Formelargumente in diesem Fall sind

Zelle: B4,

Suchen_nach: Preis,

Text hinzufügen: (sale),

Ersetzen Sie den Text: (Verkaufs-)Preis.

Drücken Eingeben Taste, um das erste Additionsergebnis zu erhalten, dann den Autoausfüllgriff über die Zellen ziehen, die diese Formel benötigen.

5.2 Text nach dem ersten spezifischen Text hinzufügen

Sie möchten beispielsweise das Währungssymbol „$“ nach dem ersten Doppelpunkt „:“ in der Zelle hinzufügen, wie in den folgenden Screenshots gezeigt:

In Excel können die Funktionen ERSETZEN und WECHSELN diese Aufgabe lösen.

ERSETZEN-Funktion

ERSETZE(Zelle;FINDEN("find_text";Zelle);find_text_länge;"replace_text")

ERSETZEN-Funktion

ERSETZEN(Zelle,"find_text","replace_text")

In diesem Fall verwenden Sie bitte die obigen Formeln wie folgt:

=ERSETZEN(B12;FINDEN(":";B12);1;":$")

Oder

=WECHSELN(B12,":",":$")

Die Formelargumente in diesem Fall sind

Zelle: B12,

Suchen_nach: :,

Text_ersetzen::$.

Finde_Textlänge:1

Drücken Eingeben Taste, um das erste Additionsergebnis zu erhalten, dann den Autoausfüllgriff über die Zellen ziehen, die diese Formel benötigen.

In diesem Teil des Tutorials werden drei verschiedene Methoden (Formel, Suchen und Ersetzen, VBA) vorgestellt, die Ihnen helfen, Zeichen vor oder nach jedem Wort in einer Zelle hinzuzufügen.

Zeichen vor jedem Wort hinzufügen

Zeichen nach jedem Wort hinzufügen

6.1 Zeichen vor oder nach jedem Wort per Formel hinzufügen

Die SUBSTITUTE-Funktion kann verwendet werden, um diese Aufgabe zu lösen. Die allgemeinen Formeln sind wie folgt:

Vor jedem Wort hinzufügen

"Zeichen"&SUBSTITUTE(Zeichenfolge, " "," Zeichen")

Nach jedem Wort hinzufügen

ERSETZEN(Text," ","Zeichen ")&"Zeichen"

Angenommen, Sie möchten ein "+" vor jedem Wort in der Liste B3:B6 hinzufügen, verwenden Sie bitte die folgende Formel:

="+"&SUBSTITUTE(B3, " "," +")

Drücken Eingeben Taste, um das erste Ergebnis zu erhalten, dann den automatischen Füllgriff nach unten ziehen, um alle Ergebnisse zu erhalten.

Wenn Sie nach jedem Wort in der Liste B11:B14 ein ($) hinzufügen möchten, verwenden Sie bitte die folgende Formel:

=WECHSELN(B11," ","($) ")&"($)"

Drücken Eingeben Taste, um das erste Ergebnis zu erhalten, dann den automatischen Füllgriff nach unten ziehen, um alle Ergebnisse zu erhalten.

6.2 Zeichen vor oder nach jedem Wort mit Suchen und Ersetzen hinzufügen

Wenn Sie Zeichen vor jedem Wort mit Ausnahme des ersten hinzufügen möchten oder Zeichen nach jedem Wort mit Ausnahme des letzten hinzufügen möchten, wie im folgenden Screenshot gezeigt, kann das integrierte Suchen und Ersetzen-Dienstprogramm von Excel diese Aufgabe bewältigen.

Vor jedem Wort hinzufügen, außer vor dem ersten

Nach jedem Wort hinzufügen, außer dem letzten

1. Wählen Sie die Zellen aus, denen Sie vor oder nach jedem Wort Zeichen hinzufügen möchten, und drücken Sie dann die Tasten Ctrl + H , um das Dialogfeld „Suchen und Ersetzen“ zu öffnen.

2. Geben Sie ein Leerzeichen in das Textfeld „Suchen nach“ ein, dann geben Sie die neuen Zeichen mit einem Leerzeichen im Textfeld „Ersetzen durch“ ein, und klicken Sie abschließend auf die Schaltfläche „Alle ersetzen“.

Hier sind zwei VBA-Codes zum Hinzufügen von Zeichen vor oder nach jedem Wort. Bitte führen Sie den Code aus, indem Sie die folgenden Schritte befolgen.

1. Drücken Sie die Tasten Alt + F11 , um das Fenster Microsoft Visual Basic for Applications zu öffnen.

2. Klicken Sie im erscheinenden Fenster auf Einfügen > Modul um ein neues leeres Modul einzufügen.

3. Kopieren Sie den folgenden VBA-Code und fügen Sie ihn in das Modul ein.

Vor jedem Wort hinzufügen

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 Sub

Nach jedem Wort hinzufügen

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 Sub

4. Drücken Sie F5 Taste zum Ausführen des Codes (oder Sie können klicken Ausführen Schaltfläche   ), ein Dialogfenster erscheint, in dem Sie aufgefordert werden, zusammenhängende Zellen auszuwählen, um fortzufahren. Klicken Sie OK.

5. Geben Sie im zweiten Dialogfenster die Zeichen ein, die Sie hinzufügen möchten, und klicken Sie auf OK.

Dann werden die Zeichen am Anfang oder Ende jedes Wortes hinzugefügt.

Manchmal möchten Sie möglicherweise Text zwischen jedem Zeichen hinzufügen. In diesem Abschnitt werden zwei Szenarien aufgeführt: eines besteht darin, Text zwischen jedem Zeichen hinzuzufügen, das andere, Leerzeichen zwischen jeder Zahl einzufügen.

7.1 Fügen Sie mit einer benutzerdefinierten Funktion Text zwischen jedes Zeichen/Ziffer ein

Um Zeichen zwischen jedem Zeichen in einer Zeichenfolge hinzuzufügen, kann die benutzerdefinierte Funktion Ihnen helfen.

In diesem Fall fügen wir ein Leerzeichen zwischen jedem Zeichen ein.

1. Drücken Sie die Tasten Alt + F11 , um das Fenster Microsoft Visual Basic for Applications zu öffnen.

2. Klicken Sie im Popup-Fenster auf Einfügen > Modul um ein neues leeres Modul einzufügen.

3. Kopieren Sie den folgenden VBA-Code und fügen Sie ihn in das Modul ein.

Text zwischen Zeichen hinzufügen

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 Function

4. Speichern Sie den Code und schließen Sie das Fenster, um zum Arbeitsblatt zurückzukehren. Geben Sie dann die folgende Formel in eine Zelle ein, in der Sie das Addierergebnis platzieren möchten.

=AddText(B3)

B3 ist die Zelle, die die Zeichenfolge enthält, zwischen deren Zeichen Sie Leerzeichen hinzufügen möchten.

5. Drücken Sie Eingeben Taste, um das Ergebnis zu erhalten, ziehen Sie den automatischen Ausfüllgriff über die Zellen, um weitere Ergebnisse nach Bedarf zu erhalten.

7.2 Fügen Sie mit einer Formel Leerzeichen zwischen jede Zahl ein

Wenn Sie zwischen den Ziffern einer Zeichenfolge, die ein numerischer Wert ist, Leerzeichen hinzufügen möchten, können Sie die TEXT-Funktion verwenden.

Angenommen, hier ist eine Liste von 8-stelligen Zahlen im Bereich B10:B12. Bitte wählen Sie eine Zelle aus, in der das Ergebnis platziert werden soll, und geben Sie die folgende Formel ein:

=TEXT(B10,"# # # # # # # #")

Drücken Eingeben Taste, dann den Ausfüllkursor über die anderen Zellen ziehen.

Wenn die Länge der Zahlen 5 beträgt, wird die Formel geändert zu =TEXT(B10,"# # # # #").

Wenn Sie Zeichen zwischen jedem Wort hinzufügen möchten, zum Beispiel ein Leerzeichen zwischen Vor-, Mittel- und Nachnamen wie im folgenden Screenshot gezeigt, können Sie eine benutzerdefinierte Funktion verwenden.

1. Drücken Sie die Tasten Alt + F11 , um das Fenster Microsoft Visual Basic for Applications zu öffnen.

2. Klicken Sie im erscheinenden Fenster auf Einfügen > Modul, um ein neues leeres Modul einzufügen.

3. Kopieren Sie den folgenden VBA-Code und fügen Sie ihn in das Modul ein.

Zeichen zwischen Wörtern hinzufügen

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 Function

4. Speichern Sie den Code und schließen Sie das Fenster, um zum Arbeitsblatt zurückzukehren. Geben Sie dann die folgende Formel in eine Zelle ein, in der Sie das Addierergebnis platzieren möchten.

=AddCharacters(B3)

B3 ist die Zelle, die die Zeichenfolge enthält, in der Sie Leerzeichen zwischen Wörtern hinzufügen möchten.

5. Drücken Sie Eingeben Taste, um das Ergebnis zu erhalten, ziehen Sie den automatischen Ausfüllgriff über die Zellen, um weitere Ergebnisse nach Bedarf zu erhalten.

Um bestimmte Zeichen zwischen Texten und Zahlen wie im folgenden Screenshot gezeigt hinzuzufügen, werden hier zwei Methoden vorgestellt.

9.1 Zeichen zwischen Texten und Zahlen mit Formel hinzufügen

Wenn die Texte vor den Zahlen stehen, lautet die allgemeine Formel

GLÄTTEN(ERSATZ(Zeichenfolge;MIN(FINDEN({1,2,3,4,5,6,7,8,9,0};Zeichenfolge&"1234567890"));0;"-"))

Am obigen Fall als Beispiel orientiert, verwenden Sie bitte die Formel wie folgt:

=GLÄTTEN(ERSETZEN(B4;MIN(FINDEN({1;2;3;4;5;6;7;8;9;0};B4&"1234567890"));0;"-"))

Drücken Eingeben Taste, um das erste Ergebnis zu erhalten, dann den automatischen Füllgriff über andere Zellen ziehen, um diese Formel auszufüllen.

Wenn die Zahlen am Anfang der Texte stehen, lautet die allgemeine Formel

LINKS(Text,SUMME(LÄNGE(Text)-LÄNGE(WECHSELN(Text,{0,1,2,3,4,5,6,7,8,9},""))))&"."&RECHTS(Text,LÄNGE(B13)- SUMME(LÄNGE(Text)-LÄNGE(WECHSELN(Text, {0,1,2,3,4,5,6,7,8,9}, ""))))

Am obigen Beispiel orientierend, verwenden Sie bitte die Formel wie folgt:

=LINKS(B13;SUMME(LÄNGE(B13)-LÄNGE(WECHSELN(B13;{0;1;2;3;4;5;6;7;8;9};""))))&"."&RECHTS(B13;LÄNGE(B13)- SUMME(LÄNGE(B13)-LÄNGE(WECHSELN(B13; {0;1;2;3;4;5;6;7;8;9}; ""))))

Drücken Eingeben Taste, um das erste Ergebnis zu erhalten, dann den Auto-Ausfüllgriff über andere Zellen ziehen, um diese Formel auszufüllen.

9.2 Zeichen zwischen Texten und Zahlen mit Blitzfüllung hinzufügen

Wenn Sie Excel 2013 oder eine spätere Version verwenden, kann Ihnen die Funktion Flash Fill ebenfalls helfen, Zeichen zwischen Texten und Zahlen einzufügen.

Neben den ursprünglichen Daten geben Sie die neuen Daten ein, denen Zeichen zwischen Texten und Zahlen hinzugefügt wurden. Drücken Sie dann die Eingabetaste, um zur nächsten Zelle des ersten Ergebnisses zu gelangen.

Und drücken Sie Strg + E Tasten zum Aktivieren der Flash-Auffüllung.

Erweiterung

Dieses Tutorial listet auch einige Szenarien zum Hinzufügen von Text auf, die wir im täglichen Leben oder bei der Arbeit möglicherweise antreffen.

Manchmal müssen Sie Trennzeichen wie Bindestriche in eine Reihe von Telefonnummern oder Sozialversicherungsnummern einfügen, wie im folgenden Screenshot gezeigt. Wenn Hunderte von Nummern hinzugefügt werden müssen, wird manuelles Tippen zur Herausforderung. In diesem Abschnitt werden drei clevere Methoden vorgestellt, um diese Aufgabe schnell zu erledigen.

10.1 Bindestriche zu Zahlen mit Formel hinzufügen

Hier sind zwei Formeln, die schnell Bindestriche zu Zahlen hinzufügen können. Um Bindestriche nach der dritten und sechsten Zahl in Zelle B3 hinzuzufügen, verwenden Sie bitte die folgenden Formeln:

ERSETZEN-Funktion

=ERSETZEN(ERSETZEN(B3;4;0;"-");8;0;"-")

TEXT-Funktion

=TEXT(B3,"???-???-??? ")

Drücken Eingeben Taste, um das erste Ergebnis zu erhalten, dann den Auto-Ausfüllgriff über andere Zellen ziehen.

10.2 Striche zu Zahlen hinzufügen durch Zellen formatieren

Um direkt Bindestriche zu den Zahlen in den ursprünglichen Zellen hinzuzufügen, können Sie die Funktion Format Cells verwenden.

1. Wählen Sie die Zahlen aus, zu denen Sie Bindestriche hinzufügen möchten, und drücken Sie dann die Tasten Ctrl + 1, um das Dialogfeld Zellen formatieren zu öffnen.

2. Wählen Sie im Dialogfeld Format Cells unter der Registerkarte Number die Option Custom im Abschnitt Category.

3. Dann im rechten Bereich Typ Abschnitt, Typ ###-###-### in das Textfeld ein, klicken Sie auf OK.

Jetzt wurden den ausgewählten Zahlen Bindestriche hinzugefügt.

10.3 Bindestriche zur Telefonnummer hinzufügen mit der Funktion 'Text hinzufügen' von Kutools für Excel

Wenn Sie Kutools für Excel in Excel installiert haben, kann Ihnen die Funktion Text hinzufügen ebenfalls helfen.

1. Wählen Sie die Zellen aus, die die Zahlen enthalten, zu denen Bindestriche hinzugefügt werden sollen, und klicken Sie auf Kutools > Text > Text hinzufügen.

2. In der Text hinzufügen Dialog, geben Sie den Bindestrich oder ein anderes Trennzeichen ein in Text Textfeld, auswählen Angeben Option, dann geben Sie die Positionen ein, nach denen Sie Bindestriche hinzufügen möchten, und trennen Sie sie durch Kommas im untenstehenden Textfeld.

3. Klicken Sie auf OK oder Anwenden. Dann wurden den ausgewählten Zahlen Bindestriche hinzugefügt.

Kostenloser Download von Add Text für eine 30-tägige Testversion.

Angenommen, es gibt eine Liste von Zahlen mit unterschiedlichen Längen, und Sie möchten führende Nullen hinzufügen, um sie auf die gleiche Länge zu bringen und ordentlich aussehen zu lassen, wie im folgenden Screenshot dargestellt. Hier sind zwei Methoden für Sie bereitgestellt, um diese Aufgabe zu bewältigen.

11.1 Führende Nullen zu Zellen mit der Funktion 'Zellen formatieren' hinzufügen

Sie können die Funktion Format Cells verwenden, um ein benutzerdefiniertes Format festzulegen und die Zellenlänge durch das Hinzufügen führender Nullen zu fixieren.

1. Wählen Sie die Zellen aus, denen Sie führende Nullen hinzufügen möchten, und drücken Sie dann die Tasten Ctrl + 1, um das Dialogfeld Zellen formatieren zu öffnen.

2. Wählen Sie im Dialogfeld Format Cells unter der Registerkarte Number in der Sektion Category die Option Custom.

3. Dann im rechten Bereich Typ Abschnitt, Typ 0000 in das Textfeld ein (um die Zahlenlänge auf 4 Stellen festzulegen, geben Sie 0000 ein, Sie können es auch auf eine andere Länge ändern, z. B. 5 Stellen, indem Sie 00000 eingeben) und klicken Sie OK.

Dann wurden den Zahlen führende Nullen hinzugefügt, und sie haben die gleiche Länge.

11.2 Führende Nullen mit Formel zu Zellen hinzufügen

Wenn Sie die ursprünglichen Daten nicht ändern möchten, können Sie eine Formel verwenden, um führende Nullen zu Zahlen an anderen Positionen hinzuzufügen.

Hier sind drei Funktionen, die Ihnen helfen können.

Formel1 TEXT-Funktion

TEXT(Zahl,"00…")

Formel2 RECHTS-Funktion

RECHTS("00…"&Zahl,Länge)

Formel3 BASIS-Funktion

BASIS(Zahl,10,Länge)

Die Anzahl der Nullen in der Formel entspricht der Länge der Zahl.

Hier legen Sie 4 Stellen als Länge der Zahlen fest. Bitte verwenden Sie die folgenden Formeln:

=TEXT(B10,"0000")

=RECHTS("0000"&B10;4)

=BASE(B10,10,4)

Drücken Eingeben Taste gedrückt halten und AutoAusfüllkursor nach unten ziehen.

Wenn Sie Zahlen nachgestellte Nullen hinzufügen möchten, um eine feste Länge zu erreichen, zum Beispiel den Zahlen in den Zellen B3:B7 nachgestellte Nullen hinzufügen, damit sie eine 5-stellige Länge erhalten, wie im folgenden Screenshot gezeigt, können Sie eine Formel anwenden, um diese Aufgabe zu erledigen.

REPT-Funktion

Zahl&WIEDERHOLEN("0",Länge-LÄNGE(Zahl))

In der Formel steht das Argument "Zahl" für die Zahl oder eine Zellreferenz, zu der Sie nachgestellte Nullen hinzufügen möchten, und das Argument "Länge" gibt die gewünschte feste Länge an.

Bitte verwenden Sie die Formel wie folgt:

=B3&WECHSELN("0",5-LÄNGE(B3))

Drücken Eingeben Taste gedrückt halten und den automatischen Ausfüllgriff nach unten in die Zellen ziehen, die diese Formel benötigen.

Wenn Sie Daten aus einem Arbeitsblatt in einen Notepad exportieren oder kopieren möchten, können die Texte aufgrund unterschiedlicher Zellenwerte unaufgeräumt erscheinen. Um sie wie im folgenden Screenshot gezeigt ordentlich aussehen zu lassen, müssen Sie nachgestellte Leerzeichen hinzufügen.

Um diese Aufgabe zu lösen, kann die REPT-Funktion Ihnen helfen.

LINKS(Text & WIEDERHOLEN(" ",max_länge),max_länge)

In der Formel ist das Argument "Text" der Text oder die Zellreferenz, zu der Sie ein nachgestelltes Leerzeichen hinzufügen möchten, und das Argument "max_length" ist die feste Länge, auf die Sie die Texte bringen möchten, die mindestens so lang sein muss wie die maximale Länge der verwendeten Texte.

Um nachgestellte Leerzeichen zu den Texten in den Zellen B3:C6 hinzuzufügen, verwenden Sie bitte die folgende Formel:

=LINKS(B3 & WIEDERHOLEN(" ",10),10)

Drücken Sie die Eingabetaste, dann ziehen Sie den Automatische-Ausfüllmarkierung über alle Zellen, die diese Formel benötigen.

In diesem Fall verwenden wir 10, da der längste verwendete Text 9 Zeichen hat.

Manchmal müssen Sie Anführungszeichen oder Klammern um Text oder Zahlen in Zellen setzen, wie im folgenden Screenshot gezeigt. Abgesehen vom manuellen Eintippen einzeln, gibt es zwei Methoden in Excel, die das schnell lösen können.

14.1 Fügen Sie durch Formatieren von Zellen Anführungszeichen/Klammern hinzu

Wenn Sie Anführungszeichen um Text in den Originaldaten hinzufügen möchten, kann Ihnen die Funktion Zellen formatieren in Excel helfen.

1. Wählen Sie die Zellen aus, denen Sie Anführungszeichen hinzufügen möchten, und drücken Sie die Tasten Ctrl + 1, um das Dialogfeld Zellen formatieren zu öffnen.

2. In der Zellen formatieren Dialog, unter Zahl Tab, auswählen Benutzerdefiniert aus der Liste von Kategorie, dann gehen Sie zum rechten Abschnitt, um zu tippen "''"@"''" in die Typ Textfeld. Klicken OK.

Der Text in den ausgewählten Zellen wurde mit Anführungszeichen versehen.

Wenn Sie Klammern um den Text hinzufügen möchten, verwenden Sie im Textfeld 'Typ' des Dialogfelds 'Zellen formatieren' (@).

14.2 Anführungszeichen/Klammern mit Formeln hinzufügen

Formeln können auch verwendet werden, um Anführungszeichen zu Text in anderen Zellen hinzuzufügen.

Verwendung des &-Verbinders

""""&text&""""

Oder

Zeichen(34)&Text&Zeichen(34)

In diesem Fall verwenden Sie für den Text in Zelle B11 bitte die folgenden Formeln:

=""""&B11&""""

Oder

=char(34)&B11&char(34)

Drücken Eingeben Taste gedrückt halten und AutoAusfüllkursor nach unten ziehen, um Zellen zu füllen.

Wenn Sie Klammern um einen Text hinzufügen möchten, verwenden Sie die Formel als "("&text&")".

14.3 Anführungszeichen/Klammern mit VBA hinzufügen

Wenn Sie VBA-Code benötigen, um diese Aufgabe zu lösen, hier ist ein VBA.

1. Drücken Sie die Tasten Alt + F11 , um das Fenster Microsoft Visual Basic for Applications zu öffnen.

2. Klicken Sie dann im erscheinenden Fenster auf Einfügen > Modul, um ein neues leeres Modul einzufügen.

3. Kopieren Sie den folgenden VBA-Code und fügen Sie ihn in das neue Modul ein.

VBA: Fügen Sie Anführungszeichen um Texte ein

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

4. Drücken Sie F5 Taste drücken oder klicken Ausführen Knopf  im Fenster, um den VBA-Code zu aktivieren. Ein Dialogfenster öffnet sich zur Auswahl der Zellen, denen Anführungszeichen hinzugefügt werden sollen.

5. Klicken Sie auf OK. Jetzt wurden um die Texte in den ausgewählten Zellen Anführungszeichen hinzugefügt.

Manchmal müssen Sie, um anderen Benutzern den besseren Verständnis von Daten zu ermöglichen, Text in die Formelergebnisse einfügen, wie im folgenden Screenshot gezeigt. Um Text in eine Formel einzubinden, können Sie Anführungszeichen um den Text setzen und den &-Verbinder verwenden, um den Text und die Formel zusammenzufügen.

Fall 1 Text vor Formel hinzufügen

"Heute ist "&TEXT(HEUTE(),"dddd, mmmm dd.")

Fall 2 Text nach Formel hinzufügen

TEXT(NOW(),"HH:MM:SS")&" ist die aktuelle Uhrzeit."

Fall 3: Texte an beiden Seiten der Formel hinzufügen

"Heute verkauft "&data&" kg."

---

Die besten Tools zur Büroproduktivität

Kutools für Excel löst die meisten Ihrer Probleme und steigert Ihre Produktivität um 80 %

• Super Formel-Leiste (F) (mehrere Zeilen Text und Formeln einfach bearbeiten); Lese-Layout (einfaches Lesen und Bearbeiten einer großen Anzahl von Zellen); Einfügen in gefilterte Bereiche...
• Zellen/Zeilen/Spalten zusammenführen und Daten behalten; Zellinhalt aufteilen; Doppelte Zeilen zusammenführen und Summe/Durchschnitt berechnen... Doppelte Zellen verhindern; Bereiche vergleichen...
• Doppelte oder eindeutige Zeilen auswählen; Leere Zeilen auswählen (alle Zellen sind leer); Super Suchen und unscharfes Suchen in vielen Arbeitsmappen; Zufällige Auswahl...
• Exakte Kopie mehrerer Zellen ohne Änderung der Formelreferenz; Automatisches Erstellen von Referenzen zu mehreren Blättern; Aufzählungszeichen, Kontrollkästchen und mehr einfügen...
• Formeln, Bereiche, Diagramme und Bilder favorisieren und schnell einfügen; Zellen mit Passwort verschlüsseln; Mailingliste erstellen und E-Mails senden...
• Text extrahieren, Text hinzufügen, Zeichen an einer bestimmten Position entfernen, Leerzeichen entfernen; Druckstatistik erstellen und drucken; Zwischen Zelleninhalt und Kommentaren konvertieren...
• Super Filter (Filter-Schemata speichern und auf andere Blätter anwenden); Erweiterte Sortierung nach Monat/Woche/Tag, Häufigkeit und mehr; Spezialfilter nach Fett, Kursiv...
• Arbeitsmappen und Arbeitsblätter konsolidieren; Tabellen basierend auf Schlüsselspalten zusammenführen; Daten auf mehrere Blätter aufteilen; Stapelkonvertierung von xls, xlsx und PDF...
• Gruppierung von PivotTables nach Kalenderwoche, Wochentag und mehr... Entsperre und gesperrte Zellen durch unterschiedliche Farben anzeigen; Zellen mit Formel/Name hervorheben...

Weiterlesen... Vollständige Funktionen 30-Tage-Testversion... Kaufen... 

---

Office Tab - bringt das Register-Interface ins Office und macht Ihre Arbeit viel einfacher

• Aktivieren Sie die Registerkarten-Bearbeitung und den Lese-Modus in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
• Öffnen und erstellen Sie mehrere Dokumente in neuen Tabs desselben Fensters, anstatt in neuen Fenstern.
• Steigert Ihre Produktivität um 50 % und reduziert Hunderte von Mausklicks täglich!

Weiterlesen... Vollständige Funktionen 30-Tage-Testversion... Kaufen...