Како додати водеће нуле у Екцел

Овај водич покрива различите методе за додавање или задржавање водећих нула, као и уклањање водећих нула у Екцел-у.

Кад год унесете или увезете бројеве са једном или више водећих нула, као што је 000652, Екцел аутоматски уклања те нуле и само се сам број приказује у ћелијама (652). То је зато што водеће нуле нису неопходне за прорачуне и не рачунају се.

Међутим, постоје случајеви када су те нуле на почетку неопходне, на пример када уносите ИД бројеве, бројеве телефона, бројеве кредитних картица, кодове производа или поштанских бројева, итд. На срећу, Екцел нам даје неколико начина да додамо или задржимо почетне нуле у ћелијама. У овом чланку ћемо вам показати различите начине за додавање или задржавање водећих нула и уклањање водећих нула.

Додавање водећих нула у Екцел

У суштини, постоје 2 методе које можете користити да додате почетне нуле: један, форматирајте свој број као „текст“; друго, користите прилагођено форматирање да додате водеће нуле. Метода коју желите да користите може зависити од тога шта желите да урадите са бројем.

Можда ћете желети да додате почетну нулу када уносите јединствене ИД бројеве, бројеве рачуна, бројеве социјалног осигурања или поштанске бројеве, итд. Али, нећете користити ове бројеве за прорачуне или у функцијама, па је најбоље да их конвертујете бројева у текст. Никада не бисте збројили или усредсредили бројеве телефона или рачуне.

Постоји неколико начина на које можете додати или додати нуле испред бројева тако што ћете их форматирати као текст:

  • Промена формата ћелије у Текст
  • Додавање апострофа (‘)
  • Коришћење функције ТЕКСТ
  • Коришћење функције РЕПТ/ЛЕН
  • Користите функцију ЦОНЦАТЕНАТЕ/амперсанд оператор (&)
  • Коришћење ДЕСНО функције

Промена формата ћелије у текст

Ово је један од најједноставнијих начина да додате водеће нуле вашим бројевима. Ако ћете само да уносите бројеве и желите да задржите водеће нуле док куцате, онда је ово метод за вас. Променом формата ћелије из општег или броја у текстуални, можете натерати Екцел да третира ваше бројеве као текстуалне вредности и све што унесете у ћелију остаће потпуно исто. Ево како то радите:

Изаберите ћелију(е) у које желите да додате водеће нуле. Идите на картицу „Почетна“, кликните на падајући оквир „Формат“ у групи Бројеви и изаберите „Текст“ из опција формата.

Сада када унесете своје бројеве, Екцел неће избрисати ниједну водећу нулу из њега.

Можда ћете видети мали зелени троугао (индикатор грешке) у горњем левом углу ћелије и када изаберете ту ћелију, она ће вам показати знак упозорења који показује да сте сачували број као текст.

Да бисте уклонили поруку о грешци, изаберите ћелију(е), кликните на знак упозорења, а затим са листе изаберите „Игнориши грешку“.

Такође можете да унесете бројеве телефона са размаком или цртицом између бројева, Екцел ће аутоматски третирати ове бројеве као текст.

Коришћење Леадинг Апостроф (‘)

Други начин да додате водеће нуле у Екцел је додавање апострофа (‘) на почетак броја. Ово ће приморати Екцел да унесе број као текст.

Само унесите апостроф пре било ког броја и притисните „Ентер“. Екцел ће оставити водеће нуле нетакнуте, али (‘) неће бити видљив на радном листу осим ако не изаберете ћелију.

Коришћење функције текста

Горњи метод додаје нуле бројевима док их куцате, али ако већ имате листу бројева и желите да ставите водеће нуле испред њих, онда је функција ТЕКСТ прави метод за вас. Функција ТЕКСТ вам омогућава да конвертујете бројеве у текстуалне низове док примењујете прилагођено форматирање.

Синтакса функције ТЕКСТ:

= ТЕКСТ (вредност, формат_текст)

Где,

  • вредност - То је нумеричка вредност коју треба да конвертујете у текст и примените форматирање.
  • формат_тект – је формат који желите да примените.

Помоћу функције ТЕКСТ можете одредити колико цифара ваш број треба да има. На пример, ако желите да ваши бројеви буду дуги 8 цифара, унесите 8 нула у други аргумент функције: „00000000“. Ако имате 6-цифрени број у ћелији, функција ће ручно додати 2 водеће нуле, а ако имате двоцифрене бројеве као што је 56, остатак ће бити нуле (00000056).

