Replace Function using Wildcards
Hi Bobm,
I think you need a user defined function:
Function MyReplace(ByVal strFrom As String) As String
Dim strResult As String
Dim intPos As Integer
intPos = InStr(1, strFrom, ")", vbTextCompare)
Do While intPos 0
If intPos 2 Then
strResult = strResult & Left(strFrom, intPos - 2) & ";"
Else
strResult = strResult & ";"
End If
strFrom = Mid(strFrom, intPos + 1)
intPos = InStr(1, strFrom, ")", vbTextCompare)
Loop
MyReplace = strResult & strFrom
End Function
HTH,
Executor.
bobm wrote:
Hello,
I am using the VBA replace function and wondering if I can include wildcard
to find a text.
For example my string is...
"a) Akli Amine Mohamed b) Killech Shamir c) Kali Sami Elias"
and i want to replace the letter and closing brackets with a semi colon so
it looks like this...
";Akli Amine Mohamed; Killech Shamir; Kali Sami Elias"
i tried the replace function but could only replace the closing brackets,
not the letter to the left.
Replace("a) Akli Amine Mohamed b) Killech Shamir c) Kali Sami Elias", ")",
";")
and output was...
a; Akli Amine Mohamed b; Killech Shamir c; Kali Sami Elias
my question is, can I use a wildcard in the replace function so the closing
bracket and letter to the left are replaced by the semi colon.
appreciate help and perhaps a better way to do this.
thanks,
bobm
|