VBA – Insert Into String

Posted on October 15, 2022 by

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)

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"

1	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"

1	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

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! 🙂