Replace more characters using UDF

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Replace more characters using UDF

Post by YasserKhalil »

Hello everyone
I am using the following UDF to replace specific letter in names with another letter

Code: Select all

Function MyReplace(ByVal txt As String) As String
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "ي(\s|$)"
        MyReplace = Trim(.Replace(txt, "ى "))
    End With
End Function

How can I add more characters to replace ...?
Example: the letters "a" and "c" and "k" would be replaced with "y"
the letters "h" would be replaced with "z"
This is just example to illustrate that I need more replace in flexible way
Thanks advanced for help.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Replace more characters using UDF

Post by HansV »

To find the letters a, c or h, use [ach] in the Pattern.
You'll have to do different replacements separately. You might create an array with characters to match and another array with replacement characters.
You can then loop to perform the replacements.
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Replace more characters using UDF

Post by YasserKhalil »

So this wouldn't be possible using the regex pattern ..?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Replace more characters using UDF

Post by HansV »

Some regular expression engines allow for multiple replacements in one go, but I don't think the VBScript one does. Maybe someone else knows.
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Replace more characters using UDF

Post by YasserKhalil »

Thanks a lot my tutor.

LisaGreen
5StarLounger
Posts: 964
Joined: 08 Nov 2012, 17:54

Re: Replace more characters using UDF

Post by LisaGreen »

Hi YasserKhalil,

Is this for vbscript or vba?

Lisa

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Replace more characters using UDF

Post by YasserKhalil »

It is for VBA.

LisaGreen
5StarLounger
Posts: 964
Joined: 08 Nov 2012, 17:54

Re: Replace more characters using UDF

Post by LisaGreen »

My apologies, but my VBE doesn't seem to accept the characters "ي" and "ى ". Is it possible for you to supply an english text string you want to replace letters in and the result you want please?

Lisa

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Replace more characters using UDF

Post by YasserKhalil »

You can use any English letter. The letter I am using is in Arabic.

LisaGreen
5StarLounger
Posts: 964
Joined: 08 Nov 2012, 17:54

Re: Replace more characters using UDF

Post by LisaGreen »

Yes, I understand that. Is it possible for you to give us an original string and a string, a pattern to replace, and a string reflecting what you want please?

The reason I'm asking is that your code will replace the a c and h in one go in the following.

Code: Select all

Sub subRegExReplace()

MsgBox MyReplace("abasbdbrbtbaaaoodddhhhh")


' *********************************************************************
End Sub
Function MyReplace(ByVal txt As String) As String
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "[ach]"
        MyReplace = Trim(.Replace(txt, " @ "))
    End With
End Function
Result....
@ b @ sbdbrbtb @ @ @ ooddd @ @ @ @


Lisa

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Replace more characters using UDF

Post by YasserKhalil »

That's great start. How can I add more?
I mean for exmaple need too to replace [sj] with x
Will I repeat the pattern or it can be included on one pattern?

Code: Select all

Sub Test_MyReplace_UDF()
    Debug.Print MyReplace("abasjbrbtbaaasodddhhjh")
End Sub

Function MyReplace(ByVal txt As String) As String
    Dim sTemp As String
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "[ach]"
        sTemp = Trim(.Replace(txt, "@"))
        .Pattern = "[sj]"
        sTemp = Trim(.Replace(sTemp, "x"))
    End With
    MyReplace = sTemp
End Function

LisaGreen
5StarLounger
Posts: 964
Joined: 08 Nov 2012, 17:54

Re: Replace more characters using UDF

Post by LisaGreen »

Do you mean to replace "sj" or s and j?

There are lots of tutorials and info about regex Expressions out there.
https://www.princeton.edu/~mlovett/refe ... xpressions is a good place to start.
http://www.rlmueller.net/RegularExpressions.htm has a table that can be used as a sort of cheat sheet.

If you enclose in brackets [sj] then that is a "Character class", the key word being character. To match a group of characters use brackets (sj) will match "sj".

A awful lot can be done on a single line but they do get to be quite complex! For example
^address=\/(.*?)\/(\d{1,3}(?:\.\d{1,3}){3})
Which is on https://regex101.com/r/cYcNWK/1/
There is an explanation for each part on that page as well. It's a tester page. You can type in a pattern and a string and get the number of matches. There are quite a few on the internet.

You can use that page to test your pattern against a string.

Hans' idea of creating an array is a good one.

HTH
Lisa

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Replace more characters using UDF

Post by YasserKhalil »

Thanks a lot.
I meant [sj] like you did with [ach] , so x would be replace with x and j replaced with x too.
What I am asking for not the solution but for one question: Can regex do such multiple replacements in one pattern?

LisaGreen
5StarLounger
Posts: 964
Joined: 08 Nov 2012, 17:54

Re: Replace more characters using UDF

Post by LisaGreen »

Gimme an original string, and the result you want and I'll tell you.... but I'm sure the answer is yes because that's what you've done with your code unless I'm not understanding you.

Lisa

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Replace more characters using UDF

Post by YasserKhalil »

yes I have done in my post by repeating the process with another pattern .. I mean I have used two patterns to do such a task, and my question can these patterns be merged in only one pattern?

LisaGreen
5StarLounger
Posts: 964
Joined: 08 Nov 2012, 17:54

Re: Replace more characters using UDF

Post by LisaGreen »

Hi YasYasserKhalil,

I'm pretty sure they can but I repeat my last post request and add.... why do you want the pattern in a single string please?

Lisa

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Replace more characters using UDF

Post by YasserKhalil »

Just as a matter of curios. There is no specific reason. I am trying to explore the different approaches. That's all.

LisaGreen
5StarLounger
Posts: 964
Joined: 08 Nov 2012, 17:54

Re: Replace more characters using UDF

Post by LisaGreen »

ok.... where are thos strings and both patterns please? LOL!!!

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Replace more characters using UDF

Post by YasserKhalil »

Both patterns are included in my post in which I posted the UDF ..

Code: Select all

Sub Test_MyReplace_UDF()
    Debug.Print MyReplace("abasjbrbtbaaasodddhhjh")
End Sub

Function MyReplace(ByVal txt As String) As String
    Dim sTemp As String
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "[ach]"
        sTemp = Trim(.Replace(txt, "@"))
        .Pattern = "[sj]"
        sTemp = Trim(.Replace(sTemp, "x"))
    End With
    MyReplace = sTemp
End Function

LisaGreen
5StarLounger
Posts: 964
Joined: 08 Nov 2012, 17:54

Re: Replace more characters using UDF

Post by LisaGreen »

aaaaannnnd .... the start and end strings???