VBA – Insert Into String

Inserting into string in Excel should be a trivial task, but it always makes sense to have something prepared in the boilerplate, before you start working.

*This is my 2. article with that cat, it is becoming famous*

Ok, why is it important to have a nice function, if something like this already does the job:

    InsertIntoString = Mid(originalString, 1, positionToAdd - 1) _
                        & addedString _
                        & Mid(originalString, positionToAdd, Len(originalString) - positionToAdd + 1)

Good question, and the answer is because we will not always remember to add some nice corner cases. E.g. what would happen if we want to insert on a position, that is way bigger than the length of the string like this:

InsertIntoString("vitosh", "academy", 1000) '"vitoshacademy"

Or if we somehow passed a parameter, and it turned out to insert a string at the 0-th or a negative position, as it somehow makes sense for the author?

InsertIntoString("academy", "vit", -6) '"vitacademy"

Ok, without further delay, the code handling these two cornercases is here:

Function InsertIntoString(originalString As String, addedString As String, positionToAdd As Long) As String

    If positionToAdd < 1 Then positionToAdd = 1
    If Len(originalString) < positionToAdd Then positionToAdd = Len(originalString) + 1

    InsertIntoString = Mid(originalString, 1, positionToAdd - 1) _
                        & addedString _
                        & Mid(originalString, positionToAdd, Len(originalString) - positionToAdd + 1)

End Function

Public Sub TestInsertIntoString()

    Debug.Print InsertIntoString("vitosh", "academy", 1000) = "vitoshacademy"
    Debug.Print InsertIntoString("academy", "vit", -6) = "vitacademy"
    Debug.Print InsertIntoString("vitacademy", "osh", 4) = "vitoshacademy"
    Debug.Print InsertIntoString("abcd", "ff", 3) = "abffcd"
    Debug.Print InsertIntoString("abcd", "ff", 4) = "abcffd"
    Debug.Print InsertIntoString("abcd", "ff", 100) = "abcdff"

End Sub

And if you feel like improving my boilerplate, you might consider doing it here: https://github.com/Vitosh/VBA_personal/blob/master/Formatting/InsertIntoString.vb

Enjoy! 🙂