Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Forumthread: Telefonnummer automatisch per VBA formatieren

Telefonnummer automatisch per VBA formatieren
26.09.2019 11:47:54
Lilli
Hallo Excel-Fans :)
ich bräuchte bitte Hilfe in Telefonnummer Formatierung.
Für die Rufnummer habe ich folgende Formatierung angewandt: 00 000 0000 0000
das funktioniert auch ganz nett, wenn die Zahl der Rufnummer immer gleich ist.
49 170 1234 1234
wenn die Nummer kürzer ist, sieht das schlecht aus.
Schön wäre, wenn ich die Nummer so eingebe: 4917012341234
dass beim Verlassen der Zelle folgendes erscheint:
+49 170 1234 1234
sollte die Rufnummer kürzer sein, wäre schön, wenn das Makro das erkennt und dementsprechend auch umwandelt.
+49 170 1234 123
Zurzeit habe ich für eine Zelle im Tabellenblatt folgendes Makro drin:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'Für Spalte "B3:B4"
If Not Intersect(Target, Range("AC6")) Is Nothing Then
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
End If
End Sub
um die Buchstaben in der Zelle AC6 groß zu machen.
Bei dem Makro wurde mir auch hier geholfen.
Ich hoffe, ich habe alles angegeben, was an Info benötigt wird.
Vielen Dank für die Hilfe im Voraus.
Viele Grüße,
Lilli :)
Anzeige

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Telefonnummer automatisch per VBA formatieren
26.09.2019 12:54:18
MCO
Hallo Lilli!
Probier das mal aus:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim numb As String
If Target = "" Then Exit Sub
Application.EnableEvents = False
numb = Replace(Target.Value, "+", "")
If Len(numb)  13 Then
MsgBox "Eingabe zu lang"
Else
Target.NumberFormat = "+00 000 0000 0000"
End If
Application.EnableEvents = True
End Sub
Gruß, MCO
Anzeige
AW: Telefonnummer automatisch per VBA formatieren
27.09.2019 12:48:08
Lilli
Hallo MCO,
vielen Dank für Dein Feedback und für Deine Hilfe.
wo darf ich den Code stellen?
in der Tabelle, wo ich die Funktion brauche, oder in ein extra Modul?
Viele Grüße, Lilli :)
AW: Telefonnummer automatisch per VBA formatieren
26.09.2019 12:57:21
Dieter
Hallo Lilli,
ich vermute, dass du die Funktion der bisherigen Worksheet_Change-Prozedur beibehalten willst und dass die Telefonnummern in Spalte B stehen sollen (sonst einfach nur "B" gegen den entsprechenden Buchstaben austauschen).
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rest As String
Dim zf As String
If Not Intersect(Target, Me.Columns("B")) Is Nothing Then
Application.EnableEvents = False
If Target.Cells.Count = 1 Then
If IsNumeric(Target) Then
If Len(Target) > 5 Then
rest = Right$(Target, Len(Target) - 5)
zf = ""
Do Until rest = ""
If Len(rest) > 4 Then
zf = zf & " " & Left$(rest, 4)
rest = Right$(rest, Len(rest) - 4)
Else
zf = zf & " " & rest
rest = ""
End If
Loop
Target = "+" & Left$(Target, 2) & " " & Mid$(Target, 3, 3) & " " & zf
ElseIf Len(Target) > 2 Then
Target = "+" & Left$(Target, 2) & " " & Right$(Target, Len(Target) - 2)
Else
Target = "'+" & Target
End If
End If
End If
Application.EnableEvents = True
Exit Sub
End If
If Not Intersect(Target, Range("AC6")) Is Nothing Then
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
End If
End Sub
https://www.herber.de/bbs/user/132216.xlsm
Viele Grüße
Dieter
Anzeige
AW: Telefonnummer automatisch per VBA formatieren
27.09.2019 12:51:56
Lilli
Hallo Dieter,
vielen Dank für Dein Feedback und für Deine Hilfe.
ich möchte es sehr gerne beibehalten. Allerdings brauche ich das nicht für die Spalte B. Das ist im Code auskommentiert. Ich brauche ich es nur für die Zelle AC6. Darf ich fragen was ich ändern soll, wenn ich das nur für die Zelle AC6 brauche?
vielen Dank für Deine Mühe im Voraus.
viele Grüße,
Lilli :)
Anzeige
AW: Telefonnummer automatisch per VBA formatieren
28.09.2019 15:31:59
Dieter
Hallo Lilli,
das Programm könnte dann so aussehen:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rest As String
Dim zf As String
If Target.Address(False, False) = "AC6" Then
Application.EnableEvents = False
If Target.Cells.Count = 1 Then
If IsNumeric(Target) Then
If Len(Target) > 5 Then
rest = Right$(Target, Len(Target) - 5)
zf = ""
Do Until rest = ""
If Len(rest) > 4 Then
zf = zf & " " & Left$(rest, 4)
rest = Right$(rest, Len(rest) - 4)
Else
zf = zf & " " & rest
rest = ""
End If
Loop
Target = "+" & Left$(Target, 2) & " " & Mid$(Target, 3, 3) & " " & zf
ElseIf Len(Target) > 2 Then
Target = "+" & Left$(Target, 2) & " " & Right$(Target, Len(Target) - 2)
Else
Target = "'+" & Target
End If
End If
End If
Application.EnableEvents = True
Exit Sub
End If
End Sub
https://www.herber.de/bbs/user/132266.xlsm
Viele Grüße
Dieter
Anzeige
Version Nummer 3
26.09.2019 13:02:49
Werner
Hallo Lilli,
meine Version:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TN As String
If Target.Count > 1 Then Exit Sub
If Target.Column  2 Then Exit Sub
If Target  "" Then
If IsNumeric(Target) Then
TN = "+" & Left(Target, 2) & " " & Mid(Target, 3, 3) & " " & Mid(Target, 6, 4) _
& " " & Mid(Target, 10, 9 ^ 9)
Application.EnableEvents = False
Target = TN
Application.EnableEvents = True
End If
End If
End Sub
Gruß Werner
Anzeige
AW: Version Nummer 3
27.09.2019 12:53:44
Lilli
Hallo Werner,
vielen Dank für Deine Mühe und für das Feedback.
ich werde es probieren und gebe auf jeden Fall noch Bescheid.
vielen Dank
viele Grüße, Lilli :)
leere Versprechungen. o.w.T.
02.10.2019 06:35:53
Werner
... und noch eine
26.09.2019 13:35:31
Rudi

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ERREXIT
Application.EnableEvents = False
If Target.Count = 1 Then
Select Case Target.Address(0, 0)
Case "AC6"
Target = UCase(Target)
Case "A6" 'Zelle Telefonnummer; anpassen
Target = TELNUM(Target)
End Select
End If
ERREXIT:
Application.EnableEvents = True
End Sub
Function TELNUM(vntNum)
Dim l As Integer
vntNum = Replace(vntNum, "+", "")
l = Len(vntNum)
vntNum = Left(vntNum & String(13, "0"), 13)
vntNum = Format(--vntNum, "00 000 0000 0000")
TELNUM = "+" & Left(vntNum, l + Int(l / 3) + (l > 9))
End Function

