+ Reply to Thread
Results 1 to 8 of 8

Fix Formatting In Column A

  1. 06-24-2023, 02:58 PM #1
    jayfin
    jayfin is offline
    Registered User
    Join Date
    10-21-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    94

    Fix Formatting In Column A

    Hi

    I have a Excel workbook, that I save as a CSV to upload to a site.

    Column A contains a list of numbers like this (No more than 1500 rows) eg@

    Column A

    3D001
    3D002
    3D003
    3D004

    After saving as a CSV I notice a few rows have changed to something like

    1.00E+01
    1.00E+02
    1.00E+03
    1.00E+04
    1.00E+05
    1.00E+06
    1.00E+07
    1.00E+08
    1.00E+09
    1.00E+10
    1.00E+11
    1.00E+12
    1.00E+13
    1.00E+14
    1.00E+15
    1.00E+16
    1.00E+17
    1.00E+18
    1.00E+19

    Before saving I have set the entire column to Text before saving as a CSV.

    Any advice on how to preserve the data before saving it?

    Thanks
    Reply With QuoteRegister To Reply
  2. 06-24-2023, 03:08 PM #2
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2406 Win 11 Home 64 Bit
    Posts
    23,983

    Re: Fix Formatting In Column A

    I am unable to replicate your issue. Please read the banner at the top of this page and attach a file that can be analyzed in which you have the issue.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
    Reply With QuoteRegister To Reply
  3. 06-24-2023, 03:33 PM #3
    jayfin
    jayfin is offline
    Registered User
    Join Date
    10-21-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    94

    Re: Fix Formatting In Column A

    sample-original.xls
    sample-export.csv

    Thanks - samples attached before and after
    Reply With QuoteRegister To Reply
  4. 06-24-2023, 03:46 PM #4
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2406 Win 11 Home 64 Bit
    Posts
    23,983

    Re: Fix Formatting In Column A

    When I took your .xls file and highlighted column A and then applied TEXT as the format and then saved as a .CSV file (MS-Dos), it did not change the characteristics. See attached CSV file that was saved.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 06-24-2023, 03:55 PM #5
    jayfin
    jayfin is offline
    Registered User
    Join Date
    10-21-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    94

    Re: Fix Formatting In Column A

    It has changed a few have a look at 51-69 same results as me.

    Thanks for looking
    Reply With QuoteRegister To Reply
  6. 06-24-2023, 04:16 PM #6
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,813

    Re: Fix Formatting In Column A

    I'd guess the E in those values is confusing Excel to consider them as exponential/scientific notation.

    https://support.microsoft.com/en-gb/...3-e934cd2aae25

    Can't do any testing at the moment as I don't have access to a PC.

    One possible option is to put a single quote in front of the value. For example, change 1E001 to '1E001
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  7. 06-24-2023, 04:55 PM #7
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2406 Win 11 Home 64 Bit
    Posts
    23,983

    Re: Fix Formatting In Column A

    LInes 51-69 appear as text when I created the csv file and when I open the file on my PC they remain as text. I am guessing it is a setting on your machine. I can not help any further. Good Luck.
    Reply With QuoteRegister To Reply
  8. 06-25-2023, 12:20 PM #8
    jayfin
    jayfin is offline
    Registered User
    Join Date
    10-21-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    94

    Re: Fix Formatting In Column A

    Does the same result on 2 different PCS both using Office 2019.

    Neither have any special settings applied to them.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Button to Copy Column Range with ActiveCell position, Losing formatting after 3rd column
    By xlyfe in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-16-2018, 03:42 PM
  2. [SOLVED] Apply conditional formatting to column A when date is entered in column B (adjacent cells)
    By Hedy in forum Excel Formulas & Functions
    Replies: 33
    Last Post: 01-19-2018, 03:53 PM
  3. Sheets loose protection, formatting, validation, row & column formatting...
    By Mike Eburne in forum Excel General
    Replies: 2
    Last Post: 03-30-2017, 07:10 AM
  4. [SOLVED] Conditional Formatting. If cell in column A=x, then any entry in column B must include y
    By rnbblsmm in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-03-2015, 05:17 PM
  5. conditional formatting formula, to highlight cells in column b, based on dates in column a
    By dickman in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-24-2012, 05:14 PM
  6. Can Column Formatting based on the Data Content and the Column Headings be maintained
    By e4excel in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-26-2011, 11:57 AM
  7. Match Column data between two tabs and copy the row formatting if column matches
    By kapil in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-29-2010, 09:57 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1