Find & Replace forcing Scientific Notation format

Question

I would like Excel to stop substituting a perfectly fine string that contains an E with its scientific notation equivalent when doing a Find & Replace with VBA.

I have tried forcing it by using ReplaceFormat.NumberFormat = "@" to no avail.

This is the code that illustrates my problem:

Sub testsub()

ActiveSheet.Columns("A:A").NumberFormat = "@"

fnd = "2"
rplc = "39456E10"

Range("A1").Value = fnd
Range("A2").Value = rplc

Application.ReplaceFormat.NumberFormat = "@"

  ActiveSheet.Cells.Replace what:=fnd, Replacement:=rplc, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=True
End Sub

And this is the outcome

Answer 1

Well depending on how many cells you got in your Range object. You could opt for a Application.Substitute method.

I think as long as you use a method/function that explicitly returns a String data type, you'll be good. Since Substitute does exactly that you could try something like below:

Sub Test
    Range("A1:A2").Value = Application.Substitute(Range("A1:A2"), "2", "39456E10")
End Sub

Working on the assumption your Range is already formatted as "@". If not, you might want to include that in the code. This would also work for a larger-scale 2D-array, e.g.: A1:Z1000.

For example:

>

---

I figured the same applies for example RegEx.Replace method and the Replace function (not mistaken with the Replace method) for example. Which instead of the above would need iteration.

---

Just a fun fact, with Application.Substitute we could also tell VBA which occurence of our searchvalue we would like to replace. For example to only replace the 2nd occurence of the number 2:

Range("A1:A4") = Application.Substitute(Range("A1:A4"), "2", "39456E10", 2)

---

However, as per @Pᴇʜ his answer, the apostrophe is there for a reason... =). Therefor, that would be my personal preference (and should be faster).

Answer 2

Just put an apostrophe ' infront of your string to ensure it is considered as string when replacing:

rplc = "'39456E10"

The apostrophe won't show in the Excel cell.