На пример, да бисте додали водеће нуле и учинили бројеве шестоцифреним, користите ову формулу:

=ТЕКСТ(А2,"000000")

Пошто имамо 6 нула у другом аргументу формуле, функција конвертује низ бројева у текстуални низ и додаје 5 водећих нула да би низ био дугачак 6 цифара.

Белешка: Не заборавите да ставите кодове формата у двоструке наводнике у функцији.

Сада можете применити исту формулу на остале ћелије тако што ћете превући ручицу за пуњење. Као што видите, функција претвара бројеве у текстове и додаје нуле на почетку бројевима тако да укупан број цифара буде 6.

Функција ТЕКСТ ће увек враћати вредност као текстуални низ, а не као број, тако да нећете моћи да их користите у аритметичким прорачунима, али и даље можете да их користите у формулама за тражење као што су ВЛООКУП или ИНДЕКС/МАТЦХ да бисте дохватили детаље производа помоћу ИД-ова производа.

Коришћење функције ЦОНЦАТЕНАТЕ/амперсанд оператора (&)

Ако желите да додате фиксни број водећих нула испред свих бројева у колони, можете користити функцију ЦОНЦАТЕНАТЕ или амперсанд оператор (&).

Синтакса функције ЦОНЦАТЕНАТЕ:

=ЦОНЦАТЕНАТЕ(текст1, [текст2], ...)

Где,

текст 1 – Број нула које треба уметнути испред броја.

текст 2 – Оригинални број или референца ћелије

Синтакса оператора Амперсанд:

=Вредност_1 и Вредност_2

Где,

Вредност_1 је водеће нуле за уметање испред броја, а вредност_2 је број.

На пример, да бисте додали само две нуле испред броја, користите било коју од ове формуле:

=ЦОНЦАТЕНАТЕ("00",А2)

Први аргумент су две нуле („00“) јер желимо да додамо две нуле испред броја у А2 (који је други аргумент).

Или, 

="00"&А2

Овде је први аргумент 2 нуле, праћен оператором '&', а други аргумент је број.

Као што видите, формула додаје само две водеће нуле свим бројевима у колони без обзира на то колико цифара број садржи.

Обе ове формуле спајају одређени број нула пре оригиналних бројева и чувају их као текстуалне низове.

Коришћење функције РЕПТ/ЛЕН

Ако желите да додате почетне нуле нумеричким или алфанумеричким подацима и претворите стринг у текст, онда користите функцију РЕПТ. Функција РЕПТ се користи за понављање знакова одређени број пута. Ова функција се такође може користити за уметање фиксних бројева водећих нула испред броја.

=РЕПТ(текст, број_пута)

Где је „текст“ знак који желимо да поновимо (у нашем случају „0“), а аргумент „број_пута“ је колико пута желимо да поновимо тај знак.

На пример, да бисте генерисали пет нула испред бројева, формула би изгледала овако:

=РЕПТ(0,5)&А2

Формула понавља 5 нула и спаја низ бројева у А2 и враћа резултат. Затим се формула примењује на ћелију Б2:Б6 помоћу ручке за попуњавање.

Горња формула додаје фиксни број нула испред броја, али укупна дужина броја варира у зависности од броја.

Ако желите да додате почетне нуле где год је потребно да бисте креирали низове дужине одређеног карактера (фиксне дужине), можете заједно да користите функције РЕПТ и ЛЕН.

Синтакса:

=РЕПТ(текст, број_пута-ЛЕН(текст))&ћелија

На пример, да бисте додали нуле са префиксом вредности у А2 и направили низ од 5 знакова, испробајте ову формулу:

=РЕПТ(0,5-ЛЕН(А2))&А2

Овде „ЛЕН(А2)“ добија укупну дужину низа/бројева у ћелији А2. „5“ је максимална дужина низа/бројева које ћелија треба да има. А део „РЕПТ(0,5-ЛЕН(А2))“ додаје број нула одузимањем дужине низа у А2 од максималног броја нула (5). Затим се број 0 спаја пре вредности А2 да би се направио низ фиксне дужине.

Коришћење функције ДЕСНО

Други начин да уметнете водеће нуле пре стринга у Екцел-у је употреба функције ДЕСНО.

