Sinasaklaw ng tutorial na ito ang iba't ibang paraan upang magdagdag o mapanatili ang mga nangungunang zero pati na rin ang pag-alis ng mga nangungunang zero sa Excel.
Sa tuwing maglalagay ka o mag-i-import ng mga numero na may isa o higit pang nangungunang mga zero, tulad ng 000652, awtomatikong inaalis ng Excel ang mga zero na iyon, at ang numero lang mismo ang makikita sa mga cell (652). Ito ay dahil ang mga nangungunang zero ay hindi kinakailangan para sa mga kalkulasyon at hindi binibilang.
Gayunpaman, may mga pagkakataon na kailangan ang mga nangungunang zero na iyon, tulad ng kapag naglalagay ka ng mga numero ng ID, numero ng telepono, numero ng credit card, code ng produkto, o postal code, atbp. Sa kabutihang palad, binibigyan tayo ng Excel ng ilang paraan upang magdagdag o mapanatili ang mga nangungunang zero. sa mga selula. Sa artikulong ito, ipapakita namin sa iyo ang iba't ibang paraan upang magdagdag o mapanatili ang mga nangungunang zero at alisin ang mga nangungunang zero.
Pagdaragdag ng Mga Nangungunang Zero sa Excel
Sa esensya, mayroong 2 paraan na maaari mong gamitin upang magdagdag ng mga nangungunang zero: isa, i-format ang iyong numero bilang 'Text'; dalawa, gumamit ng custom na pag-format para magdagdag ng mga nangungunang zero. Ang paraan na gusto mong gamitin ay maaaring depende sa kung ano ang gusto mong gawin sa numero.
Maaaring gusto mong magdagdag ng nangungunang zero kapag naglalagay ka ng mga natatanging ID number, account number, social security number, o zip code, atbp. Ngunit, hindi mo gagamitin ang mga numerong ito para sa mga kalkulasyon o sa mga function, kaya pinakamahusay na i-convert ang mga iyon mga numero sa text. Hindi ka kailanman magsusuma o karaniwang mga numero ng telepono o numero ng account.
Mayroong ilang mga paraan na maaari mong idagdag o i-pad ang mga zero bago ang mga numero sa pamamagitan ng pag-format sa mga ito bilang text:
- Ang pagpapalit ng format ng cell sa Text
- Pagdaragdag ng apostrophe (‘)
- Gamit ang TEXT function
- Gamit ang REPT/LEN Function
- Gamitin ang CONCATENATE Function/Ampersand operator (&)
- Gamit ang RIGHT function
Pagbabago ng Cell Format sa Text
Ito ay isa sa mga pinakasimpleng paraan upang magdagdag ng mga nangungunang zero sa iyong mga numero. Kung maglalagay ka lang ng mga numero at gusto mong manatiling nangunguna sa mga zero habang nagta-type ka, ito ang paraan para sa iyo. Sa pamamagitan ng pagpapalit ng format ng cell mula sa Pangkalahatan o Numero sa Teksto, maaari mong pilitin ang Excel na ituring ang iyong mga numero bilang mga halaga ng teksto at anumang ita-type mo sa cell ay mananatiling eksaktong pareho. Narito kung paano mo ito gagawin:
Piliin ang (mga) cell kung saan mo gustong magdagdag ng mga nangungunang zero. Pumunta sa tab na 'Home', mag-click sa drop-down box na 'Format' sa pangkat ng Mga Numero, at piliin ang 'Text' mula sa mga opsyon sa format.
Ngayon kapag nag-type ka ng iyong mga numero, hindi tatanggalin ng Excel ang anumang nangungunang zero mula dito.
Maaari kang makakita ng maliit na berdeng tatsulok (Error indicator) sa kaliwang sulok sa itaas ng cell at kapag pinili mo ang cell na iyon, magpapakita ito sa iyo ng babalang palatandaan na nagsasaad na naimbak mo ang numero bilang text.
Upang alisin ang mensahe ng error, piliin ang (mga) cell, mag-click sa tanda ng babala, at pagkatapos ay piliin ang 'Balewalain ang Error' mula sa listahan.
Maaari mo ring i-type ang mga numero ng telepono na may espasyo o gitling sa pagitan ng numero, awtomatikong ituturing ng Excel ang mga numerong ito bilang teksto.
Paggamit ng Nangunguna Apostrophe ( ‘ )
Ang isa pang paraan upang magdagdag ng mga nangungunang zero sa Excel ay ang pagdaragdag ng apostrophe (‘) sa simula ng numero. Pipilitin nito ang Excel na maglagay ng numero bilang text.
Mag-type lamang ng apostrophe bago ang anumang mga numero at pindutin ang 'Enter'. Iiwan ng Excel na buo ang mga nangungunang zero, ngunit ang (‘) ay hindi makikita sa worksheet maliban kung pipiliin mo ang cell.
Gamit ang Text Function
Ang pamamaraan sa itaas ay nagdaragdag ng mga zero sa mga numero habang tina-type mo ang mga ito, ngunit kung mayroon ka nang listahan ng mga numero at gusto mong i-pad ang mga nangungunang zero bago ang mga ito, ang TEXT function ay ang tamang paraan para sa iyo. Binibigyang-daan ka ng TEXT function na i-convert ang mga numero sa mga text string habang inilalapat ang custom na pag-format.
Ang Syntax ng TEXT function:
= TEXT( halaga, format_text)saan,
- halaga - Ito ang numerical value na kailangan mong i-convert sa text at ilapat ang pag-format.
- format_text – ang format na gusto mong ilapat.
Gamit ang TEXT function, maaari mong tukuyin kung gaano karaming mga digit ang dapat mayroon ang iyong numero. Halimbawa, kung gusto mong maging 8 digit ang haba ng iyong mga numero, pagkatapos ay i-type ang 8 zero sa pangalawang argumento ng function: “00000000”. Kung mayroon kang 6 na digit na numero sa isang cell, ang function ay manu-manong magdagdag ng 2 nangungunang mga zero at kung mayroon kang 2 digit na mga numero tulad ng 56, ang natitira ay magiging mga zero (00000056).
Halimbawa, upang magdagdag ng mga nangungunang zero at gawing 6 na digit ang haba, gamitin ang formula na ito:
=TEXT(A2,"000000")Dahil mayroon kaming 6 na zero sa pangalawang argumento ng formula, kino-convert ng function ang string ng numero sa isang text string at nagdaragdag ng 5 nangungunang zero upang gawing 6 na digit ang haba ng string.
Tandaan: Mangyaring tandaan na ilakip ang mga format ng code sa double quotation marks sa function.
Ngayon ay maaari mong ilapat ang parehong formula sa natitirang mga cell sa pamamagitan ng pag-drag sa fill handle. Tulad ng nakikita mo, ang function ay nagko-convert ng mga numero sa mga teksto at nagdaragdag ng mga nangungunang zero sa mga numero upang ang kabuuang bilang ng mga digit ay 6.
Ang TEXT Function ay palaging magbabalik ng halaga bilang isang text string, hindi isang numero, kaya hindi mo magagamit ang mga ito sa mga kalkulasyon ng arithmetic ngunit magagamit mo pa rin ang mga ito sa mga lookup formula gaya ng VLOOKUP o INDEX/MATCH para makuha ang mga detalye ng isang produkto gamit ang mga Product ID.
Paggamit ng CONCATENATE Function/Ampersand Operator (&)
Kung gusto mong magdagdag ng nakapirming bilang ng mga nangungunang zero bago ang lahat ng numero sa isang column, maaari mong gamitin ang CONCATENATE function o ang ampersand operator (&).
Syntax ng CONCATENATE function:
=CONCATENATE(text1, [text2], ...)saan,
text1 – Ang bilang ng mga zero na ilalagay bago ang numero.
text2 – Ang orihinal na numero o cell reference
Syntax ng Ampersand operator:
=Halaga_1 at Halaga_2 saan,
Ang Value_1 ay ang nangungunang mga zero na ilalagay bago ang numero at ang Value_2 ay ang numero.
Halimbawa, upang magdagdag lamang ng dalawang zero bago ang isang numero, gamitin ang alinman sa formula na ito:
=CONCATENATE("00",A2)Ang unang argumento ay dalawang zero (“00”) dahil gusto naming maglagay ng dalawang zero bago ang numero sa A2 (na siyang pangalawang argumento).
o kaya,
="00"&A2Dito, ang unang argumento ay 2 zero, na sinusundan ng '&' operator, at ang pangalawang argumento ay ang numero.
Tulad ng nakikita mo, ang formula ay nagdaragdag lamang ng dalawang nangungunang mga zero sa lahat ng mga numero sa isang column kahit gaano karaming mga digit ang nilalaman ng numero.
Ang parehong mga formula na ito ay nagsasama ng isang tiyak na bilang ng mga zero bago ang orihinal na mga numero at iniimbak ang mga ito bilang mga string ng teksto.
Gamit ang REPT/LEN Function
Kung gusto mong magdagdag ng mga nangungunang zero sa numeric o alphanumeric na data at i-convert ang string sa text, pagkatapos ay gamitin ang REPT function. Ang REPT function ay ginagamit upang ulitin ang isang (mga) character sa isang tiyak na bilang ng beses. Ang function na ito ay maaari ding gamitin upang magpasok ng mga nakapirming numero ng mga nangungunang zero bago ang numero.
=REPT(teksto, numero_beses)Kung saan ang 'text' ay ang character na gusto naming ulitin (sa aming kaso '0') at ang 'number_times' na argumento ay ang dami ng beses na gusto naming ulitin ang character na iyon.
Halimbawa, upang makabuo ng limang zero bago ang mga numero, ang formula ay magiging ganito:
=REPT(0,5)&A2Ang ginagawa ng formula ay ulitin ang 5 zero at isasama ang number string sa A2 at ibinabalik ang resulta. Pagkatapos, inilapat ang formula sa cell B2:B6 gamit ang fill handle.
Ang formula sa itaas ay nagdaragdag ng isang nakapirming bilang ng mga zero bago ang numero, ngunit ang kabuuang haba ng numero ay nag-iiba depende sa numero.
Kung gusto mong magdagdag ng mga nangungunang zero saanman kinakailangan upang lumikha ng isang partikular na character na mahaba (fixed length) na mga string, maaari mong gamitin ang REPT at LEN function nang magkasama.
Syntax:
=REPT(text, number_times-LEN(text))&cellHalimbawa, upang magdagdag ng mga prefix na zero sa value sa A2 at gumawa ng 5-character na mahabang string, subukan ang formula na ito:
=REPT(0,5-LEN(A2))&A2Dito, nakukuha ng 'LEN(A2)' ang kabuuang haba ng string/mga numero sa cell A2. Ang '5' ay ang maximum na haba ng string/numero na dapat mayroon ang cell. At ang bahagi ng 'REPT(0,5-LEN(A2))' ay nagdaragdag ng bilang ng mga zero sa pamamagitan ng pagbabawas ng haba ng string sa A2 mula sa maximum na bilang ng mga zero (5). Pagkatapos, ang isang numero ng 0 ay pinagsama bago ang halaga ng A2 upang makagawa ng isang nakapirming-haba na string.
Gamit ang RIGHT Function
Ang isa pang paraan upang i-pad ang mga nangungunang zero bago ang isang string sa Excel ay ang paggamit ng RIGHT function.
Ang RIGHT function ay maaaring magdagdag ng bilang ng mga zero sa simula ng isang numero at i-extract ang pinakakanang N character mula sa value.
Syntax:
= RIGHT (text, num_chars)- text ay ang cell o value kung saan mo gustong kunin ang mga character.
- num_chars ay ang bilang ng mga character na kukunin mula sa teksto. Kung hindi ibinigay ang argumentong ito, ang unang character lang ang makukuha.
Para sa pamamaraang ito, pinagsasama-sama namin ang maximum na bilang ng mga zero sa cell reference na naglalaman ng string sa argument na 'text'.
Upang lumikha ng 6 na digit na numero batay sa string ng numero sa A na may mga nangungunang zero, subukan ang formula na ito:
=RIGHT("0000000"&A2,6)Ang unang argumento (teksto) ng formula ay nagdaragdag ng 7 zero sa value sa A2 (“0000000”&A2), at pagkatapos ay ibinabalik ang pinakakanang 7 character, na nagreresulta sa ilang nangungunang mga zero.
Pagdaragdag ng Mga Nangungunang Zero gamit ang Custom Number Formatting
Kung gagamitin mo ang alinman sa mga pamamaraan sa itaas upang ilagay ang mga nangungunang zero bago ang mga numero, palagi kang makakakuha ng string ng teksto, hindi isang numero. At hindi sila gaanong magagamit sa mga kalkulasyon o sa mga numeric na formula.
Ang pinakamahusay na paraan upang magdagdag ng mga nangungunang zero sa Excel ay maglapat ng custom na pag-format ng numero. Kung magdaragdag ka ng mga nangungunang zero sa pamamagitan ng pagdaragdag ng custom na format ng numero sa cell, hindi nito babaguhin ang halaga ng cell ngunit ang paraan lamang ng pagpapakita nito. Ang halaga ay mananatili pa rin bilang isang numero, hindi text.
Upang baguhin ang pag-format ng numero ng mga cell, sundin ang mga hakbang na ito:
Piliin ang cell o hanay ng mga cell kung saan mo gustong ipakita ang mga nangungunang zero. Pagkatapos, i-right-click kahit saan sa loob ng napiling hanay na iyon at piliin ang opsyong ‘Format Cells’ mula sa menu ng konteksto. O pindutin ang mga shortcut key na Ctrl + 1.
Sa window ng Format Cells, pumunta sa tab na ‘Number’ at piliin ang ‘Custom’ sa ilalim ng mga opsyon sa Kategorya.
Ilagay ang bilang ng mga zero sa kahon na ‘Uri:’ upang tukuyin ang kabuuang bilang ng mga digit na gusto mong ipakita sa isang cell. Halimbawa, kung gusto mong maging 6 na digit ang haba, ilagay ang '000000' bilang custom na format code. Pagkatapos, i-click ang 'OK' para mag-apply.
Ipapakita nito ang mga nangungunang zero bago ang mga numero at kung ang numero ay mas mababa sa 6 na numero, ito ay magiging zero bago ito.
Ang mga numero ay lilitaw lamang na may mga nangungunang zero habang ang pinagbabatayan na halaga ay mananatiling hindi magbabago. Kung pipili ka ng cell na may custom na pag-format, ipapakita nito sa iyo ang orihinal na numero sa formula bar
Mayroong maraming mga digital na placeholder na magagamit mo sa iyong custom na format ng numero. Ngunit mayroon lamang dalawang pangunahing placeholder na magagamit mo para sa pagdaragdag ng mga nangungunang zero sa mga numero.
- 0 – Ito ang digit na placeholder na nagpapakita ng mga dagdag na zero. Nagpapakita ito ng mga sapilitang digit na 0-9 kung ang digit ay may kaugnayan o hindi sa halaga. Halimbawa, kung nagta-type ka ng 2.5 na may format na code 000.00, ito ay magpapakita ng 002.50.
- # – Ito ang digit na placeholder na nagpapakita ng mga opsyonal na digit at hindi kasama ang mga karagdagang zero. Halimbawa, kung nagta-type ka ng 123 na may formatting code na 000#, ipapakita nito ang 0123.
Gayundin, ang anumang bantas na marka o iba pang karakter na isasama mo sa format na code ay ipapakita kung ano ito. Maaari kang gumamit ng mga character tulad ng hyphen (-), comma (,), forward-slash (/), atbp.
Halimbawa, maaari mo ring i-format ang mga numero bilang mga numero ng telepono sa pamamagitan ng paggamit ng custom na format.
Ang Format code sa Format Cells dialog box:
Ang resulta:
Ilapat natin ang formatting code na ito sa sumusunod na halimbawa:
##0000
Tulad ng nakikita mo, ang '0' ay magdaragdag ng mga karagdagang zero habang ang '#' ay hindi nagdaragdag ng mga hindi gaanong halaga:
Maaari ka ring gumamit ng mga paunang natukoy na format code sa seksyong ‘Mga espesyal na format’ ng dialog box ng Format cells para sa mga postal code, numero ng telepono, at social security number.
Ang sumusunod na talahanayan ay nagpapakita ng mga numero na may mga nangungunang zero kung saan ang iba't ibang 'Espesyal' na format ng mga code ay inilalapat sa iba't ibang mga column:
Pag-alis ng Mga Nangungunang Zero sa Excel
Ngayon, natutunan mo na kung paano magdagdag ng mga nangungunang zero sa Excel, tingnan natin kung paano alisin ang mga nangungunang zero mula sa bilang ng mga string. Minsan, kapag nag-import ka ng data mula sa isang panlabas na pinagmulan, maaaring magkaroon ng prefix zero ang mga numero at ma-format bilang text. Sa ganitong mga kaso, kailangan mong alisin ang mga nangungunang zero at i-convert ang mga ito pabalik sa mga numero, para magamit mo ang mga ito sa mga formula.
Mayroong iba't ibang mga paraan na maaari mong alisin ang mga nangungunang zero sa Excel at makikita namin ang mga ito nang paisa-isa.
Alisin ang Mga Nangungunang Zero sa pamamagitan ng Pagbabago sa Cell Formatting
Kung ang mga nangungunang zero ay idinagdag sa pamamagitan ng custom na pag-format ng numero, madali mong maalis ang mga ito sa pamamagitan ng pagbabago sa format ng mga cell. Malalaman mo kung custom na format ang iyong mga cell sa pamamagitan ng pagtingin sa address bar (makikita ang mga zero sa cell hindi sa address bar).
Upang alisin ang mga prefix na zero, piliin ang mga cell na may mga nangungunang zero, mag-click sa kahon ng 'Number Format', at piliin ang 'General' o 'Number' na opsyon sa pag-format.
Ngayon, wala na ang mga nangungunang zero:
Tanggalin ang Mga Nangungunang Zero sa pamamagitan ng Pag-convert ng Teksto sa Mga Numero
Kung ang iyong mga nangungunang zero ay idinagdag sa pamamagitan ng pagbabago sa format ng cell o sa pamamagitan ng pagdaragdag ng mga kudlit bago ang mga numero o awtomatikong idinagdag kapag nag-i-import ng data, ang pinakamadaling paraan upang i-convert ang mga ito sa mga numero ay sa pamamagitan ng paggamit ng Error Checking Option. Narito kung paano mo ito gagawin:
Magagamit mo ang paraang ito kung ang iyong mga numero ay naka-left-align at ang iyong mga cell ay may maliit na berdeng tatsulok (Isang tagapagpahiwatig ng error) sa kaliwang sulok sa itaas ng mga cell. Nangangahulugan ito na ang mga numero ay naka-format bilang teksto.
Piliin ang mga cell na iyon at makakakita ka ng dilaw na babala sa kanang bahagi sa itaas ng pagpili. Pagkatapos, i-click ang opsyong ‘I-convert sa Numero’ mula sa drop-down.
Aalisin ang iyong mga zero at ibabalik ang mga numero sa format ng numero (Naka-align sa kanan).
Pag-alis ng Mga Nangungunang Zero ni Multiply/Dividing sa 1
Ang isa pang madali at pinakamahusay na paraan upang alisin ang nangunguna ay sa pamamagitan ng pag-multiply o paghahati ng mga numero na may 1. Hindi binabago ng paghahati o pagpaparami ng halaga ang halaga, binabago lang nito ang halaga pabalik sa isang numero at inaalis ang mga nangungunang zero.
Upang gawin ito, i-type ang formula sa halimbawa sa ibaba sa isang cell at pindutin ang ENTER. Ang mga nangungunang zero ay aalisin at ang string ay ibabalik sa isang numero.
Pagkatapos, ilapat ang formula na ito sa ibang mga cell gamit ang fill handle.
Maaari mong makamit ang parehong mga resulta sa pamamagitan ng paggamit ng 'Paste Special' Command. Narito kung paano:
I-type ang '1' numeric value sa isang cell (sabihin natin sa B2) at kopyahin ang value na iyon.
Susunod, piliin ang mga cell kung saan mo gustong alisin ang mga nangungunang zero. Pagkatapos, mag-right-click sa pagpili at pagkatapos ay piliin ang opsyon na 'I-paste ang Espesyal'.
Sa I-paste ang Espesyal na dialog box, sa ilalim ng Operation, piliin ang alinman sa 'Multiply' o 'Divide' na opsyon at i-click ang 'OK'.
Iyon lang, ang iyong mga nangungunang zero ay aalisin na iniiwan ang mga string bilang mga numero.
Alisin ang Mga Nangungunang Zero sa pamamagitan ng paggamit ng isang Formula
Ang isa pang madaling paraan upang tanggalin ang mga prefix na zero ay sa pamamagitan ng paggamit ng VALUE function. Maaaring maging kapaki-pakinabang ang pamamaraang ito kung idinagdag ang iyong mga nangungunang zero sa pamamagitan ng paggamit ng isa pang formula o apostrophe o sa pamamagitan ng custom na pag-format.
=VALUE(A1)Ang argument ng formula ay maaaring isang value o ang cell reference na may value. Tinatanggal ng formula ang mga nangungunang zero at kino-convert ang halaga mula sa teksto patungo sa numero. Pagkatapos ay ilapat ang formula sa natitirang mga cell.
Minsan, maaaring gusto mong alisin ang mga nangungunang zero ngunit nais mong panatilihin ang mga numero sa format ng teksto. Sa ganitong mga kaso, kailangan mong gamitin ang TEXT() at VALUE () function nang magkasama tulad nito:
=TEXT(VALUE(A1),"#")Kino-convert ng VALUE function ang value sa A1 sa numero. Ngunit ang pangalawang argumento, ang '#' ay nagko-convert ng halaga pabalik sa format ng teksto nang walang anumang karagdagang mga zero. Bilang resulta, makukuha mo ang mga numero nang walang anumang nangungunang mga zero ngunit nasa format pa rin ng teksto (naka-align sa kaliwa).
Alisin ang Mga Nangungunang Zero gamit ang Text to Columns Feature ng Excel
Ang isa pang paraan upang alisin ang mga nangungunang zero ay ang paggamit ng tampok na Text to Columns ng Excel.
Piliin ang hanay ng mga cell na may mga numerong may mga nangungunang zero.
Susunod, pumunta sa tab na ‘Data’ at mag-click sa button na ‘Text to Columns’ sa pangkat na Mga Tool ng Data.
Lalabas ang wizard ng ‘Convert Text to Columns’. Sa hakbang 1 ng 3, piliin ang ‘Delimited’ at i-click ang ‘Next’.
Sa hakbang 2 ng 3, alisan ng check ang lahat ng delimiter at i-click ang 'Next'.
Sa huling hakbang, iwanan ang opsyon sa format ng data ng Column bilang 'Pangkalahatan' at piliin ang patutunguhan (ang unang cell ng hanay) kung saan mo gustong ang iyong mga numero nang walang mga nangunguna sa zero. Pagkatapos, i-click ang 'Tapos na'
At makukuha mo ang mga numerong may nangunguna na inalis sa isang hiwalay na column tulad ng ipinapakita sa ibaba.
Yun lang.