Tag Archives: ERSETZEN

Jahrelang wurde Kindern beigebracht für schönes Wetter alles aufzuessen. Und was haben wir jetzt? Dicke Kinder und Hitzewelle!

Erstaunlich! Aber erklärbar!

Ich erstelle eine Reihe an Werten, mit denen ich weiterrechne – beispielsweise „+“:

Ich lösche zwei Zeilen. Der Fehler #BEZUG! ist die Folge:

Ich kopiere diese fehlerhafte Zeile an einen andere Stelle und füge sie als Inhalt ein:

Ich lösche mit dem Ersetzen Befehl, indem ich #BEZUG! durch „nichts“ ersetze:

Klappt – alles weg. Sowohl in den Formeln (=#BEZUG!+#BEZUG!+D1) als auch in den Zellen

Ich zeichne diese Aktion mit dem Makrorekorder auf:

Der Code:

    Cells.Replace What:="#BEZUG!", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

Das Ergebnis:

Nichts wird gelöscht! Also doch per Hand. Beispielsweise so:

Sub Bezug_Loeschen_02()
     Dim xlZelle As Range
     For Each xlZelle In ActiveSheet.UsedRange
        If xlZelle.Text = "#BEZUG!" Then
           xlZelle.ClearContents
       End If
    Next
End Sub

Der Grund ist verständlich: Der Suchen- und Ersetzen-Befehl ist sehr mächtig. So wird auch in „26.10.2020“ der Wert „2020“ gefunden, obwohl in der Zelle eigentlich der Wert 44.130 steht. Eben: #BEZUG! ist eigentlich ein Fehlerwert (und kein Text). Er steht ja auch in der Mitte der Zelle. Suchen und ersetzen „übergeht“ diesen Datentyp; der VBA-Befehl Replace sucht (und ersetzt) einen Text (was nicht in der Zelle steht).

Und wer nun fragt: „Wer braucht denn so etwas?“ Letzte Woche habe ich eine Datei mit fehlerhaften Bezügen bekommen. Der Anwender hat ein Blatt von einer Datei in eine andere kopiert. Ich wollte diese nichtssagenden Fehler mit einem Makro „rausputzen“ – Klick auf Button sollte die Datei „bereinigen“.

Und so habe ich festgestellt, dass der Replace-Befehle nicht geeignet ist.

Ist nicht Bier kaltstellen auch irgendwie kochen?

Word hat mehr Zeichen als Excel: geschützte Leerzeichen, bedingte Trennzeichen, geschützte Trennzeichen, … Kopiert man Texte mit solchen Zeichen nach Excel ist das Erstaunen oft groß: Zeichen, die in der Zelle angezeigt werden, jedoch nicht in der Eingabezeile.

Die Funktion CODE hilft den (ASCII-)Code zu ermitteln:

Mit diesem Wissen kann man mit der Funktion WECHSELN die alten Zeichen (hier: 173) durch „“ oder durch ein anderes Zeichen ersetzen:

Oder ich markiere es, indem ich mit [F2] in die Zelle wechsle, dort mit der Pfeiltaste nach rechts wandere und mit [Umschalt] + [Pfeil] markiere. Das kann ich kopieren [Strg] + [C])

und in den Ersetzen-Dialog einfügen. Und so alle Trennzeichen auf dem Blatt löschen.

Wenn man dir viel heiße Luft um die Ohren bläst, sitzt du nicht zwangsläufig beim Friseur. Vielleicht bist du auch in einem Vortrag oder Seminar.

Eine Artikelliste.

[Strg] + [H]. Ich ersetze „Dawn“ durch „Fairy“. Ich schließe den Dialog.

Nun möchte ich Twix durch Raider ersetzen.

Erneut: [Strg] + [H]. Warum ist der Fokus auf dem Ersetzen-Feld und nicht auf dem Suchen-Feld. Ich muss nach oben klicken oder „springen“, um „Twix“ durch „Raider“ zu ersetzen. Jedesmal. Doof!

Coco Chanel sagt, man solle das Parfum dorthin sprühen, wo man geküsst werden will. Brennt jetzt aber ziemlich.

Hallo Herr D.,

danke für den Hinweis.
Wir haben ein Problem! Ich habe es mal eingekreist:

In einer Datei habe ich fünf Verknüpfungen auf eine andere Datei:

Ich ersetze den Pfad durch einen anderen Pfad, in dem die Datei noch einmal liegt:

Klappt!
Ich ersetze den Pfad durch einen anderen Pfad, in dem die Datei nicht liegt oder durch einen anderen Ordner, der nicht existiert. Es öffnet sich das Suchfester, das abfragt, wo denn die Datei liegt:

Ich breche es ab.
Was passiert: Excel hat nur in der ausgewählten Zelle den Pfad geändert – in allen anderen nicht!

