Replace Function using Wildcards

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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