Turn Off Excel Autoformat on replace

[just1joe]

Hello,

I have data that I'm trying to cleanse. I imported it into excel with each column defined as text format. This part is fine and worked the way I needed it to. I am trying to remove - / \ characters from some of the columns.

When I use the replace function 05/17 becomes 517 when I need 0517 and 6666-8888-7777-9999-9999 dsiplays as 6.66689e+19. If I then save the file as text, it saves the 6.66689e+19 value.

Is there a way to turn the autoformat feature off? Or is there another method for removing these characters other than replce?

 

[Mark W.]

Right, but if you used Excel's Text ODBC driver you could produce some SQL that would clean your data before it ever reached Excel.