Функција ДЕСНО може додати број нула на почетак броја и издвојити крајњих Н знакова из вредности.

Синтакса:

= ДЕСНО (текст, број_знакова)
  • текст је ћелија или вредност из које желите да издвојите знакове.
  • број_знакова је број знакова које треба издвојити из текста. Ако овај аргумент није дат, тада ће се издвојити само први знак.

За ову методу, спајамо максималан број нула са референцом ћелије која садржи стринг у аргументу „текст“.

Да бисте креирали шестоцифрени број на основу низа бројева у А са водећим нулама, испробајте ову формулу:

=РИГХТ("0000000"&А2,6)

Први аргумент (текст) формуле додаје 7 нула вредности у А2 (“0000000”&А2), а затим враћа крајњих 7 знакова, што резултира неким водећим нулама.

Додавање водећих нула коришћењем прилагођеног форматирања бројева

Ако користите било који од горњих метода да ставите водеће нуле испред бројева, увек ћете добити текстуални низ, а не број. И неће бити од велике користи у прорачунима или у нумеричким формулама.

Најбољи начин да додате водеће нуле у Екцел је да примените прилагођено форматирање бројева. Ако додате водеће нуле додавањем прилагођеног формата броја у ћелију, то не мења вредност ћелије, већ само начин на који је приказана. Вредност ће и даље остати број, а не текст.

Да бисте променили форматирање бројева ћелија, следите ове кораке:

Изаберите ћелију или опсег ћелија у којима желите да прикажете водеће нуле. Затим кликните десним тастером миша било где у том изабраном опсегу и изаберите опцију „Форматирај ћелије“ из контекстног менија. Или притисните тастере пречице Цтрл + 1.

У прозору Формат Целлс идите на картицу „Број“ и изаберите „Прилагођено“ у оквиру опција категорије.

Унесите број нула у поље „Тип:“ да бисте навели укупан број цифара које желите да прикажете у ћелији. На пример, ако желите да број има 6 цифара, унесите „000000“ као код прилагођеног формата. Затим кликните на „ОК“ да бисте се применили.

Ово ће показати водеће нуле испред бројева, а ако је број мањи од 6 цифара, ставља нулу испред.

Чини се да бројеви имају само водеће нуле, док би основна вредност остала непромењена. Ако изаберете ћелију са прилагођеним форматирањем, она ће вам показати оригинални број у траци формуле

Постоји много дигиталних чувара места које можете да користите у свом прилагођеном формату бројева. Али постоје само два примарна чувара места која можете да користите за додавање водећих нула у бројеве.

  • 0 – То је чувар места цифара који приказује додатне нуле. Приказује присилне цифре 0-9 без обзира да ли је цифра релевантна за вредност или не. На пример, ако унесете 2.5 са кодом формата 000.00, приказаће се 002.50.
  • # – То је чувар места цифара који приказује опционе цифре и не укључује додатне нуле. На пример, ако унесете 123 са кодом за форматирање 000#, приказаће се 0123.

Такође, сваки знак интерпункције или други знак који укључите у код формата биће приказан такав какав јесте. Можете користити знакове као што су цртица (-), зарез (,), коса црта (/) итд.

На пример, такође можете да форматирате бројеве као бројеве телефона користећи прилагођени формат.

Форматни код у дијалогу Форматирање ћелија:

Резултат:

Хајде да применимо овај код за форматирање у следећем примеру:

##0000

Као што видите, „0“ ће додати додатне нуле, док „#“ не додаје безначајне нуле:

Такође можете да користите унапред дефинисане кодове формата у одељку „Посебни формати“ дијалога Формат ћелије за поштанске бројеве, бројеве телефона и бројеве социјалног осигурања.

Следећа табела приказује бројеве са водећим нулама где се различити кодови „посебног“ формата примењују на различите колоне:

Уклањање водећих нула у Екцел-у

Сада, научили сте како да додате водеће нуле у Екцел, да видимо како да уклоните водеће нуле из броја стрингова. Понекад, када увезете податке из спољног извора, бројеви могу на крају имати префикс нула и бити форматирани као текст. У таквим случајевима, морате уклонити водеће нуле и претворити их назад у бројеве, тако да их можете користити у формулама.

Постоје различити начини на које можете уклонити водеће нуле у Екцел-у и видећемо их једну по једну.

Уклоните водеће нуле променом форматирања ћелије

