Fun with functions…. Improving Excels native REPLACE() function

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!

Replace_text() function…

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: –