Solved

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

Posted on 2013-02-02
5
189 Views
Last Modified: 2013-02-03
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
Comment
Question by:Rowby Goren
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 16

Assisted Solution

by:terencino
terencino earned 250 total points
ID: 38847631
Try ="0"&B2 then drag it down
Add zero...Terry
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 125 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

by:terencino
terencino earned 250 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

Open in new window

0
 
LVL 7

Assisted Solution

by:karunamoorthy
karunamoorthy earned 125 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

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

I went with the 000000 formatting in this situation.

Rowby
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

756 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