You know what annoys me about Excels built in REPLACE() function, it does not work like the equivalent VBA function Replace() function. It’s that simple and that is enough of a reason to think twice about using it if you just want to replace a sub-string of a string with another string. You can do it of course, but it’s overly complicated… there is an easier way.
What exactly am I talking about here?
Well, the built in Excel function requires you to establish where the start point of the text you want to replace is located in your original string and some other rubbish..
That means writing some more (kind of annoying) formulas to establish these things if you just want to replace a specific term with another term if that term could be anywhere in your string… i.e. you just want the damn thing to perform just like the equivalent of the VBA Replace() function. Why are they not the same! What should be simple like it is in VBA is not simple.
When all you really want it to do is say replace AAA in A3AAA890 with BBB to end up with A3BBB890. Well, that’s kind of annoying using the REPLACE() function as it just doesn’t have this functionality out of the box without writing a whole lot more cryptic formulas to find the position of AAA (if it even exists at all), and the length of AAA.
So today I was wanting to rename a whole bunch of files using Excel, essentially deleting a common string from the filename if it existed. I already had imported the original filenames in Excel, and had some code to rename the files. So I started out manually removing the particular string from the filenames in each cell…. after a few cells I thought this is going to take a f#$5ing while, bugger this. There must be a better way. Well VBA to the rescue (naturally) as the VBA Replace() function does exactly this…. $profit$
So, I wrote a user defined function (UDF) in literally minutes which saved me a whole bucketload of time, and I can reuse it again in the future with no effort!
If you have a use case for wanting to do a replace like the VBA function, then this simply wraps up the VBA function. It is also written in a way that it will work on entire ranges and returns a dynamic array, because why not.
The function also allows for replacement of parts of numbers (by converting to a string and converting back to a number). Potentially useful to someone, I can’t think of a use case, but I did it anyway (maybe for renaming/renumbering parts numbers or something). Thats how we roll… adding functionality that has zero practical use!
Copy/paste into your workbook and get replacing.
Function Replace_text(text_string As Variant, text_to_replace As String, text_to_insert As String, Optional skip_non_strings As Boolean = True) 'function to replace text in string 'convert range to array for processing text_string = arr_convert_rng_to_array(text_string) Dim row_total As Long Dim col_total As Long Dim i As Long Dim j As Long Dim convert_flag As Boolean row_total = UBound(text_string, 1) col_total = UBound(text_string, 2) convert_flag = False For i = 1 To row_total For j = 1 To col_total 'convert to string if a number is provided If Not skip_non_strings Then If VBA.IsNumeric(text_string(i, j)) Then text_string(i, j) = CStr(text_string(i, j)) 'set flag convert_flag = True End If End If 'replace text if string If Application.IsText(text_string(i, j)) Then text_string(i, j) = Replace(Trim(text_string(i, j)), text_to_replace, text_to_insert) 'convert back to number if string was originally a number If convert_flag Then text_string(i, j) = CDbl(text_string(i, j)) 'reset flag convert_flag = False End If End If Next j Next i 'return results Replace_text = text_string End Function Private Function arr_convert_rng_to_array(arr As Variant) 'function to convert ranges to arrays Dim temp As Variant 'if already an array exit function returning same array If IsArray(arr) Then arr_convert_rng_to_array = arr Exit Function End If 'convert range input into array If arr.Columns.count = 1 And arr.Rows.count = 1 Then temp = arr.Value2 ReDim arr(1 To 1, 1 To 1) arr(1, 1) = temp arr_convert_rng_to_array = arr Else arr_convert_rng_to_array = arr.Value2 End If End Function
Usage should be fairly obvious: –