Problem with Replace function

[mcomp72]

I am trying to use the Replace function within VBA to remove a specific character from a string, at a certain position within the string. I don't think I am understanding how Replace works, but I've read a couple of different websites that talk about it, and it seems like I'm doing it right.

What I'm trying to do is remove a "$" from a formula in certain instances. The "$" will never be in the same position every time, so I have code that figures out where it is. But when I try to remove it from the string, instead of it removing only that one character, it removes it and everything to the left of it.

I condensed my code down so you could see what I'm trying to do.

Sub ReplaceACharacter()

Dim MyFormula As String
Dim FirstDollarSignPosition As Long
Dim SecondDollarSignPosition As Long

MyFormula = "=$N$36+'Page 1A'!$AM$61"

FirstDollarSignPosition = 2
SecondDollarSignPosition = 4

MyFormula = Replace(MyFormula, "$", "", SecondDollarSignPosition, 1)
MyFormula = Replace(MyFormula, "$", "", FirstDollarSignPosition, 1)

MsgBox MyFormula

End Sub

I want the result of MyFormula to be: "=N36+'Page 1A'!$AM$61"

But when I run this sub, I get this: "6+'Page 1A'!AM$61"

I can't figure out what I'm doing wrong.

Is there a better way to remove one character from a string if you know the position of that character within the string?

 

[BSALV]

MyFormula = Replace(MyFormula, "$", "", , 2)               'just the first 2 $-signs

 

[mcomp72]

MyFormula = Replace(MyFormula, "$", "", , 2)               'just the first 2 $-signs

Thanks, that works!

But I'm curious... if I wanted to remove a character from a string at a specific position in the string, how would I do that? I am not understanding how REPLACE works in terms of the starting position within the string, and I would love to understand it. I will most likely need to use this later in my workbook.

 

[BSALV]

Replace ( string1, find, replacement, [start, [count, [compare]]] )
4th parameter = startposition, 5th = number of replacements.
I omitted the 4th (equal to start=1) and the 5th was 2, thus 2 replacements.
sometimes you have to do it in several steps, for example delete the 3rd $

    MyFormula = "=$N$36+'Page 1A'!$AM$61"
     s = Replace(MyFormula, "$", "|", , 2)                      'replace the first 2 $-signs with a tempory "|"
     s = Replace(s, "$", "", , 1)                               'delete the 3rd, who is now the first
     s = Replace(s, "|", "$")                                   'replace that dummy "|" again with $
'or in 1 combined line
     s = Replace(Replace(Replace(MyFormula, "$", "|", , 2), "$", "", , 1), "|", "$")

other option, find the position of the "+" character with "INSTR" and replace the 1st $ starting at that position

 

[Rick Rothstein]

You could call out to Excel's REPLACE function as it allows you to replace by a specified position within the text. For example, let's say you wanted to remove the $ sign located at the 16 character postion...

S = WorksheetFunction.Replace(s, 16, 1, "")

 

[BSALV]

too late to add to the previous post

   i = InStr(MyFormula, "+")
     If i > 0 Then s = Left(MyFormula, i - 1) & Replace(MyFormula, "$", "", i, 1)

 

[mcomp72]

4th parameter = startposition, 5th = number of replacements.

You say the 4th parameter is the start position. So, if I were to do this, should it do the replace starting at position 4, and do it 1 time?

MyFormula = Replace(MyFormula, "$", "", 4, 1)

Because when I try this, it deletes the first 4 characters in the string, instead of deleting only the "$". I don't understand why this is happening.

 

[BSALV]

i was too late with my answer #6
startposition is the x-th character, not the x-th appearance of "$"
And indeed, all the characters before x disappear.

 

[mcomp72]

You could call out to Excel's REPLACE function as it allows you to replace by a specified position within the text. For example, let's say you wanted to remove the $ sign located at the 16 character postion...

S = WorksheetFunction.Replace(s, 16, 1, "")

Thanks, Rick. That did the trick!