Strg+H kennt fast jeder um in Excel um z.B. „:A“ durch „:$A“ zu ersetzen. Das wird erst dann recht mühsam, wenn man mehrere Zeichenfolgen ersetzen will und erst recht, wenn man das immer wieder machen muss bei Import Dateien, Zahlen und Datumsformaten aber auch um Kategorien abzugleichen. Auch codierte Umlaute oder mehrfache Leerzeichen können so korrigiert oder entfernt werden.

Weil ich viel mit verschiedenen externen Reports arbeite und z.B. Kontoauszügen habe ich ein kleines Excel-Makro, dass mir dabei hilft das recht schnell zu erledigen. Für alle Ungeduldigen geht’s hier direkt zum Download.

 

Funktionsweise des Ersetzen-Makros

Enthalten sind zwei Versionen des Makros, einmal mehrere Strings durch den gleichen Wert ersetzen oder jeweils einen String durch einen anderen ersetzen. Version eins ist für das Ersetzen verschiedener Varianten einer Zeichenfolge gedacht. Version zwei stellt einer Zeichenfolge eine zu ersetzende Zeichenfolge gegenüber; Umlaute z.B. oder verschiedene Schreibweisen von Ländern.

 

Daten vorbereiten

Für beide Versionen müssen die Daten erstmal in das File. Dafür kopierst Du in den Reiter „Replace“ die Tabelle, in der Du die Werte suchen und ersetzen willst. Am besten kopierst Du die komplette Tabelle, damit es beim Sortieren und Filtern nicht zu Verschiebungen kommt.

 

Version 1: Mehrere Zeichenfolgen durch eine Zeichenfolge ersetzen

Im Reiter „Strings_x_String“ stehen in Spalte A untereinander die Zeichenfolgen, die ersetzt werden sollen. Im Excel File stehen hier schon Werte drin (aus einem Kontoauszug), diese kannst Du ruhig löschen oder überschreiben.

Daneben steht in Spalte B die Formel für die Zeichenlänge. Entsprechend dieser Formel sortiert das Makro die Strings in abnehmender Reihenfolge. Das ist wichtig, wenn Du z.B. sich wiederholende Zeichenfolgen in der Liste hast. Dazu ein Beispiel: In einer Excel Liste sind zwei verschiedene Versionen für „Deutschland“ enthalten, einmal „Deutschland DE“ und einmal „Deutschland“ und das möchte ich korrigieren bzw. beide in „DE“ ändern.

Führe ich nun das Ersetzen zuerst für „Deutschland aus, dann wird daraus folgendes:

 

Beispiel_für_Länge

 

Aber keine Angst, Du kannst das nicht vergessen. Dass Makro schreibt die Formeln automatisch rein und sortiert die Einträge entsprechend, bevor der eigentliche Vorgang startet.

In Spalte C werden doppelte Einträge angezeigt. Das ist wichtig, wenn Teilstrings ersetzt werden, die hinterher wieder vorhanden sind, also z.B. wenn 100k durch 100kg ersetzt wird; denn der Teilstring“100k“ ist ja dann wieder enthalten und wird dann noch mal ersetzt, was dazu führt „100kgg“.

In die Zelle H1 kommt der Werte, durch den die Strings ersetzt werden sollen. Beim Klick auf den Button daneben startet das Makro, das fügt die Formeln ein und sortiert die Strings. Danach fragt das Makro die Spalte(n) ab, in der die Werte gesucht und ersetzt werden sollen. Wenn Du nur eine Spalte durchsuchen sillst, dann beides mal die gleiche Spalte angeben. Das Makro sucht in den angegebenen Saplte(n) in „Replace“ jeden Eintrag und ersetzt ihn durch den Wert in Zelle H1.

 

Version 2: Mehrere Zeichenfolgen durch jeweils eine Zeichenfolge ersetzen

Die Tabelle mit den Daten ist in den Reiter „Replace“ kopiert? Dann kannst Du in den Reiter „Strings_x_StringS“ wechseln.

Hier stehen in Spalte A wieder die Zeichenfolgen, die ersetzt werden sollen. In Spalte B und C wieder die Formeln für die Länge der Zeichenfolgen und die doppelten Einträge. In Spalte D kannst Du jetzt jedem Wert aus Spalte A einen separaten Wert zuweisen. Das Makro ersetzt dann den Wert aus Spalte A durch den Wert der gleichen Zeile aus Spalte D. Dabei kannst Du natürlich auch gleiche Werte in Spalte D verwenden. In die Zelle H1 muss dann natürlich nichts eingetragen werden.

Die Werte die hier schon drin stehen sind decodierte Umlaute, die wieder in „normale“ Umlaute umgewandelt werden sollen. Ein Problem, dass mir sehr oft begegnet.

Wenn Du über den Button das Makro startest, werden hier auch die Formeln eingefügt und die Werte absteigend nach der Länge sortiert. Danach fragt das Makro wieder die Spalte(n) ab in denen gesucht wird und ersetzt dann nacheinander alle Werte aus A durch die Werte aus D in „Replace“. Bei nicht zusammenhängenden Spalten einfach mehrmals laufen lassen mit den unterschiedlichen Bereichen.

 

Erledigt! Hier noch mal der Link zum File.

Ich hoffe das erleichtert dem einen oder anderen ein wenig die Arbeit und bei Anregungen oder Kritik oder Bugs, immer her damit.