Replace functions calculates scientific numbers in text field

Hello all,

I have a document that imports data from a text file. There is one column that is imported as text that contains ID strings such as 11A-1, 45D-6, 12E-1, etc.

When I use excels built in replace function to change anything within the string, it calculates the text field as a scientific number.

For Example:

Find "11" and replace with "12"

Original values Stored as Text

11E-1
11E-2
11E-3
11E-4
11E-5
11E-6

Desired Result, stored as text:

12E-1
12E-2
12E-3
12E-4
12E-5
12E-6


Replace with Text Format result:

1.2
0.12
0.012
0.0012
0.00012
0.000012

Replace with No Format result:

1.20E+00
1.20E-01
1.20E-02
1.20E-03
1.20E-04
1.20E-05


I don't understand why when replacing a text value in a text formatted cell, excel still calculated the value as a number.....

All in all very Frustrating. Is there a workaround somewhere? Haven't been able to find anything.

Thanks,

It's the E that is causing excel to assume you want scientific notation.

When you use find and replace, type a single quote before the Replace with value. '12

Regards,

David

Thanks for your help friends!