String Insertion Function?

[dwessell]

I'm using VBA in Excel. And I have a string similar to "D99A". I would like to convert that string to "D099A". But I'm not finding a string insertion function, that let's me insert a string at pos x.. Can someone point me in the right direction?

Thanks
David

 

[SkipVought]

Hi,

"I have a string..."

In a CELL?

In a CONTROL?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ettienne]

Replace(expression, find, replace[, start[, count[, compare]]])

 

[dwessell]

Initially in a cell, but I'm moving the contents of that cell into a string variable.

 

[PHV]

yourString = "D99A"
newString = Left(yourString, 1) & "0" & Mid(yourString, 2)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fumei]

Hey Skip....does it matter? A string manipulation is a string manipulation. To be manipulated it has to BE a string. Maybe I am missing something, but are not

Dim strYadda As String

strYadda = Excel cell text
strYadda = Word range.text
strYadda = userform.control.text

essentially identical, in terms of manipulating strYadda? It is a string.

ettienne, what would you use for the Find? Depending on what is actually required, it would be easy enough to use "99", as in:

Dim strIn As String
strIn = "D99A"
strIn = Replace(strIn, "99", "099", 1)

strIn is now "D099A". However, this is a hardcoded type of action. It is NOT an insertion at a specific point.

It replaces "99" with "099".

It does NOT insert "0" at the second character (a specific position), which is what the OP asked about.

"that let's me insert a string at pos x.. "

I do not know of a direct INSERT, but you can rebuild the string. To insert a "0" at character position 2:

Dim strIn As String
strIn = "D99A"

strIn = Left(strIn, 1) & "0" & _
   Right(strIn, Len(strIn) - 1)

It builds the string using the first character -
Left(strIn, 1)

the character you want to insert - "0"

and the rest of the string - Right(strIn, Len(strIn) - 1)

You can make a Function that will insert a string at a specific variable position, like so:

Function InsertAt(strIn As String, _
   strInsert As String, _
   j As Long) As String
   
InsertAt = Left(strIn, j - 1) & strInsert & _
   Right(strIn, Len(strIn) - (j - 1))
End Function


Sub testMe()
Dim StartString As String
Dim InsertedString As String
Dim Position As Long

StartString = "D99A"

InsertedString = InputBox("Enter the string to insert.")
Position = InputBox("Enter the position - from the left - " & _
   "for the inserted string.")

MsgBox InsertAt(StartString, InsertedString, Position)
End Sub

Obviously, the StartString could also be anything, taken from another inputbox, or a cell, or a previous Found piece of text (using .Find)...whatever.

Also, hopefully obvious, there should be proper error trapping! j can not be greater than the Len of the string.

faq219-2884

Gerry
My paintings and sculpture

 

[SkipVought]

Gerry,

Makes not a dime's worth of difference.

But at the time I was doing this 'drive-by', I was thinking about the SelStart, SelText, SelLength Forms properties.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

Gerry, why not simply this in your function ?
InsertAt = Left(strIn, j - 1) & strInsert & Mid(strIn, j)

 

[fumei]

That is why we love you PHV.

Why not indeed. Much better. I guess I was having my own drive-by...whatever the heck that means.

faq219-2884

Gerry
My paintings and sculpture

 

[SkipVought]

"Drive by"

While yer answerin' one question, a whole passel of 'em are commin' atch.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ettienne]

I have a string similar to "D99A". I would like to convert that string to "D099A"

In the simplest form:
Dim Somestring as String
Somestring = "D99A"
Somestring = Replace(Somestring, "D99A", "D099A")

or you can get creative and replace D with D0, D9 with D09, 99 with 099, etc.

It all depends what the OP wants to do, the post is a bit vague, or not.
Otherwise Left, Mid and Right functions will do the trick.