Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Turn Off Excel Autoformat on replace

This is a discussion on Turn Off Excel Autoformat on replace within the Excel Questions forums, part of the Question Forums category; Hello, I have data that I'm trying to cleanse. I imported it into excel with each column defined as text ...

  1. #1
    Board Regular
    Join Date
    Apr 2003
    Posts
    79

    Default Turn Off Excel Autoformat on replace

    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?

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    25,871

    Default Re: Turn Off Excel Autoformat on replace

    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,"-","")
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Apr 2003
    Posts
    79

    Default Re: Turn Off Excel Autoformat on replace

    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

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default Re: Turn Off Excel Autoformat on replace

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

  5. #5
    Board Regular
    Join Date
    Apr 2003
    Posts
    79

    Default Re: Turn Off Excel Autoformat on replace

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

  6. #6
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    25,871

    Default Re: Turn Off Excel Autoformat on replace

    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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default Re: Turn Off Excel Autoformat on replace

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

  8. #8
    Board Regular
    Join Date
    Apr 2003
    Posts
    79

    Default Re: Turn Off Excel Autoformat on replace

    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.

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default Re: Turn Off Excel Autoformat on replace

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

  10. #10
    Board Regular
    Join Date
    Apr 2003
    Posts
    79

    Default Re: Turn Off Excel Autoformat on replace

    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.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com