+ Reply to Thread
Results 1 to 2 of 2

Undo Excel Automatically Formatting Cells as Numbers

  1. #1
    Registered User
    Join Date
    05-19-2015
    Location
    Houston, TX
    MS-Off Ver
    Pro 2013
    Posts
    30

    Undo Excel Automatically Formatting Cells as Numbers

    I'm working with a program that exports data to a text tab delimited file. When I need to edit large sections of data it is easier to export the database to the text file and open it in Excel to manipulate the data.

    The problem is with the column of data that contains number addresses. The program uses the format #,#####. As soon as I open the file in Excel it sees this column data as a Number, and inserts commas since the "Use 1,000 separator" option is selected. When the file is imported back into the program this causes errors since the number before and the numbers after the comma have specific meanings.

    My workaround is to convert the column to text, and then manually insert the required comma after the first digit in each cell. Doing this one by one takes a long time. I then save the file and import it back into the program. This works, but if I go and open the file in Excel again, I lose all the formatting I just did.

    For example:

    Address in program is listed as: 1,23456
    When the export file is opened in Excel, the address is automatically changed to: 123,456

    I don't think there's a way to prevent Excel from changing the data when the file is opened, I'm just looking for a quick way to correct the data instead of manually correcting each cell one by one.

    I've been looking in the Excel help files for a function that can insert a character (comma in this instance) after a specified position in a text string (after the first character in this instance), but I haven't been able to find one. Maybe this is better solved with a macro?

    Any help is appreciated.

    Thanks

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Undo Excel Automatically Formatting Cells as Numbers

    Quote Originally Posted by axegrynder View Post
    I don't think there's a way to prevent Excel from changing the data when the file is opened
    There is a way. Use the Import Data feature (on the menu Data\ Import External Data\ Import Data). Select your text file and then you can define each column data type. Use Text for your Address column.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 11-05-2014, 03:34 PM
  2. Replies: 0
    Last Post: 02-29-2012, 01:40 PM
  3. [SOLVED] Undo converting numbers to dates
    By zbor in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-20-2011, 09:30 AM
  4. Cells automatically formatting
    By jclvpjm in forum Excel General
    Replies: 1
    Last Post: 07-31-2009, 01:18 PM
  5. Replies: 1
    Last Post: 07-30-2009, 04:26 PM
  6. VBA Undo & Excel's built-in Undo
    By dlh in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-30-2007, 05:02 PM
  7. [SOLVED] Can numbers be typed to automatically be negative in cells?
    By finnplan in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 12-08-2005, 06:30 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1