VB and VBA Users Source Code: Search and Replace Text
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Search and Replace Text
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Wednesday, March 21, 2001
Hits:
744
Category:
Visual Basic General
Article:
To search and replace text in a variable use the following (please read the notes for guidance on how to use this routine). 'Purpose : Searches and replaces all instances of a string within a variable. 'Inputs : sText The string to search. ' sReplaceThis The string to replace. ' sWithThis The replacement string. ' [lNumReplacements] Returns the number of times a replacement was made. 'Outputs : N/A 'Author : Andrew Baker 'Date : 03/09/2000 14:17 'Notes : This routines output parameters are passed in ByRef for speed and hence it should be called: ' Dim sOutputText as String 'Create a variable to hold the value in ' sOutputText = "aaaaaa" ' SearchAndReplace sOutputText, "a", "b" ' Debug.Print sOutputText Sub SearchAndReplace(ByRef sText As String, ByVal sReplaceThis As String, ByVal sWithThis As String, Optional ByRef lNumReplacements As Long) Dim lPos As Long, lLenOriginal As Long, lLenReplace As Long lLenOriginal = Len(sReplaceThis) lLenReplace = Len(sWithThis) If lLenOriginal Then lPos = InStr(1, sText, sReplaceThis) Do While lPos > 0 lNumReplacements = lNumReplacements + 1 If lLenOriginal = lLenReplace Then Mid$(sText, lPos, Len(sReplaceThis)) = sWithThis Else sText = Left$(sText, lPos - 1) & sWithThis & Mid$(sText, lPos + lLenOriginal) End If lPos = InStr(lPos + lLenOriginal, sText, sReplaceThis) Loop End If End Sub
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
