## How to add/insert leading zeros to numbers or text in Excel?

Supposing you need to input some zip codes or phone numbers in cells of Excel, then you will find Microsoft Excel removes the leading zeros by default. This article is going to show you some tutorials about how to add leading zeros to numbers or text in ranges of cells in Excel.

**Add/ insert leading zeros to numbers with Text function**

**Add/ insert leading zeros to numbers and text with Concatenate function**

**Add/ insert leading zeros to numbers and text with Kutools for Excel**

**Recommended Productivity Tools**

**Office Tab**: **Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.**Try now

**Kutools for Excel**: **200 new features for Excel, make Excel much easy and powerful, increase productivity immediately.**Try now

** Add/ insert leading zeros to numbers with Text function**

Supposing you have a range of data, and you need to add zeros in front of each cells as following screenshots shown. How could you quickly add the zeros before the numbers?

The Text function will add zeros in front of numbers, and make the new numbers with leading zeros in certain length.

Step 1: Enter the formula ** =Text (A1, "00000")** in a blank cell which is adjacent to the data cell.

Step 2: Then press **Enter** key, and select cell C1 drag the fill handle across the range that you want to fill. Then you will view all the numbers in A1:B6 are copied and pasted to C1:D6 with leading zeros. And each numbers contains 5 digits.

**Tips**: 1. As they are formulas, so when you need to copy and paste them to other places, you need to paste them as values.

2. This Text function can not add leading zeros for text.

** Add/ insert leading zeros to numbers and text with Concatenate function**

If you want to insert specific digit of leading zeros into each number, for example three leading zeros for each number, you should try the Concatenate function.

Step 1: Enter the formula ** =Concatenate ("000", A1)** in a blank cell and press

**Enter**key. In this case, we enter the formula in Cell C1.

Step 2: Click the Cell C1, and drag the fill handle across the range that you want to fill. Then you will view numbers in A1:B6 are pated to C1:D6, and each number contains three leading zeros.

** Add/ insert leading zeros to numbers and text with Kutools for Excel**

Functions or formulas may be not easy for you to remember and apply. Here is another tricky way, the **Add Text** utility of **Kutools for Excel**, to help you insert leading zeros before numbers in selections.

Kutools for Excel includes more than 120 handy Excel tools. Free to try with no limitation in 30 days. **Get it Now**.

Step 1: Select the range what you will work with.

Step 2: Right click the range, and select **Formal cells** from context menu. In the **Format Cells** dialog box, click the **Text **option in **Category** list under** Number** tab. Click **OK**.

Step 3: Click **Kutools** > **Text** > **Add Text**.

Step 4: In the** Add Text** dialog box, enter zeros in the **Text** box, and select the **Before first character** option.

Step5: And then click **OK** or **Apply**. Then all cells are added leading zeros in the selection.

The **Add Text** utility of **Kutools for Excel **supports us to add any specific text and characters before or after the original cell text. What's more, we can add specific text between original cell texts. No matter which settings you set, you can preview the cell changes in the Preview section. **Click to know more about this utility**.

#### ** Keep formula cell reference constant with Kutools for Excel**

**Kutools for Excel** includes more than 120 handy Excel tools. Free to try with no limitation in 60 days.** Download the free trial now!**

**Recommended Productivity Tools**

**Office Tab**

** Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.**

**Kutools for Excel**

**Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!**

**200 New Features for Excel, Make Excel Much Easy and Powerful:**

- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

## Comments

my problem is the worksheet needs to be uploaded into a program and the C1 cell is a problem because it has the concatenate formula and is not recognised as a number. Can you help?

One tip for those like me who are trying to add leading zeros & leading text, try using the "&" to pull everything together.

Example:

="Bob"&(Text(value,"000000")). If the value (number) is 123, the result will be... Bob000123

