VBA Replace first character in any cell within a Range if first character is "="

[SgtSpud]

Objective is to pull the contents of a folder of text files into Excel for DB overlay. I've written the VBA code and it has been working well. But I've found some files start with an equals sign "=" which causes the code to ignore certain SUBSTITUTE commands, and for Excel to display #NAME?. I have coded a SUBSTITUTE to convert all "=" to "»" which overcomes the first part. I now need to (1) where "»" is the first character in the cell in column C, Replace with a "'=" and then (2) replace all other "»" with "=" as some text is URL's which validly contains "=" symbols. The below code achieves this, but replaces the entire cell contents with just "'=" whereas I need to keep the cell contents and just replace "»" with "'=" where "»" is the first character in the cell. Please help.

EqualsSign = "»"
For X = 1 To Range("C" & Rows.Count).End(xlUp).Row
If Replace(EqualsSign, Left(Range("C" & X).Value, 1), "") <> EqualsSign Then Range("C" & X).Value = "'="
Next

Columns("C:C").Select
Selection.Replace What:="»", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

 

[johnnyL]

Don't the last 4 lines of your code do what you want? Why do you have the first 4 lines of code?

 

[Akuini]

@SgtSpud
Welcome to MrExcel
Try this:

EqualsSign = "»"
For X = 1 To Range("C" & Rows.Count).End(xlUp).Row
    With Range("C" & X)
        If Left(.Value, 1) = EqualsSign Then .Value = Replace(.Value, EqualsSign, "'=", , 1)
    End With
Next

Columns("C:C").Select
Selection.Replace What:="»", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

 

[SgtSpud]

@SgtSpud
Welcome to MrExcel
Try this:

EqualsSign = "»"
For X = 1 To Range("C" & Rows.Count).End(xlUp).Row
    With Range("C" & X)
        If Left(.Value, 1) = EqualsSign Then .Value = Replace(.Value, EqualsSign, "'=", , 1)
    End With
Next

Columns("C:C").Select
Selection.Replace What:="»", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

Thank you SO much, this is perfect and exactly what I needed; so very grateful Sir.

 

[SgtSpud]

Don't the last 4 lines of your code do what you want? Why do you have the first 4 lines of code?

Thank you for replying. No not quite, the second part converts all "»" to "=" which would cause the #NAME? error in Excel if any cell had "=" as the first character in the cell. For this reason I needed any "=" which fell as the first character in the cell to be replaced by "'=" (i.e. apostrophe equals), and then all remaining "»" to be replaced by "=". The solution provided by Akuini was perfect.

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.