Microsoft Excel

Herbers Excel/VBA-Archiv

Text vor bestimmtem Zeichen löschen


Betrifft: Text vor bestimmtem Zeichen löschen
von: Matthias K.
Geschrieben am: 13.12.2018 08:52:41

Guten Morgen zusammen,

folgendes Problem:

Ich versuche den Text vor einem bestimmten Zeichen zu löschen. Es geht sich um Telefonlisten. Nummern sind wie folgt aufgebaut:

+49 (0000) 0000 - 00

Nun möchte ich alles vor dem "-" löschen, und nur die Durchwahl stehen lassen.

Folgendes Makro habe ich gebaut:


Application.ScreenUpdating = False
   start = 3
   letzte = ActiveSheet.Range("E65536").End(xlUp).Row
   For lngZ = letzte To start Step -1
      strW = Cells(lngZ, 5).Value
      If InStr(2, strW, "-", vbTextCompare) > 0 Then
         Cells(lngZ, 5) = Right(strW, InStr(2, strW, "-", vbTextCompare) - 1)
      End If
   Next
   Application.ScreenUpdating = True

Es klappt teilweise. Es nimmt das erste Zeichen, was es findet. und nicht das "-". Kann mir jemand verraten, wo der Fehler liegt?

Vielen Dank im voraus und liebe Grüße,

Matthias K.

  

Betrifft: AW: Text vor bestimmtem Zeichen löschen
von: PeterK
Geschrieben am: 13.12.2018 08:57:44

Hallo

RIGHT verlangt als 2. Parameter die Anzahl der Zeichen! Also "len(strW) - InStr(..."


  

Betrifft: AW: Text vor bestimmtem Zeichen löschen
von: Daniel
Geschrieben am: 13.12.2018 09:00:35

Hi

bei RIGHT musst du als 2. Parameter folgendes angeben:

Right(strW, Len(strW) - Instr(strW, "-"))
einfacher wäre hier die MID-Funktion:
Mid(strW, Instr(strW, "-")+1)
noch einfacher geht's aber mit der ERSETZEN-Funktion, wenn du "*-" durch nichts ersetzt:
dein komplettes Makro lässt sich dann mit einer Programmzeile ausführen:
Columns(5).Replace "*-", "", xlpart
Gruß Daniel


  

Betrifft: AW: Text vor bestimmtem Zeichen löschen
von: Matthias K.
Geschrieben am: 13.12.2018 09:13:06

Hallo Daniel,
Vielen Dank, Funktioniert prima!


  

Betrifft: Achtung Falle: Wandlung Text in Zahl vermeiden
von: Daniel
Geschrieben am: 13.12.2018 09:33:09

du musst hier u.U. beachten, dass wenn beim Entfernen der Vorwahl-Nummern nur Ziffern stehen bleiben, Excel dies in Zahlen wandelt und dann aus "xxx-01" nur "1" machst und die 0 verschwindet.
damit das nicht passiert musst du Excel mitteilen, dass die Werte wie Text behandelt werden sollen und nicht als Zahl. Hierzu musst du das Hochkomma voranstellen:

Replace "*-", "'", xpart
das solltest du auch tun, wenn du bei deiner Schleifenlösung bleibst:
Cells(lngZ, 5) = "'" & Mid(strW, InStr(strW, "-") + 1)
dann gäbe es noch eine dritte Lösungsvariante
wende auf die Spalte die Funktion DATEN - DATENTOOLS - TEXT IN SPALTEN an
wähle "-" als Trennzeichen
klicke im Schritt 3
- auf die erste Spalte und wähle "nicht importieren"
- auf die zweite Spalte und wähle "Text"

sieht als Code dann so aus:
Columns("E:E").TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
            Tab:=False, Semicolon:=False, Comma:=False, Space:=False, _
            Other:=True, OtherChar:="-", _
            FieldInfo:=Array(Array(1, 9), Array(2, 2))
Gruß Daniel


  

Betrifft: AW: Achtung Falle: Wandlung Text in Zahl vermeiden
von: Matthias K.
Geschrieben am: 13.12.2018 10:03:34

Hallo Daniel,

Vielen Dank für die Info, aber die Formatierung ist kein Problem, ich habe eine Hilfszeile für die Formatierung der Zellen, diese wird auch per Schleife rüberkopiert.

Range("A3:G4").Select
    Selection.AutoFill Destination:=Range("A3:G55"), Type:=xlFillFormats
    Range("A1").Select



  

Betrifft: AW: Text vor bestimmtem Zeichen löschen
von: Matthias L
Geschrieben am: 13.12.2018 09:01:10

Hallo

Geht auch mit Formeln

Tabelle1

 AB
1 +49 (0000) 0000 - 0000
2 +49 (0000) 0000 - 0101
3 +49 (0000) 0000 - 0202
4 +49 (0000) 0000 - 0303
5 +49 (0000) 0000 - 0404
6 +49 (0000) 0000 - 0505
7 +49 (0000) 0000 - 0606
8 +49 (0000) 0000 - 0707
9 +49 (0000) 0000 - 0808
10 +49 (0000) 0000 - 0909
11 +49 (0000) 0000 - 1010
12 +49 (0000) 0000 - 1111
13 +49 (0000) 0000 - 1212
14 +49 (0000) 0000 - 1313

Formeln der Tabelle
ZelleFormel
B1=WENNFEHLER(GLÄTTEN(TEIL(A1;FINDEN("-";A1)+1;9^9));"")
B2=WENNFEHLER(GLÄTTEN(TEIL(A2;FINDEN("-";A2)+1;9^9));"")
B3=WENNFEHLER(GLÄTTEN(TEIL(A3;FINDEN("-";A3)+1;9^9));"")
B4=WENNFEHLER(GLÄTTEN(TEIL(A4;FINDEN("-";A4)+1;9^9));"")
B5=WENNFEHLER(GLÄTTEN(TEIL(A5;FINDEN("-";A5)+1;9^9));"")
B6=WENNFEHLER(GLÄTTEN(TEIL(A6;FINDEN("-";A6)+1;9^9));"")
B7=WENNFEHLER(GLÄTTEN(TEIL(A7;FINDEN("-";A7)+1;9^9));"")
B8=WENNFEHLER(GLÄTTEN(TEIL(A8;FINDEN("-";A8)+1;9^9));"")
B9=WENNFEHLER(GLÄTTEN(TEIL(A9;FINDEN("-";A9)+1;9^9));"")
B10=WENNFEHLER(GLÄTTEN(TEIL(A10;FINDEN("-";A10)+1;9^9));"")
B11=WENNFEHLER(GLÄTTEN(TEIL(A11;FINDEN("-";A11)+1;9^9));"")
B12=WENNFEHLER(GLÄTTEN(TEIL(A12;FINDEN("-";A12)+1;9^9));"")
B13=WENNFEHLER(GLÄTTEN(TEIL(A13;FINDEN("-";A13)+1;9^9));"")
B14=WENNFEHLER(GLÄTTEN(TEIL(A14;FINDEN("-";A14)+1;9^9));"")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Gruß Matthias


  

Betrifft: AW: Text vor bestimmtem Zeichen löschen
von: Matthias K.
Geschrieben am: 13.12.2018 09:14:38

Hallo Matthias,

Vielen Dank, aber ich muss das in VBA lösen, habs jetzt dank Daniel :) Danke euch vielmals!