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 |
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 |
Replace Function using Wildcards
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 |
All times are GMT +1. The time now is 09:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com