Note: The other languages of the website are Google-translated. Back to English
Войти  \/ 
x
or
x
Регистрация  \/ 
x

or

Как добавить или дополнить начальные нули числами или текстом в Excel?

Если у вас есть список чисел, который вам нужно добавить или дополнить некоторыми ведущими нулями перед числами, при вводе нулей вручную они будут удалены по умолчанию. Эта статья покажет вам несколько руководств о том, как добавлять начальные нули к числам или тексту в диапазонах ячеек в Excel.

док добавить начальные нули 1

Метод 1. Добавьте или дополните начальные нули к числам до определенной длины с помощью функции Text

Метод 2: добавьте или дополните начальные нули к числам, чтобы они равнялись определенной длине, с помощью функции форматирования ячеек

Метод 3: добавьте или вставьте одинаковое количество ведущих нулей в числа с помощью функции Concatenate

Метод 4: добавьте или вставьте одинаковое количество ведущих нулей в числа и текст с помощью Kutools for Excel


Метод 1. Добавьте или дополните начальные нули к числам до определенной длины с помощью функции Text

Предположим, у вас есть диапазон данных, и вам нужно добавить нули перед каждой ячейкой, чтобы числа были одинаковой длины. Как можно быстро добавить нули перед числами?

Функция Text добавит нули перед числами и сделает новые числа с ведущими нулями определенной длины.

Введите формулу = ТЕКСТ (A4; «000000») в пустой ячейке, в которую вы хотите вывести результат, а затем перетащите дескриптор заполнения вниз к ячейкам, к которым вы хотите применить эту формулу, и все числа станут фиксированной шестизначной символьной строкой, см. снимок экрана:

док добавить начальные нули 2

Внимание: Эта функция текста не может добавлять начальные нули к тексту.


Метод 2: добавьте или дополните начальные нули к числам, чтобы они равнялись определенной длине, с помощью функции форматирования ячеек

В Excel Формат ячеек Функция также может помочь вам добавить начальные нули к значениям ячеек, чтобы сделать их одинаковой длины, пожалуйста, сделайте следующее:

1. Выберите значения ячеек, которые вы хотите дополнить начальными нулями, а затем щелкните правой кнопкой мыши, затем выберите Формат ячеек из контекстного меню.

2. В Формат ячеек диалоговое окно под Число вкладка, выберите На заказ слева Категория панель, а затем введите 000000 в Тип текстовое поле (в этом примере я сделаю числа как шестизначные числа, вы можете изменить его по своему усмотрению.), см. снимок экрана:

док добавить начальные нули 3

3, Затем нажмите OK , чтобы закрыть это диалоговое окно, и числа в исходных ячейках были преобразованы в числа определенной длины с ведущими нулями. Смотрите скриншот:

док добавить начальные нули 4


Метод 3: добавьте или вставьте одинаковое количество ведущих нулей в числа с помощью функции Concatenate

Если вы хотите вставить определенную цифру ведущих нулей в каждое число, например три ведущих нуля для каждого числа, вам следует попробовать Объединить функции.

Введите формулу = СЦЕПИТЬ ("000"; A4) в пустой ячейке, а затем перетащите дескриптор заполнения вниз, чтобы заполнить эту формулу в другие ячейки, и ко всем числам были добавлены три нуля перед числами, см. снимок экрана:

док добавить начальные нули 5


Метод 4: Метод 4: Добавьте или вставьте одинаковое количество ведущих нулей в числа и текст с помощью Kutools for Excel

Вам может быть нелегко запомнить и применить функции или формулы. Вот еще один хитрый способ Добавить текст полезности Kutools for Excel, чтобы помочь вам вставить начальные нули перед числами в выборках.

Kutools for Excel : с более чем 300 удобными надстройками Excel, бесплатно и без ограничений в течение 30 дней. 

1. Выберите диапазон, в котором вы хотите добавить нули перед числами или текстом.

2. Затем нажмите Кутулс > Текст > Добавить текст, см. снимок экрана:

3. В Добавить текст диалоговом окне введите количество нулей с начальным апострофом в Текст поле и выберите Перед первым персонажем , затем нажмите Ok и нули были добавлены перед числами, как показано на следующем снимке экрана:

док добавить начальные нули 7

Нажмите, чтобы загрузить Kutools for Excel и бесплатную пробную версию сейчас!