Das heißt: da Sie SUMMEWENNS-Formeln auf mehrere Dateien aufsetzen:

SUMMEWENNS( … \xyz\[2017-04-27_xyz_EHB_KoPrüf Gas 2015_Verpächter_1_SWM_überarbeitet Biogas.xlsx] … + \abc2\[2016-08-01_xyz_EHB_KoPrüf Gas 2015_Verpächter_3_GVG_versandt.xlsx]

„erkennt“ Excel den Gesamtpfad als nicht gültig und stoppt den Ersetzvorgang.

Nun haben wir ein Problem: die zirka 50.000 Formeln beziehen sich auf zwei Ordner, die es nicht mehr gibt. Sobald ich einen versuche zu reparieren, weigert sich Excel diesen Vorgang in allen Zellen durchzuführen.

Was machen wir? Haben Sie nur einige wenige solcher Dateien? Dann könnte man den Pfad so „hinbiegen“, dass er beim ersten Ersetzvorgang korrekt ist, beim zweiten immer noch, beim dritten auch …
Haben Sie „viele“ solcher Dateien? Dann muss ich noch einmal in mich gehen …

„Hübscher“ Bug zum Thema „Excel ersetzt nicht“. Oder „Excel ersetzt nur einmal“. Oder „Ersetzen in Excel klappt nicht.“

Betrunkene sagen die Wahrheit. Blöd, das sie immer so nuscheln.

Hallo Rene,

bitte wieder Tipparbeit bei der schon bekannten Tabelle.

Durch die Hinweise auf VBA in manchen Vorträgen habe ich mir einige der Schulungen angesehen.
Ach oh je 🙁
Es ist toll, dass man sich nachdem das Programm feriggestellt ist arbeit erspart. Die Suche im Internet nach geeigneten Code hat natürlich nicht zu einem Ergebnis geführt.
Dazu braucht man ein erweitertes Grundwissen das ich nicht habe.

Meine Bitte und Anliegen:
Kannst Du mir bitte VBA Code für die Tabelle mit der Datumskorrektur schreiben?

Das alte Datum soll ausgeblendet werden.
Das einfügen der Spalte für das neu formatierte Datum muss variable sein. Es kommt neuerdings auch eine Tabelle mit Point vor dem Datum.

Bitte ein alphabethisches Inhaltsverzeichnis mit Link auf die aktuellen Tabellen.

Zu VBA
Kannst Du mir ein Buch empfehlen?
Kann man in den Büchern, ähnlich wie in denen von Excel, bestimmte Code für Funktionen (kopieren, einfügen, Formeln in VBA) finden?

Im Internet habe ich für das Einfügen einer Spalte Code entdeckt. Wollte aber eine variable Möglichkeit, die ich nicht gefunden habe. Mitbekommen habe ich, dass man Variable definieren muss. Wie diese jedoch bezeichnet werden habe ich nicht herausgefunden. Die Zellen werden in Ziffern aufgeteilt. Buchstaben und Zahlen für die Bezeichnung der Zellen habe ich in anderen Code entdeckt. Erwähnt wurde auch der Unterschied zwischen USA und dem localen Code.

Mit besten Grüßen
Peter

#####

Hallo Peter,

hier der Code:

Sub MonateErsetzen()
Dim strSpalte As String
Dim i As Integer
Dim strMonateDeutsch
Dim strMonateEnglisch

strMonateDeutsch = Array(„Jänner“, „Februar“, „März“, „April“, „Mai“, „Juni“, „Juli“, „August“, „September“, „Oktober“, „November“, „Dezember“)
strMonateEnglisch = Array(„January“, „February“, „March“, „April“,
„May“, „June“, „July“, „August“, „September“, „October“, „November“,
„December“)

strSpalte = InputBox(„In welcher Spalte sollen die Datumswerte ersetzt werden?“)

For i = 0 To UBound(strMonateDeutsch)
ActiveSheet.Columns(strSpalte & „:“ & strSpalte).Replace What:=strMonateEnglisch(i), Replacement:=strMonateDeutsch(i), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next i

End Sub

Wie kommt man dazu? Zeichen mit dem Makrorekorder auf – beispielsweise ersetze „January“ durch „Januar“. Dann erhältst du folgenden Code:

Sub Makro1()

‚ Makro1 Makro


Selection.Replace What:=“February“, Replacement:=“Februar“, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Nun – und da bauen wir ein bisschen Makro außenrum.

Ich empfehle keine Bücher. Jeder hat eine andere Art zu lesen und zu lernen.
Geh in eine große Buchhandlung, hole einen Meter VBA-Literatur raus, blättere sie durch und bestelle dann das Buch, das dir am besten gefallen hat, in einer kleinen Buchhandlung.

Tipp: ich würde einen Kurs besuchen (Volkshochschule oder privater Anbieter). Dort bekommst du in kurzer Zeit am schnellsten die wichtigste Informationen. Dozent zeigt auch, was man falsch machen kann und hilft.
Liebe Grüße

Rene

#######

Hallo Rene,

Danke.

Leider hilft mir dieser Code nicht weiter nachdem nur der Monatsname in deutscher Schreibweise umsetzt wird. Benötigt wird ein ‚echtes‘ Datum.

In der Spalte C steht einmal ein echtes Datum und dann das Datum als Text.

In der neuen Spalte D die eingefügt wurde, hast du mit der untenstehenden Formel ein ‚echtes‘ Datum erstellt.

=WENN(ISTZAHL(C2);C2;DATUM(RECHTS(C2;4);VERGLEICH(TEIL(C2;SUCHEN(“ „;C2)+1;LÄNGE(C2)-8);{„January“;“February“;“March“;“April“;“May“;“June“;“July“;“August“;“September“;“October“;“November“;“December“};0);LINKS(C2;2)))

erstellt.

 

Bitte im VBA Code umsetzen, dass eine neue Spalte erzeugt wird, das Datum nach deiner Formel eingesetzt wird und die Spalte mit den nicht brauchbaren Angaben ausgeblendet wird.

Deine Vorschläge werde ich befolgen. Schau mal in die Buchhandlung was dort aufliegt. Ein Kurs ist auch eine gute Idee.

Besten Dank,

Peter

#####

Hallo Peter,

probiere es aus!

Durch das Ersetzen „erkennt“ Excel ein Datum und wandelt in eine Datumszahl um

LG ::  Rene

Ich schimpfe nie beim Autofahren – ich raste direkt aus.

gestern in der Excel-Schulung wollte eine Teilnehmerin wissen, warum „ersetzen“ (von suchen und ersetzen) als Funktion „WECHSELN“ heißt und nicht „ERSETZEN“. Denn ERSETZEN macht ja etwas anderes:

Die Antwort: Ich weiß es nicht. Auch im Englischen heißen die Funktionen SUBSTITUTE und REPLACE. Irgendwie doof gemacht …

Armbruch, Beinbruch, Zeilenumbruch

Hilfe!

Ich habe eine Exceltabelle, in der mein Kollege häufig Zeilenumbrüche – also [ALT] + [Return] eingefügt hat. Wie kann ich die ganz schnell löschen?

Viele Zeilenumbrüche

Viele Zeilenumbrüche

Die Antwort: Sie können im Ersetzen-Dialog (Registerkarte Start / Bearbeiten / Suchen und Auswählen oder: [STRG] + [H]) auch nach Formatierungen suchen. Erstaunlicherweise findet Excel die Zeilenumbrüche, wenn Sie die Formatierung in der Registerkarte „Ausrichtung“ aktivieren. Und löscht sie, wenn Sie bei „Ersetzen“ explizit den Zeilenumbruch ausschalten UND die Option „An Zellgröße anpassen“ einschalten.

Texte zerschneiden

Ich habe in der letzten Excelschulung gelernt, dass man mit Textfunktionen Texte „manipulieren“ kann. Das wollte ich ausprobieren.

Ich habe eine Liste mit Vor- und Zunamen. Die Vornamen löse ich mit:

=LINKS(A2;FINDEN(“ „;A2)-1)

heraus. Klappt prima. Bei den Nachnamen bei der Formel

=RECHTS(A2;FINDEN(“ „;A2)-1)

macht er aber bei einigen Namen Blödsinn. Warum?

RECHTS klappt nicht!

RECHTS klappt nicht!

Das Problem: Die Funktion FINDEN (oder auch SUCHEN) findet einen Text innerhalb eines anderen VON LINKS. Das bedeutet: Sie schneiden aus dem Text VON RECHTS so viele Buchstaben heraus wie der Vorname lang ist. Das kann zufälligerweise funktionieren, normalerweise aber nicht. Sie lösen das Problem entweder mit der Gesamtanzahl der Buchstaben LÄNGE:

=RECHTS(A2;LÄNGE(A2)-FINDEN(“ „;A2))

oder mit der Funktion TEIL, die ab einem bestimmten Zeichen herausschneidet:

=TEIL(A2;FINDEN(“ „;A2)+1;999)

(zugegeben: die Zahl 999 ist „geschummelt“ – Sie schneiden nach dem Leerzeichen 999 (oder eine noch höhere Anzahl Buchstaben heraus) – aber es funktioniert)

Oder Sie löschen die ersten Buchstaben weg:

=ERSETZEN(A2;1;FINDEN(“ „;A2);““)

All das funktioniert.

So funktioniert es - drei Varianten

So funktioniert es – drei Varianten