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?
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
WorksheetFunction.Replace that works slightly differently from VBA.Replace. I give an exemple in my answer belowThis 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
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 ;).
Public Function stringReplace(source As String, _
sought As String, _
substitute As String _
) As String
Const THIS_PROC As String = "_stringReplace"
Const THIS_MOD As String = "Module1" '--> mod declares
Const isDBUG As Boolean = True 'usually in module declarations
Dim tempa_ As Variant 'the _ is there to say: hey! I'm an array_
Dim temps As String
On Error Goto EH 'local and normal
If izDBUG Then On Error Goto DBUG 'instant gratification
tempa_ = Split(source, sought)
'handy spot to perform select manipulations
temps = Join(tempa_, substitute)
EX:
Let stringReplace = temps
DoEvents
Exit Function
EH:
Let temps = "ERROR!"
Goto EX
DBUG:
Debug.Print (THIS_MOD & THIS_PROC), Err.Number, Err.Description
Debug.Assert NOT izDBUG : Resume
Mid$notMid, the former is a string function, the later a variant function. string functions are faster