How to convert MSExcel 2000 Custom Format 00000000 to TEXT

my true case numbers entered into Excel are as follows:

388569
293
11
203900
2000

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

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.
ebtspecAsked:
Who is Participating?
 
mvidasCommented:
ebtspec,

Use the TEXT formula for this in a different column, like:
 =TEXT(A1,"00000000")
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"

Matt
0
 
jsittkoCommented:
Advice:
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.

J.S.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.