Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema Userform
BildScreenshot zu Userform Userform-Seite mit Beispielarbeitsmappe aufrufen

Replace bei Buchstaben nur erste 2 Zeichen beachte


Betrifft: Replace bei Buchstaben nur erste 2 Zeichen beachte von: Curly
Geschrieben am: 30.09.2019 14:58:04

Hallo zusammen,

aktuell benutze ich Replace um Zeichenketten zu ersetzten, jetzt habe ich aber folgende Schwierigkeit, die im SuchArray enthaltenen Buchstaben können auch im Text "mittig" enthalten sein und hier sollten sie nicht ersetzt werden.

suchArray = Array(" ", ".", "/", "MB", "MA", "MQ", "MN", "MH", "ZQ", "FQ")
ersetzArray = Array("", "", "", "B", "A", "Q", "N", "H", "Q", "Q")

For i = LBound(suchArray) To UBound(suchArray)

Worksheets(TabellenName).Range(Cells(RowIndex, ColumnIndex), Cells(letzteZeile, ColumnIndex)).Replace What:=suchArray(i), Replacement:=ersetzArray(i), Lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Next i

Ich hoffe ein Beispiel macht es anschaulicher:
- Bei Leerzeichen, Punkt und Slash soll die ganze Zeichenkette betrachtet und ersetzt werden, (also alle Leerzeichen, Punkte löschen)
- Bei Buchstaben, soll nur die ersten 2 Zeichen betrachtet und ersetzt werden.

Aus z.B.
"MAMA" soll "AMA" werden und nicht wie mit Code von oben "AA" oder
"MA MU MB/A" soll "AMUMBA" werden

meine versuche mit * und xlWhole schlugen leider fehl.

Vielen Dank
Curly

  

Betrifft: AW: Replace bei Buchstaben nur erste 2 Zeichen beachte von: Daniel
Geschrieben am: 30.09.2019 15:30:02

Hi
mit ERSETZEN wird das schwierig.
ich würde hier eine Formellösung vorschlagen.
dh die folgende Formel wird am Tabellenende in eine freie Spalte eingefügt und dann mit Kopieren + Inhalte einfügen Werte in die Zielspalte übertragen.
Gleiches vorgehen auch per VBA-Makro.

die Formel für deine Umformumg in einenm deutschen Excel mit A1 als Zielzelle wäre:

=WECHSELN(WECHSELN(WECHSELN(WENNFEHLER(SVERWEIS(LINKS(A1;2);{"MB"."B";"MA"."A";"MQ"."Q";"MN"."N"; "MH"."H";"ZQ"."Q";"FQ"."Q"};2;0);LINKS(A1;2))&TEIL(A1;3;9999);" ";"");".";"");"/";"")

Gruß Daniel


  

Betrifft: AW: Replace bei Buchstaben nur erste 2 Zeichen beachte von: Curly
Geschrieben am: 01.10.2019 14:25:09

Danke Daniel,

deine Formel läuft perfekt, allerdings sitzt mein Code hinter einer Userform die mir Artikelnummern in verschiedene Formate wandelt.
deshalb bräuchte ich eine Möglichkeit, ohne extra Spalte und in VBA geschrieben, welche ich dann wieder in meine Funktion einbauen kann.

Gruß Curly


  

Betrifft: AW: Replace bei Buchstaben nur erste 2 Zeichen beachte von: Daniel
Geschrieben am: 01.10.2019 15:03:17

Hi

Naja du kannst ja auch per Code die Formel in eine freie Spalte einfügen, die Werte übernehmen und dann die Formel wieder löschen.

Gruß Daniel


  

Betrifft: AW: Replace bei Buchstaben nur erste 2 Zeichen beachte von: Piet
Geschrieben am: 04.10.2019 15:14:12

Hallo Curly

Probier bitte mal diesen Code aus, damit sollte es klappen. Thread danach bitte schliessen. Viel Spass beim testen ...
Die Spalte "A" in Const musst du ggf. für deine Datei von Hand korrigieren.

mfg Piet

Option Explicit
Const Spalte = "A"

Sub Replace_String()
Dim suchArray, rFind As Range, i As Long

suchArray = Array("MB", "MA", "MQ", "MN", "MH", "ZQ", "FQ")

With Worksheets("Tabelle1")
     '" ", ".", "/" alles löschen
     .Columns(Spalte).Replace " ", ""
     .Columns(Spalte).Replace ".", ""
     .Columns(Spalte).Replace "/", ""

     'Array über Suchlauf auswerten
     For i = LBound(suchArray) To UBound(suchArray)
         Set rFind = .Columns(Spalte).Find(What:=suchArray(i), After:=Cells(1, Spalte), _
            LookIn:=xlFormulas, LookAt:=xlPart, SearchDirection:=xlNext, MatchCase:=False)
         
         If Not rFind Is Nothing Then
            Do  'Prüfe 2 Stellen Links auf Übereinstimmung
               If Left(rFind, 2) = suchArray(i) Then
                  rFind.Value = Mid(rFind, 2, Len(rFind))
               End If
               Set rFind = .Columns(Spalte).FindNext(After:=rFind)
               If rFind Is Nothing Then Exit Do
            Loop
         End If
     Next i
End With
End Sub



  

Betrifft: AW: Replace bei Buchstaben nur erste 2 Zeichen beachte von: Hajo_Zi
Geschrieben am: 04.10.2019 16:05:30

Hallo Piet,

lasse den Fragesteller entscheiden ob offen.
Es ist heute nicht mehr üblich eine Rückmeldung zu geben und so ist der Beitrag über 6 Tage offen.

Das ist nur meine Meinung zu dem Thema.

GrußformelHomepage


  

Betrifft: AW: Replace bei Buchstaben nur erste 2 Zeichen beachte von: Curly
Geschrieben am: 04.10.2019 21:20:57

Hi Piet,

vielen Dank für deinen Code, ich bin gerade dabei ihn zu testen.
Aber ich habe fest gestellt das er in der Zeile
" If Left(rFind, 2) = suchArray(i) Then " das "i" im Sucharray nicht erhöht und er sich somit in einer endlos schleife befindet.

Danke für deine Hilfe.

Gruß Curly


  

Betrifft: AW: Replace bei Buchstaben nur erste 2 Zeichen beachte von: Piet
Geschrieben am: 05.10.2019 13:56:39

Hallo Curly

Sorry, da bist du leider im Irrtum. Du darfst in der Do Loop Schleife die Variable "i" nicht veraendern, dann bringst du den ganzen Suchlauf durcheinander!!

Die Variable "i" wird in der For Next Schleife staendig um 1 erhöht! Da ist das auch richtig! Die nachfolgnde Do Loop Schleife durchsucht jetzt die ganze Spalte immer nur nach diesem einem Text, z.B. "MA". Erst danach wird der naechste Array Text gesucht. Sonst stimmt FindNext nicht mehr!! Der sucht ja nach "MA"!

Mfg Piet


Beiträge aus dem Excel-Forum zum Thema "Replace bei Buchstaben nur erste 2 Zeichen beachte"