Inserting into an Excel VBA string

Question

In JAVA or C++, we can do something along the line of myString.insert(position, word). Is there a way we can do the same in Excel VBA's string? In my worksheet, I have a string looks like this: 01 / 01 / 995, I wants to insert a 1 into the year, so make it 01 / 01 / 1995.

Dim test_date As String
test_date = "01 / 25 / 995"
test_date = Mid(test_date, 1, 10) & "1" & Mid(test_date, 11, 4)

Is there another easier / more elegant way to do it?

Answer 1

Here is my fifty cents for this question.

First of all, I need to give credit to WONG, Ming Fung from wmfexel where I found this trick.

Unlike the VBA Replace function who asks for the String to replace, the Replace Worksheet function only asks for the position in the Origin String and the number of characters to overwrite.

By "abusing" this overwrite parameter, setting it to 0 allows us to add a given string at a specific position in an Orignin string by replacing 0 characters of it.

Here it how it works :

Dim test_date As String
test_date = "01 / 25 / 995"
test_date = Worksheetfunction.Replace(test_date, 11, 0, "1")
'Now test_date = "01 / 25 / 1995" as we added "1" at the 11th position in it

As you can see, it's really convenient and readable. For those who are picky and thinks the name Replace is just confusing, Wrap it in an Insert function and you'll be all done ;).

Answer 2

I dont think there is a cleaner way of doing it so you could just wrap it up in a function. Another way of doing it would be with replace, but it's not any cleaner.

Function Insert(source As String, str As String, i As Integer) As String
    Insert = Replace(source, tmp, str & Right(source, Len(source)-i))
End Function 

or just modify what you have

Function Insert(source As String, str As String, i As Integer) As String
    Insert = Mid(source, 1, i) & str & Mid(source, i+1, Len(source)-i)
End Function 

Answer 3

This a version of the accepted answer, with added tests and working the way I would expect it to work:

Function Insert(original As String, added As String, pos As Long) As String

    If pos < 1 Then pos = 1
    If Len(original) < pos Then pos = Len(original) + 1

    Insert = Mid(original, 1, pos - 1) _
                        & added _
                        & Mid(original, pos, Len(original) - pos + 1)

End Function

The tests pass:

Public Sub TestMe()

    Debug.Print Insert("abcd", "ff", 0) = "ffabcd"
    Debug.Print Insert("abcd", "ff", 1) = "ffabcd"
    Debug.Print Insert("abcd", "ff", 2) = "affbcd"
    Debug.Print Insert("abcd", "ff", 3) = "abffcd"
    Debug.Print Insert("abcd", "ff", 4) = "abcffd"
    Debug.Print Insert("abcd", "ff", 100) = "abcdff"

End Sub