cmd, you are very helpful. thanks!!PLEASE I STILL HAVE ONE CHALLENGE ON THIS, I HAVE A RANGE TO WORK ON, FIRST I HAVE TO ROUND THE NUMBERS UP TO 10, BUT I HAVE THEM IN 3 DIFFERENT DIGITS (6, 7 & SCATTERED ALL OVER THE RANGE. MY PROBLEM IS HOW TO INPUT A FOMULAR THAT WILL ENABLE THE EXCEL DETECT HOW MANY ZEROS EACH OF THE NUMBERS WILL HAVE IN FRONT TO ROUND UP TO 10.

1. Enter " into a blank cell.

2. Formula... =(select cell with the ")&(select cell with your text)&(select cell with the ")

Example: " in cell A1 and Barry in cell C1... =A1&C1&A1.

Result: "Barry"

=text(B4,"00000") from A1 being 2932

ABW-M-1

BAS-B-74

ESU-F-3

and need them to be:

ABW-M-001

BAS-B-074

ESU-F-003

? :/

2) Use =Text(B1,"000") formula for the numbers you need changed to 3 digits.

3) Use Concatenate formula to recombine the data back into a single string.

1. Split with Text to Column

2. Insert formula =Text(B1,"000") (Column C)

3. Concatenate Column A & Column C (Column D)

A B C D

ABW-M- 1 001 ABW-M-001

BAS-B- 74 074 BAS-B-074

ESU-F- 3 003 ESU-F-003

Ex. I need to use the number produced from =Text (A1, "000")

Cell A1 12345

Cell A2 =Text (A1,"000000")Re sult being 012345

Copy & Paste 012345 as a number into another program without the formula being pasted into the other program.

When I copy & paste 012345 the result is =Text (A1, "000000")

Sub Column_Fix()

Dim rng As Range, col As Range, arr

Dim sht As Worksheet, c As Range, tmp

On Error Resume Next 'in case of cancellation

Set rng = Application.Inp utBox( _

Prompt:="Please select the column that contains the incorrect values." & _

" (e.g. Column A or Column B)", _

Title:="Select Range", Type:=8)

On Error GoTo 0

hdr = MsgBox("Does your selection contain a header?", vbYesNo + vbQuestion, "Header Option")

If rng Is Nothing Then Exit Sub

If rng.Columns.Cou nt > 1 Then

MsgBox "Please select only one column at a time.", vbExclamation

Exit Sub

End If

Set sht = rng.Parent

Application.Scr eenUpdating = False

If hdr = vbYes Then

Set col = sht.Range(sht.C ells(2, rng.Column), _

sht.Cells(sht.R ows.Count, rng.Column).End (xlUp))

For Each c In col.Cells

c.Value = Replace(c.Value , ".", "")

Next c

End If

If hdr = vbNo Then

Set col = sht.Range(sht.C ells(1, rng.Column), _

sht.Cells(sht.R ows.Count, rng.Column).End (xlUp))

For Each c In col.Cells

c.Value = Replace(c.Value , ".", "")

Next c

End If

Application.Scr eenUpdating = True

End Sub

If you have any problems, let me know.

Thanks!

I have dates in the format yyymmdd, e.g. 2080201 stays for 1st of Feb. 2008 (2008.02.01). how can I add a zero after the first two digits?

like: yyymmdd to yyyymmdd (e.g. 2080201 to 20080201)

Thanks!

Is it possible through formatting.

I have to export a file to CSV (currently the only option), within that file there are numbers that are usually 16 characters long, the first 2 are zeros, and sometimes, the last two figures might be E followed by a number, for example, 00633597038029E 6. In the system they are being exported from, it is consistent and there isn't a problem, however as soon as the CSV file is opened in Excel, it converts the E6 and replaces it with 6 zeros, the above number becomes 006335970380290 00000. However, it also only does this when it drops the 2 zeros from the front. Leaving it as 633597038029000 000. When excel does not decide to drop the 2 zeros, it isn't a problem and it leaves the number as E6, it is only on the occasion where it has for some reason, dropped the 2 zeros.

The problem I have is that it seems that by the time I have opened the file, it is too late to do anything as excel has already converted the numbers.

Is there anything that can be done about this?

I want to add "00" for A1 column but every cell contain random numeric digits.

Thanks in advance.

=A1/10000

Then type something in A1 and check. Does that work or do you need a more precise solution?

B1 = A1/10000

= .0005

thanks

That will ensure that there are enough leading zeros so that you have 10 characters in each string.

example: =0&0&A1 (A1 is cell in which 00 is to be added as prefix)

ie. it comes up as " Sp3.14525524634 5 " and i want it to show " Sp3"

Thanks