x
Solved

# How can I append a number to the beginning of a number -- and then drag it down.

Posted on 2013-02-02
Medium Priority
208 Views
Hi

Have Excel Office 2007.  Windows 7.

I have a column of numbers (can be text or number format), like this:

2123
2121
3123

I want to add a "zero" to the beginning of each of those numbers, such

02123
02121
03123

What formula can I use to do this.

Rowby
0
Question by:Rowby Goren

LVL 16

Assisted Solution

terencino earned 1000 total points
ID: 38847631
Try ="0"&B2 then drag it down
...Terry
0

LVL 93

Accepted Solution

Patrick Matthews earned 500 total points
ID: 38847632
If you want the leading zero to be part of the value:

="0"&A2

If you want to simply alter the display, without changing the value, use a custom number format of 00000
0

LVL 16

Assisted Solution

terencino earned 1000 total points
ID: 38847646
You could copy the formula column then paste special values it over the original column. Or you could add this little macro, it adds a leading apostrophe to the selected values so it will always be treated as text data type.

``````Sub AddZero()
Dim c As Range
For Each c In Selection
c.Value = "'0" & c.Value
Next c
End Sub
``````
0

LVL 7

Assisted Solution

karunamoorthy earned 500 total points
ID: 38848488
Suppose if you want like this,
A1       B1
1         00001
12         00012
123         00123
1234   01234

you can use this formula

=text(A1,"00000")

This appending zeros also work with alphabets also

Have a nice day!
0

LVL 9

Author Closing Comment

ID: 38849158
Thanks!   All your solutions worked, in different ways.

I went with the 000000 formatting in this situation.

Rowby
0

## Featured Post

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.