Hi Joel

REPLACE function in VBA is slightly different from Excel. If we specify the
start in VBA REPLACE then the returning value will only return from the start
position as in example 3. 4th argument is the number of replacements to be
made....

Replace("alphabet", "bet", "hydro") would return "alphahydro"
Replace ("alphabet", "a", "e") would return "elphebet"
Replace("alphabet", "a", "e", 2) would return "lphebet"
Replace("alphabet", "a", "e", 1, 1) would return "elphabet"

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Hi Joel

I too noticed this. Replace do not work as expected when you use the start
argument. If you try Replace(Data,strOld,strNew) it works.

If this post helps click Yes
---------------
Jacob Skaria


"joel" wrote:

I wrote this code but the REPLACE functoin doesn't seem to work properly.
Can anybody see what is wrong?

Sub RemoveComments()

CellData = "This is {comment1} some example {comment2} text"
RowCount = 1
Do While Range("A" & RowCount) < ""
Comments = ""
'CellData = Range("A" & RowCount)
StartChr = 1
Do While InStr(StartChr, CellData, "{") 0
First = InStr(StartChr, CellData, "{") + 1
Last = InStr(StartChr, CellData, "}") - 1
If Last First Then
Length = Last - First + 1
Comment = Mid(CellData, First, Length)
CellData = Replace(expression:=CellData, Find:=Comment, _
Replace:="", Start:=First, Count:=1, compa=vbTextCompare)
'move past closing bracket
msgbox(CellData)
stop
StartChr = First + 2

End If
Loop
Loop

End Sub


"astridc" wrote:

Hello!

I have a column with text in cells; the text in each cell contains one, more
or no comments. All comments are between {}.

I need to find text between { } that exists in a cell, cut it from that cell
and paste it in the adiacent cell in the next column.

for example, I have the following text in cell A1:
"This is {comment1} some example {comment2} text"

I need to cut {comment1} and {comment2} from cell A1 and paste them both in
cell B1, so that in column A I would only have the text without comments and
in column B only the comments.

This would need to go on automatically for the whole column.

thank you all in advace for any suggestions!
AstridC