Gruß
Rudi
Anzeige
AW: ... und noch eine
27.09.2019 12:55:27
Lilli
Hallo Rudi,
vielen Dank für Deine Version und für Deine Mühe.
ich werde es probieren und werde es auf jeden Fall ein Feedback geben.
Viele Grüße,
Lilli :)
AW: Telefonnummer automatisch per VBA formatieren
27.09.2019 14:15:59
Daniel
Hi
noch ne andere Möglichkeit, wenn du die Telefonnummer immer als Zahl eingibst:
erstelle für die Spalte über die Bedingte Formatierung für jede möchliche Telefonnummernlänge ein eigenes Zahlenformat mit Formel als Regel in Spalte B:
Regel: =Länge(B1)=13 Zahlenformat: +00 000 0000 0000
Regel: =Länge(B1)=12 Zahlenformat: +00 000 0000 000
usw.
Gruß Daniel
Anzeige

Forumthreads zu verwandten Themen

Anzeige
Anzeige
Anzeige
Anzeige
Anzeige

Infobox / Tutorial

Telefonnummern in Excel automatisch formatieren mit VBA


Schritt-für-Schritt-Anleitung

Um Telefonnummern in Excel automatisch zu formatieren, kannst Du VBA (Visual Basic for Applications) verwenden. Hier ist eine einfache Schritt-für-Schritt-Anleitung:

  1. Öffne Excel und lade die Datei, in der Du die Telefonnummern formatieren möchtest.
  2. Drücke ALT + F11, um den VBA-Editor zu öffnen.
  3. Füge ein neues Modul hinzu:
    • Rechtsklick auf "VBAProject (DeineDatei.xlsx)".
    • Wähle "Einfügen" > "Modul".
  4. Kopiere den folgenden VBA-Code in das Modul:

    Private Sub Worksheet_Change(ByVal Target As Range)
       Dim numb As String
       If Target.Address(False, False) = "AC6" Then
           Application.EnableEvents = False
           numb = Replace(Target.Value, "+", "")
           If Len(numb) > 13 Then
               MsgBox "Eingabe zu lang"
           Else
               Target.NumberFormat = "+00 000 0000 0000"
               Target.Value = "+" & Left(numb, 2) & " " & Mid(numb, 3, 3) & " " & Mid(numb, 6, 4) & " " & Mid(numb, 10, 9 ^ 9)
           End If
           Application.EnableEvents = True
       End If
    End Sub
  5. Schließe den VBA-Editor und kehre zu Excel zurück.
  6. Gib eine Telefonnummer in die Zelle AC6 ein, z.B. 4917012341234. Beim Verlassen der Zelle wird die Nummer automatisch formatiert.

