Live-Forum - Die aktuellen Beiträge
Datum
Titel
29.03.2024 13:14:12
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1804to1808
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Zahlen weniger 4 Ziffern entfernen

Zahlen weniger 4 Ziffern entfernen
12.01.2021 20:57:23
Boerni
Hallo,
ich habe ein etwas eigenwilliges Problem.
Mittels VBA case select habe ich aus längeren Texten alle Buchstaben und Sonderzeichen (ausser Schrägstrich, da es sich um Telefonnummern handelt) entfernt. Die Zahlen befinden sich in einer Zelle. Jetzt müssen noch alle Zahlen mit weniger als 4 Ziffern entfernt werden, damit die Rufnummern am Ende übrig bleiben.
Kann man dies mit VBA oder einer Formel erledigen?
Vielen Dank
Boerni

16
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Was sollen wir ohne Beispiele tun?
12.01.2021 21:13:31
lupo1
AW: Was sollen wir ohne Beispiele tun?
12.01.2021 21:22:16
Boerni
Vereinfacht: in der Zelle sind die Zahlen (12 2 6589 0172123456 23 0171457895). Nun sollen alle Zahlen mit weniger als 4 Ziffern entfernt werden. In meinen Tabellen sind in den Zellen bis zuz 40 Zahlen.
AW: RegEx.Pattern "\d{4,} (owT)
12.01.2021 21:26:38
Fennek
AW: RegEx.Pattern "\d{4,} (owT)
12.01.2021 21:57:25
Boerni
Hallo Fennek,
ich habe deinen Tipp so ausprobiert:
Sub Ziffern_entfernen()
For Each cell In Selection
cell.value = regex.Pattern "\d{4,} (owT)"
Next
End Sub
Hier wird ein Syntaxfehler ausgegeben.
Bin ich vielleicht komplett am falschen Weg?
AW: RegEx.Pattern "\d{4,} (owT)
13.01.2021 09:50:03
Yal
Hallo Boerni,
(owT) steht für "ohne weitere Text". Bedeutet, alle was ich sagen möchte, ist bereit im Betreff.
Der Fehler ist, dass "\d{4,}" gemeint war.
Sub RegEx_testen()
Dim E
'version 1
Debug.Print bd_RegExtract("12 2 6589 0172123456 23 0171457895", "(\d{4,})", Trenner:=" ")
'version 2
For Each E In Split(bd_RegExtract("12 2 6589 0172123456 23 0171457895", "(\d{4,})"), ";")
Debug.Print E
Next
End Sub
Public Function bd_RegExtract(Target, Optional Pattern = "[a-zA-Z]+", Optional Occurence,  _
Optional Trenner = ";") As String
'unter Verweis (early binding) auf
'Name:         VBScript_RegExp_55
'Description:  Microsoft VBScript Regular Expressions 5.5
'FullPath:     C:\Windows\SysWOW64\vbscript.dll\3
Dim R As New RegExp
Dim strInput As String
Dim Ergs As Object
Dim i, Erg$
Select Case VarType(Target)
Case vbString: strInput = Target
Case Else: strInput = CStr(Target.Range("A1").Value)
End Select
R.IgnoreCase = True
R.MultiLine = True
R.Global = True
R.Pattern = Pattern
Set Ergs = R.Execute(strInput)
If Ergs.Count = 0 Then
bd_RegExtract = ""
Else
If IsMissing(Occurence) Then
For i = 0 To Ergs.Count - 1
Erg = Erg & Trenner & Ergs(i).Value
Next
bd_RegExtract = Mid(Erg, 2)
Else
bd_RegExtract = Ergs(Occurence - 1)
End If
End If
End Function
VG
Yal
Anzeige
AW: RegEx.Pattern "\d{4,} (owT)
13.01.2021 10:28:53
Daniel
Wenn man sich deinen Code so anschaut, dann fehlt in der ursprünglichen ein-Zeilen-Antwort aber noch sehr viel an notwendiger Information, um das fehlerfrei umzusetzen.
Gruß Daniel
AW: RegEx.Pattern "\d{4,} (owT)
13.01.2021 10:47:07
Yal
Hallo Daniel,
Das ist die Unterschied zwischen "Anregung" bzw "Idee" und vollständig entwickelte und geprüfte Lösung, die nicht nur diesen einmaligen Bedarf abdeckt, sonder wiederwendbar ist.
Ich denke nicht, dass es Anpruch des Forums ist, jedesmal mit letzterem zu antworten.
Daher finde ich den Anstoss von Fennek gar nicht mal so schlecht. Ohne den hätte ich gar nicht beigetragen.
Das Coding ist aus meinem Fundus. Ich habe es natürlich nicht extra für die Frage entwickelt.
Nur den Test, weil es die Erklärung dient.
VG
Yal
Anzeige
AW: RegEx.Pattern "\d{4,} (owT)
13.01.2021 11:16:50
Daniel
Nur sollte man sich fragen, ob der Fragesteller mit dieser Anregung auch wirklich weiterarbeiten kann.
Ich mache VBA jetzt seit fast 20 Jahren und mir würde diese "Anregung" nichts sagen, nicht mal einen Anstoßes geben, wonach ich suchen müsste.
AW: Was sollen wir ohne Beispiele tun?
12.01.2021 21:31:43
Daniel
Hi
wenn das Ergebnis so aussehen soll: (6589 0172123456 0171457895)
1. füge in einer Hilfsspalte per Formel am Anfang und Ende ein Leerzeichen hinzu: =" "&A1&" "
2. kopiere diese Hilfspalte und füge sie an gleicher Stelle als Wert ein
3. markiere die Hilfsspalte und und ersetzte nacheinander mit der Menüfunktion Erseten jeweils
" ? ", " ? " und " ? " durch " " (Leerzeichen mit Fragezeichen, die Anführungszeichen nicht mit eingeben)
4. entferne in einer weiteren Hilfsspalte mit =Glätten(B1) die Leerzeichen am Anfang und Ende
5. auch hier wieder kopieren und als Wert einfügen.
Gruß Daniel
Anzeige
AW: Was sollen wir ohne Beispiele tun?
12.01.2021 21:36:31
Daniel
Hi
per VBA für eine einzelnen Text so:
dim i as long
dim TeilTexte()
dim txtAlt as String
dim txtNeu as String
txtAlt = "12 2 6589 0172123456 23 0171457895"
TeilTexte = Split(txtAlt, " ")
for i = LBound(TeilTexte) to Ubound(TeilTexte)
If Len(TeilTexte(i)) 

