SearchBox Week, Day 5
December 19, 2008 • JP • No Comments • Rate This Article
For the final post of SearchBox Week, I've chosen the following search term:
"vba replace char in filename"
Replace Function
To replace a character inside a string, all we need to do is use the Replace function to swap out the old character for the new one. Here is a simple sub that replaces a single character:
Dim str As String
str = "C:\MyFile.xls"
str = Replace(str, "F", "R")
MsgBox str
End Sub
Substitute Function
For more robust operation, the Substitute function will replace the nth occurrence of any single string literal character with any other string literal character.
Dim str As String
str = "My dog has fleas and Fido is my Faithful dog"
str = WorksheetFunction.Substitute(str, "F", "R", 2)
MsgBox str
End Sub
This results in the following message box:
For help with either of these functions, press Shift-F3 on the worksheet for the function dialog box. Type 'replace' or 'substitute' for a list of function arguments and real-time calculation of results.
I hope you enjoyed this series of posts as much as I enjoyed writing them. If you have any requests, let me know via the Contact page.
Thanks,
JP
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: SearchBox Week, Day 4
Next Post: Code Contest Update
Speak Your Mind
Tell us what you're thinking...Certain comments (including first-time comments) are subject to moderation and will not appear immediately. Please view the Comment Policy for more information. To post VBA code in your comment, use tags like this: [cc lang='vb']Code goes here[/cc].