Häufige Fehler und Lösungen

  • Fehler: Telefonnummer wird nicht formatiert
    Lösung: Stelle sicher, dass der VBA-Code im richtigen Zielblatt (z.B. "Tabelle1") eingefügt wurde und dass Du in die korrekte Zelle (AC6) eingibst.

  • Fehler: Eingabe zu lang
    Lösung: Überprüfe, ob die eingegebene Telefonnummer mehr als 13 Ziffern enthält. Der Code ist so eingerichtet, dass er nur Nummern bis zu dieser Länge verarbeitet.

  • Fehler: Telefonnummer wird nicht als Zahl erkannt
    Lösung: Stelle sicher, dass die Zelle nicht als Text formatiert ist. Setze das Format der Zelle auf "Allgemein" oder "Zahl".


Alternative Methoden

Wenn Du die Telefonnummern nicht per VBA formatieren möchtest, kannst Du auch die bedingte Formatierung in Excel verwenden:

  1. Markiere die Zelle oder den Bereich, in dem Du die Telefonnummern eingeben möchtest.
  2. Gehe zu "Start" > "Bedingte Formatierung" > "Neue Regel".
  3. Wähle "Formel zur Ermittlung der zu formatierenden Zellen verwenden".
  4. Gib eine Formel ein, um die Länge der Telefonnummer zu überprüfen, z.B. =LÄNGE(A1)=13.
  5. Wähle das gewünschte Zahlenformat aus.

Diese Methode ist besonders nützlich, wenn Du viele Telefonnummern in einer Excel-Tabelle formatieren möchtest, ohne VBA zu verwenden.


Praktische Beispiele

  • Beispiel 1: Wenn Du die Telefonnummer 4917012341234 eingibst, wird sie in +49 170 1234 1234 umgewandelt.

  • Beispiel 2: Bei der Eingabe von 491701234123 wird das Ergebnis +49 170 1234 123 angezeigt.

Du kannst auch unterschiedliche Formate für nationale und internationale Telefonnummern verwenden, indem Du den VBA-Code entsprechend anpasst.


Tipps für Profis

  • Verwende Kommentare im VBA-Code, um den Code leichter verständlich zu machen.
  • Teste den Code gründlich, bevor Du ihn auf wichtige Daten anwendest, um sicherzustellen, dass er wie gewünscht funktioniert.
  • Sichere Deine Excel-Datei, bevor Du Änderungen an den VBA-Einstellungen vornimmst, um Datenverlust zu vermeiden.

FAQ: Häufige Fragen

1. Wo sollte ich den VBA-Code einfügen?
Du solltest den Code im Modul des spezifischen Arbeitsblattes einfügen, in dem Du die Telefonnummern formatieren möchtest.

2. Kann ich das Format der Telefonnummer ändern?
Ja, Du kannst das Format im VBA-Code anpassen, indem Du die NumberFormat-Eigenschaft änderst.

3. Funktioniert dies in allen Excel-Versionen?
Ja, der VBA-Code sollte in den meisten Excel-Versionen ab Excel 2007 funktionieren. Achte darauf, dass Makros in Excel aktiviert sind.

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige