Datenschutzerklärung


Direktnachricht



Ihre Software
Details
Excel/VBA 🔍
Add-Ins

Suche in Beispielen und Tipps zu Excel und VBA

Suchbegriff(e) mit Leerzeichen getrennt:

Doppelte Unterstriche durch einen ersetzenMakro/Sub/Prozedur

Kategorie: Suchen/Ersetzen

(Tipp 70) Nachricht zum Beitrag an Autor Nach oben

Auf einem Blatt sollen mehrere Unterstriche am Stück auf jeweils einen reduziert werden.

Wenn vorher bekannt ist, wie viele Unterstriche enthalten sein können, kann das verwendet werden:

Sub Unterstriche() Dim intI As Integer Application.ScreenUpdating = False '10 = maximale Zahl der Unterstriche For intI = 1 To 10 Range("A1:A65536").Replace What:="__", Replacement:="_", LookAt:=xlPart, SearchOrder:=xlByRows Next Range("A1").Select Application.ScreenUpdating = True End Sub

Ansonsten kann auch eine While-Schleife so lange laufen, wie noch doppelte Unterstriche gefunden werden:

Dim objGef As Object Set objGef = Cells.Find("__") Do While Not objGef Is Nothing Cells.Replace "__", "_" Set objGef = Cells.Find("__") Loop

Variante mit regulären Ausdrücken:

Sub Doppelte_Unterstriche() Dim strRepl As String, rngZelle As Range Dim Regex As Object, regMatches, regMatch If Regex Is Nothing Then Set Regex = CreateObject("VBScript.RegExp") strRepl = "_" Regex.Global = True Regex.Pattern = "(__+)" For Each rngZelle In ActiveSheet.UsedRange Set regMatches = Regex.Execute(rngZelle) rngZelle = Regex.Replace(rngZelle, strRepl) Next Set Regex = Nothing End Sub

Onedrive-Pfad zu lokalem Pfad (regulärer Ausdruck)UDF - benutzerdefinierte Funktion

Kategorien: Netz ▸ OneDrive und Stringoperationen ▸ Ersetzen

(Tipp 604) Nachricht zum Beitrag an Autor Nach oben

Mit z. B. ThisWorkbook.Fullname wird der OneDrive-Pfad (https://d.docs.live.net/…) zurückgegeben. Wie kann der in den lokalen Pfad umgewandelt werden?

Die folgende Funktion speichert mit Environ() den lokalen OneDrive-Ordner in eine Variable. Anschließend ersetzt sie im Pfad der Datei diverse mögliche Zeichenfolgen, die OneDrive selbst vergibt. Mit einem regulären Ausdruck wird dann der OneDrive-Teil im Pfad der Datei durch den lokalen Pfad ersetzt.

Ggf. können noch ein paar Fehlerbehandlungen eingebaut werden.

Function OneDrive2Lokal(ByVal strPfad As String) As String Dim strOnedrivePfad As String, RegEx As Object, regMatches OneDrive2Lokal = "" If RegEx Is Nothing Then Set RegEx = CreateObject("VBScript.RegExp") If Left(strPfad, 6) = "https:" Then strOnedrivePfad = Environ("OneDrive") & "\" ' oder "OneDriveConsumer" strPfad = Replace(strPfad, "^J", ",") strPfad = Replace(strPfad, "^N", "#") strPfad = Replace(strPfad, "^0", "&") RegEx.Pattern = "^(https:/{2}[A-Za-z0-9./]+/)" Set regMatches = RegEx.Execute(strPfad) strPfad = Replace(RegEx.Replace(strPfad, strOnedrivePfad), "/", "\") End If OneDrive2Lokal = strPfad Set RegEx = Nothing End Function

Die Funktion kann beliebig verwendet werden, zum Beispiel:

MsgBox OneDrive2Lokal(ThisWorkbook.FullName)

Zahl mit Trennzeichen trennenUDF - benutzerdefinierte Funktion

Kategorien: Stringoperationen ▸ Ersetzen und Stringoperationen ▸ Verketten

(Tipp 553) Nachricht zum Beitrag an Autor Nach oben

Eine Zahl, z. B. 8070110, soll nach jeder 0 einen Bindestrich haben, also so: 80-70-110.

Hier ist eine benutzerdefinierte Funktion:

Function zahl_aufteilen(Zahl, Ziffer, Trenner) Application.Volatile Dim intI As Integer Dim strTemp As String, strTrenner As String strTrenner = Ziffer & Trenner strTemp = Replace(Zahl, Ziffer, strTrenner) If Right(strTemp, 1) = Trenner Then strTemp = Left(strTemp, Len(strTemp) - 1) zahl_aufteilen = strTemp End Function

Dazu mit Alt und F11 den Editor aufrufen, ein Modul einfügen und die Function eingeben. In die Zelle kommt dann z. B. die folgende Formel:

=zahl_aufteilen(B6;0;"-")


Regulärer Ausdruck

Eine weitere Variante ist diese Funktion:

Function Zahl_Aufteilen_Regex(ByVal varZahl, ByVal strTrenner As String, intZiffer As Integer) Dim Regex As Object, regMatches Set Regex = CreateObject("VBScript.RegExp") Regex.Global = True Regex.Pattern = intZiffer & "\B" Set regMatches = Regex.Execute(varZahl) Zahl_Aufteilen_Regex = Regex.Replace(varZahl, intZiffer & strTrenner) Set Regex = Nothing End Function

In die Zelle käme diese Formel:

=Zahl_Aufteilen_Regex(B6;"-"; 0)