ms excel find and replace @ symbol results in broken formula

I'm trying to search and replace in excel, the column is formatted as 'Text'.

Find: [@

replace with: @

Whenever this finds a match at the start of a cell i.e the cell contents start with [@
and tries to replace that with @ the result is an error 'This function is not valid'

I guess that since the @ operator is for references, this is causing the cell to be interpreted differently (not as text anymore)

How do I make this replacement work?

Copy/paste into another program is not a good option because some of the cells contain line-breaks.

Answer

To my surprise, Excel knows at least 4 different methods to replace a substring.

All of them can handle a leading @ in the way you want it. Except the first method which you are using when opening the search & replace dialog.

The @ is the text placeholder.. If you enter a numeric value like 123 in a cell and set the custom format to @, the cell will be formatted as text (Alignment: right » left).

---

These examples will all replace the b in cell A1' value abc with 123. Result is always a123c.

1. Range.Replace() (same as CTRL+H dialog)

    [A1].Replace "b", "123", xlPart

Syntax: Range.Replace( What, Replacement, [LookAt], [SearchOrder], [MatchCase], [SearchFormat], [ReplaceFormat] )

2. WorksheetFunction.Replace()

    [A1] = WorksheetFunction.Replace([A1], InStr([A1], "b"), Len("b"), "123")

Syntax: WorksheetFunction.Replace( old_text, start, number_of_chars, new_text )

3. WorksheetFunction.Substitute()

    [A1] = WorksheetFunction.Substitute([A1], "b", "123")

Syntax: WorksheetFunction.Substitute( text, old_text, new_text, [nth_appearance] )

4. Replace()

    [A1] = Replace([A1], "b", "123")

Syntax: Replace( Expression, Find, Replace, [Start, [Count, [Compare]]] )

---

To answer Loopo's question. You should use the last method since it can handle @ at the beginning of a cell value. I made it a little bit more comfortable with 2 input boxes.

Sub ReplaceFunction()
    strFind = InputBox("Find what")
    strReplace = InputBox("Replace with")
    For Each cell In Selection
        cell.Value = Replace(cell.Value, strFind, strReplace)
    Next
End Sub