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?

 

[Joe4]

Here is a work around, though I am sure there must be better methods.

Make sure the all cells the entries are in are in text format.

Now, to fix entries like "05/17" (to "0517"), insert a column and enter this formula:

=SUBSTITUTE(A1,"/","") assuming "05/17" is in cell A1.

Likewise, to fix "6666-8888-7777-9999-9999", use:

=SUBSTITUTE(A2,"-","")

 

[just1joe]

Thanks. I'll use that on the next round. I ended up concatinating multiple MID functions together to get the same result. Sunstitute will be better because it is not position dependent like MID.

- Joe

 

[Mark W.]

Are you sure the Text number format was applied to your hyphenated values?

 

[just1joe]

Yes, It was set upon importing and I double checked prior to the replace.

 

[Joe4]

Mark W.,

Check it out for yourself. I find it to be quite bizarre Excel behavior, but if you have a cell formatted as text with "6666-8888-7777-9999-9999 " in it, and you do a Find & Replace "-" with nothing, it changes it to "6.66689e+19", even though it is still supposedly formatted as Text!

I'm not sure why that happens, but apparently SUBSTITUTE works where Find & Replace doesn't.

 

[Mark W.]

Hmmm, that appears to be the case! The only way to avoid this behavior is to preface the value with an apostrophe.

 

[just1joe]

Yes, That forces the cell to be treated as text and as long as everything you are doing is in Excel it works fine. Unfortunately it causes problems when trying to import the data into other systems. They don't know to disregard the apostrophie.

 

[Mark W.]

How are you importing this data? Have you considered "reading" this file using an ODBC driver?

 

[just1joe]

The file is an export from quickbooks in a tab delimited format. There is a 3rd party ODBC driver for QB that someone is selling, but it's too expensive for the minimal use we would have for it.

The issue really isn't in inporting the data as it is in manipulating the data once in Excel and then exporting it. The workarounds have been sufficient to address my immediate need. Thanks a lot for everyone's assistance.