How to convert MSExcel 2000 Custom Format 00000000    to    TEXT

Posted on 2005-05-05
Last Modified: 2008-03-06
my true case numbers entered into Excel are as follows:


The same case numbers are DISPLAYED in Excel as 8-digit numbers
(using Excel custom format 00000000) as follows:

That is what I REALLY want...  I want 8-digits.
Now... I wish to convert the DISPLAYED numbers into TEXT just EXACTLY as they are... In other words, I want to move the 8-digit numbers into the database, so that ALL the case numbers are 8 digits.  So far, every time I attempt a conversion, the numbers come across in their original (NOT as 8-digits).  They need to be 8-digit numbers in the database.
Question by:ebtspec
    LVL 35

    Accepted Solution


    Use the TEXT formula for this in a different column, like:
    Then highlight the column with that formula, copy it, and paste special / values either on top of itself or over the original.  Now your cells will be text!

    For instance, if A1 contains "388569".  Enter    =TEXT(A1,"00000000")   in B1, copy B1, and Paste Special / Values onto A1 (you can now delete B1).

    A1 will show (in the cell as well as the formula bar) "00388569"


    Expert Comment

    You might want to do it at the database level upon getting your external data.  Especially if using Access.
    You can do this with text functions inside your import macro or whatever and data formatting inside the table.
    If not using logic (macro) get the data into an intermediate table in the DB then draw the data into a second table with your formatting.

    The thought is to leave raw data as raw (your excel file.)
    The more manipulations from the data to presentation layers allows for more errors.

    Also, people following in your footsteps get a clearer view of what they are dealing with in terms of raw inputs versus figuring out what you have done.



    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    This article shows how to convert a multi-page PDF file into multiple image files, with one image file created for each page of the PDF. It does this by utilizing an excellent, free software package called GraphicsMagick. The solution is amazingly s…
    I use more than 1 computer in my office for various reasons. Multiple keyboards and mice take up more than just extra space, they make working a little more complicated. Using one mouse and keyboard for all of my computers makes life easier. This co…
    This video demonstrates basic masking and how to edit the mask to reveal the desired image.
    Viewers will learn how to use the Hootsuite Dashboard.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now