Добавить текст полезности Kutools for Excel поддерживает нас, чтобы добавить любой конкретный текст и символы до или после исходного текста ячейки. Более того, мы можем добавлять определенный текст между исходными текстами ячеек. Независимо от того, какие настройки вы установили, вы можете предварительно просмотреть изменения ячеек в разделе «Предварительный просмотр». Нажмите, чтобы узнать больше об этой утилите.


Добавление ведущих нулей к числам или тексту с помощью Kutools for Excel

Kutools for Excel включает более 300 удобных инструментов Excel. Бесплатная пробная версия без ограничений в течение 30 дней. Загрузите бесплатную пробную версию прямо сейчас!


Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

  • Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма ...
  • Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
  • Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы... Предотвращение дублирования ячеек; Сравнить диапазоны...
  • Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
  • Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
  • Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии...
  • Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом ...
  • Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Более 300 мощных функций. Поддерживает Office / Excel 2007-2019 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
вкладка kte 201905

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    TRAVOZ STEVEN · 11 months ago
    I got my already programmed and blank ATM card to withdraw the maximum of $100,000 MONTHLY for a maximum of 12 MONTHS. I am so happy about this because i got mine last week and I have used it to get $400,000 already. (perrythomas827@gmail.com) is giving out the card just to help the poor and needy though it is illegal but it is something nice and he is not like other scam pretending to have the blank ATM cards. And no one gets caught when using the card. Get yours from THOMAS PERRY Hackerz today! Just send an email to (perrythomas827@gmail.com)
  • To post as a guest, your comment is unpublished.
    superstar · 3 years ago
    В примере ошибка, нужно ставить не запятую, а точку с запятой: "= Текст (A2, "00000")"
  • To post as a guest, your comment is unpublished.
    Narcisse · 3 years ago
    thank you, very helpful
  • To post as a guest, your comment is unpublished.
    Bernice · 3 years ago
    I was wondering how i would write phone numbers in excel, but there was no zeros appearing. And also the phone numbers are in one column like

    column D
  • To post as a guest, your comment is unpublished.
    Rashida · 4 years ago
    Thank you for sharing..it's really use for me.
  • To post as a guest, your comment is unpublished.
    Shirley · 4 years ago
    Thank you for helping me out. You saved my day!
  • To post as a guest, your comment is unpublished.
    Peter · 4 years ago
    Worked well for me. Thanks alot.
  • To post as a guest, your comment is unpublished.
    Ryan · 4 years ago
    Hey, i was wondering how to put text in-front of a number with 0 decimal places.
    ie. it comes up as " Sp3.145255246345 " and i want it to show " Sp3"


    Thanks
  • To post as a guest, your comment is unpublished.
    MANMOHAN · 4 years ago
    Use 0&
    example: =0&0&A1 (A1 is cell in which 00 is to be added as prefix)
  • To post as a guest, your comment is unpublished.
    Andrea · 5 years ago
    I need to add zeros to the front of the ID (know how to add leading zeros) but some need 6 and some need 5 zeros. Is there a formula to make sure I have a 10 digit number when I'm done?
    • To post as a guest, your comment is unpublished.
      jyoti · 4 years ago
      you can use "REP FUNCTION AND LENGTH FUNCTION TOGETHER " for such problems.

      Example : =REP(0,10-LEN(C4))&C4
    • To post as a guest, your comment is unpublished.
      Blake · 5 years ago
      Hi Andrea. Assuming your original text is in cell A2, you can type =TEXT(A2,"0000000000")

      That will ensure that there are enough leading zeros so that you have 10 characters in each string.
  • To post as a guest, your comment is unpublished.
    Gaz · 5 years ago
    CMD & BLAKE, You are absolute whizzkids! :-)
    thanks
  • To post as a guest, your comment is unpublished.
    Duane · 5 years ago
    I deal alot with serial numbers of my companies product that includes leading zeros, which I frequently store in Excel sheets by pasting them out of our SAP system. Usually I can just format the cells as text before pasting them, which keeps the zeros, but occasionally I have to add them back in. Before finding this article I've struggled with that, but this works perfectly, thanks for the tip.
  • To post as a guest, your comment is unpublished.
    Sharon · 5 years ago
    Hi! I have no idea if this is possible or not, but my boss would like to enter whole numbers into cells, but then have them converted into decimals (for example, he want to enter a 5 in the cell, but then have it converted to .0005). Another example that he gave is he might enter 49 and would like the cell to be .0049. Any suggestions/ideas? Thanks!
    • To post as a guest, your comment is unpublished.
      Peter · 5 years ago
      A1=5
      B1 = A1/10000
      = .0005
    • To post as a guest, your comment is unpublished.
      Blake · 5 years ago
      Hi. You could enter a formula that divides the cell by 10000. For example, if your entry cell is A1 and your cell for formatting is B1, you can type the following in B1:

      =A1/10000

      Then type something in A1 and check. Does that work or do you need a more precise solution?
  • To post as a guest, your comment is unpublished.
    scott.george@siemens · 5 years ago
    Adding leading zeros
  • To post as a guest, your comment is unpublished.
    Anup · 5 years ago
    Hello

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

    Thanks in advance.
  • To post as a guest, your comment is unpublished.
    Hayley · 5 years ago
    Hi, I have a problem, but not sure if it can be fixed in Excel:

    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, 00633597038029E6. 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 00633597038029000000. However, it also only does this when it drops the 2 zeros from the front. Leaving it as 633597038029000000. 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?
  • To post as a guest, your comment is unpublished.
    Johncy · 5 years ago
    How to convert a number into thousands or lakhs, depending on the number of zeroes required.
    Is it possible through formatting.
  • To post as a guest, your comment is unpublished.
    Gin · 5 years ago
    Hi all,

    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!
  • To post as a guest, your comment is unpublished.
    Twowolves · 5 years ago
    Nice! Just ran into this problem at work and was amazed at how easy the solution was! Thanks!
  • To post as a guest, your comment is unpublished.
    kate · 5 years ago
    thanks for the info !
  • To post as a guest, your comment is unpublished.
    Zella · 5 years ago
    Great tips! I need to make this number 1-354-0-2 be in the following format: 01-0354-00-02. Can you help??
    Thanks!
  • To post as a guest, your comment is unpublished.
    Smithc530 · 5 years ago
    I am no longer sure the place you're getting your info, but great topic. I needs to spend a while studying much more or understanding more. Thanks for wonderful info I was on the lookout for this info for my mission. efbdcekddgfegdfe
  • To post as a guest, your comment is unpublished.
    oc · 6 years ago
    Thank you Blake...works perfec.
  • To post as a guest, your comment is unpublished.
    Blake · 6 years ago
    #OC, I wrote you a VBA script. To execute, open your subject Workbook, ensure that the affected column is formatted as Text, then press alt+F11, then in the toolbar select Insert > Module. When the editor loads, paste the following code in and then execute:

    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.InputBox( _
    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.Count > 1 Then
    MsgBox "Please select only one column at a time.", vbExclamation
    Exit Sub
    End If

    Set sht = rng.Parent


    Application.ScreenUpdating = False
    If hdr = vbYes Then
    Set col = sht.Range(sht.Cells(2, rng.Column), _
    sht.Cells(sht.Rows.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.Cells(1, rng.Column), _
    sht.Cells(sht.Rows.Count, rng.Column).End(xlUp))

    For Each c In col.Cells
    c.Value = Replace(c.Value, ".", "")
    Next c
    End If
    Application.ScreenUpdating = True
    End Sub



    If you have any problems, let me know.
    • To post as a guest, your comment is unpublished.
      oc · 6 years ago
      Thank you! Works like a charm..
  • To post as a guest, your comment is unpublished.
    oc · 6 years ago
    HOW CAN I HAVE A VALUE OF 0.0010 SHOW AS 00010 AND A VALUE OF 1.001 AS 1001? IN EXCEL? Thanks.
  • To post as a guest, your comment is unpublished.
    HARI · 6 years ago
    thx a lot. I like to learn a lot
  • To post as a guest, your comment is unpublished.
    Tanvir · 6 years ago
    tanx boss, really so nice
  • To post as a guest, your comment is unpublished.
    Afra · 6 years ago
    how to 8.00 ?? please ans
    • To post as a guest, your comment is unpublished.
      rfruae · 6 years ago
      Use the Comma function (Accounting)
  • To post as a guest, your comment is unpublished.
    Brandon · 6 years ago
    Thanks for sharing the information, example well formatted, baie dankie :)
  • To post as a guest, your comment is unpublished.
    Lizz · 6 years ago
    Thank you for all your helpful tips. I was wondering if there is a way to format the answers from a formula to be able to copy the result into another program as a number.
    Ex. I need to use the number produced from =Text (A1, "000")
    Cell A1 12345
    Cell A2 =Text (A1,"000000")Result 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")
    • To post as a guest, your comment is unpublished.
      cmd · 6 years ago
      Try Paste Special > Values and number formats
  • To post as a guest, your comment is unpublished.
    vartaxe · 6 years ago
    how to do when cells look like this:

    ABW-M-1
    BAS-B-74
    ESU-F-3

    and need them to be:

    ABW-M-001
    BAS-B-074
    ESU-F-003

    ? :/
    • To post as a guest, your comment is unpublished.
      cmd · 6 years ago
      1) Use Text to Columns (Data > Text to Columns) to split out the last number. - Use fixed width and split after the second hyphen/dash.
      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
  • To post as a guest, your comment is unpublished.
    Jamie · 7 years ago
    Absolutely perfect, the =Text (A1, "00000") Formula works perfectly for me! Excel can do some incredible things, its just knowing these formulas!!
  • To post as a guest, your comment is unpublished.
    Ruby · 7 years ago
    for you because you are the best
  • To post as a guest, your comment is unpublished.
    Delio · 7 years ago
    does not work i used the exact numbers and i receive formula error

    =text(B4,"00000") from A1 being 2932
  • To post as a guest, your comment is unpublished.
    Matt_m · 7 years ago
    Is there anyway to ad quotation marks before and after any text entered into a cell? It looks like Kutools could do it but is there anyway to do it with just Excel? Thanks in advance.
    • To post as a guest, your comment is unpublished.
      cmd · 7 years ago
      Here is a quick way to add the quotation mark before & after your text...

      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"
  • To post as a guest, your comment is unpublished.
    mutale kay · 7 years ago
    very good topic, i have got my work done.
  • To post as a guest, your comment is unpublished.
    seema sahu · 7 years ago
    thnx my prob is solved.
  • To post as a guest, your comment is unpublished.
    Marlene · 7 years ago
    Thanks a lot! It saved me !
  • To post as a guest, your comment is unpublished.
    Firat · 7 years ago
    Great tips! Text function just saved my day, thank you for this simple solution.
  • To post as a guest, your comment is unpublished.
    ANSELM · 7 years ago
    THIS IS JUST AWESOME. MANY 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 & 8) 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.
  • To post as a guest, your comment is unpublished.
    Hafiz Mohammad Yasee · 7 years ago
    thanks I found my problem from your site.
  • To post as a guest, your comment is unpublished.
    wani · 7 years ago
    [b]cmd[/b], you are very helpful. thanks!!
  • To post as a guest, your comment is unpublished.
    Jen · 7 years ago
    What if there are dashes, for example 3-14-50-12, 10-12-48-6. But you only need there to be a 0 in front of the single digit at the beginning?
  • To post as a guest, your comment is unpublished.
    C · 7 years ago
    Instructions on how to add leading zeros.
  • To post as a guest, your comment is unpublished.
    cmd · 7 years ago
    First off this has been a very helpful site. Thanks!

    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
    • To post as a guest, your comment is unpublished.
      Melissa · 7 years ago
      Thanks for this tip CMD, this is exactly what I was trying to do!!
    • To post as a guest, your comment is unpublished.
      Ms. R Kumar · 7 years ago
      Thanks a lot for the help given using Excel.
  • To post as a guest, your comment is unpublished.
    Subhendu Baliarsingh · 8 years ago
    Thanks....its very very helped me
  • To post as a guest, your comment is unpublished.
    Pam · 8 years ago
    Still not working - I have a alphanumeric number A1234 that I need to put a zero in front of the A how do you do that?
  • To post as a guest, your comment is unpublished.
    Daniella Kane · 8 years ago
    Thia was AWESOME!!! Thanks!!!!
  • To post as a guest, your comment is unpublished.
    Patrick · 8 years ago
    Excellent fix. Thanks
  • To post as a guest, your comment is unpublished.
    peter · 8 years ago
    I have used concatenate to create two leading zeros in number not text. A1 00 B1 00 C1 1234 = 001234
    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?
    • To post as a guest, your comment is unpublished.
      Blake · 8 years ago
      Simple fix. After using the aforementinoed formula, copy all cells that contain formulas and right click and 'paste values'. This will keep the values on the spreadsheet (formula results) and get rid of the formulas for you.
      • To post as a guest, your comment is unpublished.
        peter · 8 years ago
        Thanks, I did try that however when pasted as values the result goes to text and not a number. Any ideas appreciated