SearchBox Week, Day 5

December 19, 2008 by JP · 245 words · Leave a Comment

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:

Sub testm()
Dim str As String
str = "C:\MyFile.xls"
str = Replace(str, "F", "R")
MsgBox str
End Sub

replace

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.

Sub testm2()
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:

substitute

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