Ако су водеће нуле додате прилагођеним форматирањем бројева, онда их можете лако уклонити променом формата ћелија. Можете да утврдите да ли су ваше ћелије прилагођене формату ако погледате траку за адресу (нуле ће бити видљиве у ћелији, а не у траци за адресу).

Да бисте уклонили нуле са префиксом, изаберите ћелије са водећим нулама, кликните на оквир „Формат броја“ и изаберите опцију форматирања „Опште“ или „Број“.

Сада, водеће нуле су нестале:

Избришите водеће нуле претварањем текста у бројеве

Ако су ваше водеће нуле додате променом формата ћелије или додавањем апострофа испред бројева или аутоматски додате приликом увоза података, најлакши начин да их конвертујете у бројеве је коришћење опције за проверу грешке. Ево како то радите:

Ову методу можете користити ако су ваши бројеви поравнати лево и ваше ћелије имају мали зелени троугао (индикатор грешке) у горњем левом углу ћелија. То значи да су бројеви форматирани као текст.

Изаберите те ћелије и видећете жуто упозорење у горњем десном делу селекције. Затим кликните на опцију „Претвори у број“ у падајућем менију.

Ваше нуле ће бити уклоњене и бројеви ће бити конвертовани назад у формат бројева (поравнани удесно).

Уклањање водећих нула до Множење/дељење са 1

Још један једноставан и најбољи начин за уклањање водећих је множењем или дељењем бројева са 1. Дељењем или множењем вредности се вредност не мења, већ се вредност једноставно претвара у број и уклањају почетне нуле.

Да бисте то урадили, унесите формулу у доњем примеру у ћелију и притисните ЕНТЕР. Водеће нуле ће бити уклоњене и низ ће бити поново конвертован у број.

Затим примените ову формулу на друге ћелије користећи ручицу за попуњавање.

Можете постићи исте резултате коришћењем команде „Специјално лепљење“. Ево како:

Унесите нумеричку вредност „1“ у ћелију (рецимо у Б2) и копирајте ту вредност.

Затим изаберите ћелије у којима желите да уклоните водеће нуле. Затим кликните десним тастером миша на избор, а затим изаберите опцију „Специјално лепљење“.

У дијалогу Специјално лепљење, у оквиру Операција, изаберите опцију „Множи“ или „Подели“ и кликните на „У реду“.

То је то, ваше водеће нуле ће бити уклоњене, остављајући низове као бројеве.

Уклоните водеће нуле помоћу формула

Још један једноставан начин за брисање нула са префиксом је коришћење функције ВАЛУЕ. Овај метод би могао бити користан било да су ваше водеће нуле додате коришћењем друге формуле или апострофа или прилагођеним форматирањем.

=ВРЕДНОСТ(А1)

Аргумент формуле може бити вредност или референца ћелије која има вредност. Формула уклања водеће нуле и конвертује вредност из текста у број. Затим примените формулу на остале ћелије.

Понекад ћете можда желети да уклоните водеће нуле, али желите да задржите бројеве у текстуалном формату. У таквим случајевима, морате да користите функције ТЕКСТ() и ВАЛУЕ () заједно на следећи начин:

=ТЕКСТ(ВРЕДНОСТ(А1),"#")

Функција ВАЛУЕ претвара вредност у А1 у број. Али други аргумент, „#“ конвертује вредност назад у текстуални формат без додатних нула. Као резултат, добили бисте бројеве без водећих нула, али и даље у текстуалном формату (поравнани лево).

Уклоните водеће нуле користећи Екцел-ову функцију текста у колоне

Још један начин да уклоните водеће нуле је коришћење Екцел-ове функције Тект то Цолумнс.

Изаберите опсег ћелија које имају бројеве са водећим нулама.

Затим идите на картицу „Подаци“ и кликните на дугме „Текст у колоне“ у групи Алатке за податке.

Појавиће се чаробњак „Претвори текст у колоне“. У кораку 1 од 3, изаберите „Делимитед“ и кликните на „Даље“.

У кораку 2 од 3, поништите све граничнике и кликните на „Даље“.

У последњем кораку, оставите опцију за формат података колоне као „Опште“ и изаберите одредиште (прва ћелија опсега) где желите своје бројеве без водећих нула. Затим кликните на „Заврши“

И добићете бројеве са уклоњеним водећим у посебној колони као што је приказано испод.

То је све.