Gruß Daniel
AW: Was sollen wir ohne Beispiele tun?
12.01.2021 22:07:15
Boerni
Hallo Daniel,
vielen Dank.
Wie kann ich den Code auf die Ziffern A1 bis A500 anwenden?
Boerni
AW: Was sollen wir ohne Beispiele tun?
12.01.2021 22:17:22
Daniel
im Prinzip so:
du musst folgendes in den Code integrieren:
dim Zelle as Range
For each Zelle in Range("A1:A500")
txtAlt = Zelle.Value
ab hier dann der erste Code ab der Zeile mit Split
Zelle.Value = txtNeu
Next
oder du wendest mein erstes beschriebenenes Vorgehen auf die Tabelle an, mit Hilfe des Makrorecorders sollte sich das auch recht einfach automatisieren lassen.
Gruß Daniel
Anzeige
AW: Was sollen wir ohne Beispiele tun?
13.01.2021 09:00:19
Boerni
Hallo Daniel,
mein Code sieht jetzt so aus:

Sub Ziffern_entfernen()
Dim i As Long
Dim TeilTexte()
Dim txtAlt As String
Dim txtNeu As String
Dim Zelle As Range
For Each Zelle In Range("A1:A500")
txtAlt = Zelle.Value
TeilTexte = Split(txtAlt, " ")
For i = LBound(TeilTexte) To UBound(TeilTexte)
If Len(TeilTexte(i)) 

Leider kommt ein Laufzeitfehler 13, Typen unverträglich.
Was habe ich falsch gemacht?
Deine Vorgehen mit suchen und ersetzen scheitert leider.
In manchen Zellen sind bis 40 Zahlenreihen, viele davon ein- und zweistellig.
Excel findet mit der Suche " ? " nicht alle im ersten Durchgang, sonder braucht dazu zwei oder drei Durchgänge, warum auch immer.
Danke
Boerni
Anzeige
AW: Was sollen wir ohne Beispiele tun?
13.01.2021 16:37:41
Daniel
Hi
naja grundsätzlich falsch ist, dass du mir verschweigst in welcher Zeile der Fehler auftritt. Excel zeigt dir das normalerweise an und das ist ein nicht ganz unwichtiger Hinweis auf die mögliche Fehlerursache und das sollte eigentlich so offensichtlich sein, dass man sich fragt, WARUM du diese Information für dich behälst.
ansonsten, mach mal aus dem DIM Teiltexte() ein DIM TeilTexte (ohne Klammern)
dann läufts.
sollten aus deinen Vorarbeiten auch Fehlerwerte in den Zellen stehen können, müsstest du das
txtAlt = Zelle.Value in txtAlt = Zelle.Text wandeln.
in meiner alternativ-Methode habe ich noch einen Denkfehler drin.
damit das richtig geht, muss man etwas mehr aufwand treiben.
1. ersetze " " durch "x x" (zwischen den x mindestens ein Leerzeichen mehr als du maximal Ziffern löschen willst
2. ersetze "x?x", "x?x" und "x?x" durch nichts
3. ersetze " " durch nichts
4. ersetze "xx" durch " "
vorher natürlich noch per formel am Anfang und Ende ein Leerzeichen hinzufügen und dieses am Schluss per Formel wieder entfernen (glätten)
Gruß DAniel
Anzeige
AW: Was sollen wir ohne Beispiele tun?
13.01.2021 20:15:57
Boerni
Hallo Daniel,
ich habe nicht absichtlich verschwiegen, in welcher Zeile der Fehler auftritt. Den Code hatte ich in Tabelle1 angelegt und da wird tatsächlich NICHT angezeigt, wo der Fehler auftritt. Nachdem ich jetzt den Code in Modul1 angelegt habe, wurde der Fehler in Zeile "TeilTexte = Split(txtAlt, " ")" angezeigt. Natürlich waren es die Klammern (Im Nachhinein fällt's sogar mir auf).
Jetzt funktioniert's wunderbar, das Problem ist erledigt.
Vielen Dank
Schönen Gruss
Boerni
Vermutlich "Weniger als 5 Ziffern" gemeint
13.01.2021 02:25:30
lupo1
... außerdem nehme ich aufgrund Deines Textes an, dass auch die Klammern weg sind:
B1[:B500]: =TEXTVERKETTEN(" ";;WENN(LÄNGE(
GLÄTTEN(TEIL(WECHSELN(A1;" ";WIEDERHOLEN(" ";99));SPALTE(A1:Z1)*99-98;99)))&lt5;"";
GLÄTTEN(TEIL(WECHSELN(A1;" ";WIEDERHOLEN(" ";99));SPALTE(A1:Z1)*99-98;99))))

Anzeige

304 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige