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

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

bobm,
May use a Regular Expression:
http://visualbasic.about.com/od/usin...l/blregexa.htm

Or something based on: <pseudo code
MyArray=Split(YourString, ") ")
Each element, except first, replace last 2 chars with ", "
Join on " "

NickHK

"bobm" wrote in message
...

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