Web Development

Ask something
Discussion Locked

Excel/VBA - Manipulating Strings

0 helpful
3 replies

by cbdepadua

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I want to take a string, ex. ("Workbook1!$U$56"), and change that "U" to a "Z". Then I want to append the new string onto the end of the old string. Can any one help me?

3 replies

by nikki96

Sure.

This script replaces anything between the two dollar signs with whatever you specify as "replacement". The variable str1 is hard-coded here, but you could easily make it a table cell or some such.

Tested in Excel 2000
HTH
Nikki

Public Sub changeIt()
Dim str1, str2 As String
Dim i1, i2 As Integer
Dim replacement As String

replacement = "Z"

str1 = "Workbook1!$U$56"
str2 = ""
MsgBox ("string1 is " & str1 & vbLf & "string2 is " & str2)

i1 = InStr(str1, "$")
i2 = InStrRev(str1, "$")

str2 = str1 & Left(str1, i1) & replacement & Right(str1, Len(str1) - i2 + 1)
MsgBox ("string1 is " & str1 & vbLf & "string2 is " & str2)
End Sub

This discussion is closed to new replies.