VBA not maintaining text formats

[shane_aldrich]

OK...this is a weird one. I have VBA code that fills down some formulas. I noticed that it keeps changing the text formatting on a few of them

In Column G I have a list of codes

4SEPT</SPAN>

278E5</SPAN>

298E8</SPAN>

46E47</SPAN>

78E54</SPAN>

25E64</SPAN>

29E88</SPAN>

88E99</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

These codes are in text format

In column Y I use a formula =IF($X4="","",MID(G4,1,5))


For those codes above since X is not blank, it should report the same code...instead it returns

4-Sep</SPAN>

2.78E+07</SPAN>

2.98E+10</SPAN>

4.60E+48</SPAN>

7.80E+55</SPAN>

2.50E+65</SPAN>

2.90E+89</SPAN>

8.80E+100</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

Is there a way to force it not to reformat?

Thansk,

 

[poolhall]

You can use a leading apostrophe: '4SEPT. This will prevent your values from autoformatting.