Autoformatting/VBA to fix?

[Realtreegirl75]

There is a cell in Excel that I need to type 22E113. This autoformats to 2.20E+114 but I need it to stay the same. I know I can type a ' in front of it each time but I will be distributing this sheet to people who will not remember that lol. Is there a way to write code into the worksheet that will add the ' in front of it each time so that it will stay the same as what anyone is typing in? I found this code on another site that kind of works but doesn't stop the text from autoformatting. I've tried to tweak it but I'm not having much luck...Any help would be so appreciated!!

Sub AppendToExistingOnLeft()
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = "CN- " & c.Value
Next
End Sub

 

[xlhelp15]

Hi - Have you tried the simple way of turning it off ? Excel Options - Formulas - under "Error checking rules" - un-check "Numbers formatted as text or preceded by an apostrophe".

It can allow you to type the numbers with text without converting it.

 

[Joe4]

If you simply change the cell format to "Text" before typing these values in, Excel will not convert them.

 

[Realtreegirl75]

I feel like an absolute dope for not thinking of that! Thank you @Joe4 that made my life much easier than I thought it was going to be!!

 

[Joe4]

You are welcome.

Yep, the "Text" format is the key if you want Excel to keep enter every single character you enter and not perform any automatic conversions or changes to it.