Converting numbers to "database" format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to convert numbers in excel to a format with a set amount of digits.
For example, 1 becomes 001, 12 becomes 012 etc.

I have created a special format using 000. This works until I want to work
with these numbers, for example by using CONCATENATE. So if I have 01 and
012 and want it to be 01012, it ends up as 112.

Any suggestion on how to preserve formatting?
 
hi,
with leading zeros i am assuming that these are "text"
numbers or numbers you are not going to do math with.
so your problem is the excel thing that they are numbers.
format them as text and make sure that they all have an
apostophe(') in front of them. that should cure you ills.
Regards
Frank
 
You are correct, these numbers are for reference and not math.
Unfortunately, these numbers came from another source in a different format.
Is there a way to convert the numbers to the "text" format I desire without
retyping?
 
hi again
might be tricky but...
in cell a1 you have 12 formated as 012. the zero is not
there, just displayed as a format.
so in another cell somehere on the same row enter 0 at say
z1.
format both 12 at a1 and 0 at z1 as text.
then use concatinate to pull them togeather
=concatinate(z2,a1) = 012 as text. now the zero IS there.
copy and paste as values to get rid of the formula.
you may have to do this with groups of numbers depending
on how many leading zeros you want.
sticky problem. hope this helps
Frank
 
You can put this formula in another column

=TEXT(A1,"000")

Copy the formula down as far as you need. Edit/Copy this column, then
Edit/Paste Special and select the Values option to convert the formula to its
values. If you pasted over the formulas themselves, then delete the either
column. If you pasted over the original data, delete the column of formulas.
 
This Worked Great! Thanks.

Myrna Larson said:
You can put this formula in another column

=TEXT(A1,"000")

Copy the formula down as far as you need. Edit/Copy this column, then
Edit/Paste Special and select the Values option to convert the formula to its
values. If you pasted over the formulas themselves, then delete the either
column. If you pasted over the original data, delete the column of formulas.
Click to expand...
 
Back
Top