Bagaimana Menambahkan Nol Awal di Excel

Tutorial ini mencakup berbagai metode untuk menambah atau mempertahankan angka nol di depan serta menghapus angka nol di depan di Excel.

Setiap kali Anda memasukkan atau mengimpor angka dengan satu atau beberapa angka nol di depan, seperti 000652, Excel secara otomatis menghapus angka nol tersebut, dan hanya angka itu sendiri yang ditampilkan di sel (652). Ini karena angka nol di depan tidak diperlukan untuk perhitungan dan tidak dihitung.

Namun, ada kalanya angka nol di depan itu diperlukan, seperti saat Anda memasukkan nomor ID, nomor telepon, nomor kartu kredit, kode produk, atau kode pos, dll. Untungnya, Excel memberi kita beberapa cara untuk menambahkan atau mempertahankan angka nol di depan. dalam sel. Pada artikel ini, Kami akan menunjukkan kepada Anda berbagai cara untuk menambah atau mempertahankan nol di depan dan menghapus nol di depan.

Menambahkan Nol Utama di Excel

Pada dasarnya, ada 2 metode yang dapat Anda gunakan untuk menambahkan nol di depan: satu, format nomor Anda sebagai 'Teks'; dua, gunakan pemformatan khusus untuk menambahkan angka nol di depan. Metode yang ingin Anda gunakan mungkin bergantung pada apa yang ingin Anda lakukan dengan nomor tersebut.

Anda mungkin ingin menambahkan nol di depan saat Anda memasukkan nomor ID unik, nomor rekening, nomor jaminan sosial, atau kode pos, dll. Namun, Anda tidak akan menggunakan angka-angka ini untuk penghitungan atau dalam fungsi, jadi yang terbaik adalah mengonversinya angka menjadi teks. Anda tidak akan pernah menjumlahkan atau rata-rata nomor telepon atau nomor rekening.

Ada beberapa cara Anda dapat menambahkan atau memasukkan angka nol sebelum angka dengan memformatnya sebagai teks:

  • Mengubah format sel menjadi Teks
  • Menambahkan apostrof (‘)
  • Menggunakan fungsi TEKS
  • Menggunakan Fungsi REPT/LEN
  • Gunakan Fungsi CONCATENATE/operator Ampersand (&)
  • Menggunakan fungsi KANAN

Mengubah Format Sel menjadi Teks

Ini adalah salah satu cara paling sederhana untuk menambahkan angka nol di depan angka Anda. Jika Anda hanya akan memasukkan angka dan Anda ingin mempertahankan angka nol di depan saat Anda mengetik, maka ini adalah metode untuk Anda. Dengan mengubah format sel dari Umum atau Angka ke Teks, Anda bisa memaksa Excel untuk memperlakukan angka Anda sebagai nilai teks dan apa pun yang Anda ketik di sel akan tetap sama persis. Inilah cara Anda melakukannya:

Pilih sel di mana Anda ingin menambahkan nol di depan. Buka tab 'Beranda', klik kotak tarik-turun 'Format' di grup Angka, dan pilih 'Teks' dari opsi format.

Sekarang ketika Anda mengetikkan nomor Anda, Excel tidak akan menghapus nol di depannya.

Anda mungkin melihat segitiga hijau kecil (indikator kesalahan) di sudut kiri atas sel dan ketika Anda memilih sel itu, itu akan menunjukkan kepada Anda tanda peringatan yang menunjukkan bahwa Anda telah menyimpan nomor sebagai teks.

Untuk menghapus pesan kesalahan, pilih sel, klik tanda peringatan, lalu pilih 'Abaikan Kesalahan' dari daftar.

Anda juga dapat mengetikkan nomor telepon dengan spasi atau tanda hubung di antara nomor, Excel akan secara otomatis memperlakukan nomor ini sebagai teks.

Menggunakan Terkemuka Apostrof ( ' )

Cara lain untuk menambahkan nol di depan di Excel adalah dengan menambahkan apostrof (‘) di awal angka. Ini akan memaksa Excel untuk memasukkan angka sebagai teks.

Cukup ketik apostrof sebelum angka apa pun dan tekan 'Enter'. Excel akan membiarkan nol di depan tetap utuh, tetapi (‘) tidak akan terlihat di lembar kerja kecuali jika Anda memilih sel.

Menggunakan Fungsi Teks

Metode di atas menambahkan angka nol ke angka saat Anda mengetiknya, tetapi jika Anda sudah memiliki daftar angka dan ingin menambahkan angka nol di depannya, maka fungsi TEXT adalah metode yang tepat untuk Anda. Fungsi TEXT memungkinkan Anda mengonversi angka menjadi string teks sambil menerapkan pemformatan khusus.

Sintaks fungsi TEXT:

= TEKS(nilai, format_teks)

Di mana,

  • nilai - Ini adalah nilai numerik yang perlu Anda konversi ke teks dan menerapkan pemformatan.
  • format_teks – adalah format yang ingin Anda terapkan.

Dengan fungsi TEXT, Anda dapat menentukan berapa banyak angka yang harus dimiliki nomor Anda. Misalnya, jika Anda ingin nomor Anda terdiri dari 8 digit, ketikkan 8 angka nol pada argumen fungsi kedua: “00000000”. Jika Anda memiliki 6 digit angka dalam sel, maka fungsi akan secara manual menambahkan 2 angka nol di depan dan jika Anda memiliki 2 digit angka seperti 56, sisanya akan menjadi nol (00000056).

Misalnya, untuk menambahkan nol di depan dan membuat angka menjadi 6 digit, gunakan rumus ini:

=TEXT(A2,"000000")

Karena kita memiliki 6 angka nol dalam argumen kedua dari rumus, fungsi mengubah string angka menjadi string teks dan menambahkan 5 angka nol di depan untuk membuat string menjadi 6 digit.

Catatan: Harap ingat untuk menyertakan kode format dalam tanda kutip ganda dalam fungsi.

Sekarang Anda dapat menerapkan rumus yang sama ke sel lainnya dengan menyeret gagang isian. Seperti yang Anda lihat, fungsi ini mengubah angka menjadi teks dan menambahkan angka nol di depan angka sehingga jumlah digitnya adalah 6.

Fungsi TEXT akan selalu mengembalikan nilai sebagai string teks, bukan angka, jadi Anda tidak akan dapat menggunakannya dalam perhitungan aritmatika tetapi Anda masih dapat menggunakannya dalam rumus pencarian seperti VLOOKUP atau INDEX/MATCH untuk mengambil detail dari produk menggunakan ID Produk.

Menggunakan Fungsi CONCATENATE/Ampersand Operator (&)

Jika Anda ingin menambahkan angka tetap dari nol di depan semua angka dalam kolom, Anda dapat menggunakan fungsi CONCATENATE atau operator ampersand (&).

Sintaks fungsi CONCATENATE:

=CONCATENATE(teks1, [teks2], ...)

Di mana,

Teks 1 - Jumlah nol yang akan disisipkan sebelum angka.

teks2 – Nomor asli atau referensi sel

Sintaks operator Ampersand:

=Nilai_1 & Nilai_2

Di mana,

Nilai_1 adalah angka nol di depan yang harus disisipkan sebelum angka dan Nilai_2 adalah angkanya.

Misalnya, untuk menambahkan hanya dua angka nol sebelum angka, gunakan salah satu dari rumus ini:

=CONCATENATE("00",A2)

Argumen pertama adalah dua angka nol (“00”) karena kita ingin mengisi dua angka nol sebelum angka di A2 (yang merupakan argumen kedua).

Atau, 

="00"&A2

Di sini, argumen pertama adalah 2 nol, diikuti oleh operator '&', dan argumen kedua adalah angka.

Seperti yang Anda lihat, rumus menambahkan hanya dua angka nol di depan ke semua angka dalam kolom terlepas dari berapa banyak digit yang ada di angka tersebut.

Kedua rumus ini menggabungkan sejumlah nol sebelum angka asli dan menyimpannya sebagai string teks.

Menggunakan Fungsi REPT/LEN

Jika Anda ingin menambahkan angka nol di depan ke data numerik atau alfanumerik dan mengubah string menjadi teks, gunakan fungsi REPT. Fungsi REPT digunakan untuk mengulang karakter beberapa kali. Fungsi ini juga dapat digunakan untuk menyisipkan angka tetap dari nol di depan angka tersebut.

=REPT(teks, bilangan_kali)

Di mana 'teks' adalah karakter yang ingin kita ulangi (dalam kasus kita '0') dan argumen 'number_times' adalah berapa kali kita ingin mengulang karakter itu.

Misalnya, untuk menghasilkan lima nol sebelum angka, rumusnya akan terlihat seperti:

=REPT(0,5)&A2

Apa yang dilakukan rumus adalah mengulang 5 angka nol dan menggabungkan string angka di A2 dan mengembalikan hasilnya. Kemudian, rumus diterapkan ke sel B2:B6 menggunakan gagang isian.

Rumus di atas menambahkan angka nol yang tetap sebelum angka, tetapi panjang total angka bervariasi tergantung pada angka.

Jika Anda ingin menambahkan nol di depan di mana pun diperlukan untuk membuat string panjang karakter tertentu (panjang tetap), Anda dapat menggunakan fungsi REPT dan LEN bersama-sama.

Sintaksis:

=REPT(teks, number_times-LEN(teks))&sel

Misalnya, untuk menambahkan awalan nol ke nilai di A2 dan membuat string sepanjang 5 karakter, coba rumus ini:

=REPT(0,5-LEN(A2))&A2

Di sini, 'LEN(A2)' mendapatkan total panjang string/angka di sel A2. '5' adalah panjang maksimum string/angka yang harus dimiliki sel. Dan bagian 'REPT(0,5-LEN(A2))' menambahkan jumlah nol dengan mengurangkan panjang string di A2 dari jumlah maksimum nol (5). Kemudian, sejumlah 0 digabungkan sebelum nilai A2 untuk membuat string dengan panjang tetap.

Menggunakan Fungsi KANAN

Cara lain untuk memasukkan angka nol di depan sebelum string di Excel adalah menggunakan fungsi KANAN.

Fungsi KANAN dapat menambahkan angka nol ke awal angka dan mengekstrak karakter N paling kanan dari nilainya.

Sintaksis:

= KANAN (teks, num_chars)
  • teks adalah sel atau nilai yang ingin Anda ekstrak karakternya.
  • jumlah_karakter adalah jumlah karakter yang akan diekstrak dari teks. Jika argumen ini tidak diberikan, maka hanya karakter pertama yang akan diekstraksi.

Untuk metode ini, kami menggabungkan jumlah maksimum nol dengan referensi sel yang berisi string dalam argumen 'teks'.

Untuk membuat angka 6 digit berdasarkan string angka di A dengan nol di depan, coba rumus ini:

=KANAN("0000000"&A2,6)

Argumen pertama (teks) dari rumus menambahkan 7 nol ke nilai di A2 (“0000000”&A2), lalu mengembalikan 7 karakter paling kanan, yang menghasilkan beberapa nol di depan.

Menambahkan Nol Awal menggunakan Pemformatan Angka Kustom

Jika Anda menggunakan salah satu metode di atas untuk meletakkan angka nol di depan angka, Anda akan selalu mendapatkan string teks, bukan angka. Dan mereka tidak akan banyak digunakan dalam perhitungan atau dalam rumus numerik.

Cara terbaik untuk menambahkan angka nol di depan di Excel adalah dengan menerapkan pemformatan angka kustom. Jika Anda menambahkan nol di depan dengan menambahkan format angka kustom ke sel, itu tidak mengubah nilai sel tetapi hanya cara itu ditampilkan. Nilainya akan tetap berupa angka, bukan teks.

Untuk mengubah pemformatan angka sel, ikuti langkah-langkah berikut:

Pilih sel atau rentang sel tempat Anda ingin menampilkan angka nol di depan. Kemudian, klik kanan di mana saja dalam rentang yang dipilih dan pilih opsi 'Format Sel' dari menu konteks. Atau tekan tombol pintas Ctrl + 1.

Di jendela Format Sel, buka tab 'Nomor' dan pilih 'Kustom' di bawah opsi Kategori.

Masukkan jumlah nol di kotak 'Jenis:' untuk menentukan jumlah total digit yang ingin Anda tampilkan dalam sel. Misalnya, jika Anda ingin nomor tersebut panjangnya 6 digit, masukkan '000000' sebagai kode format khusus. Kemudian, klik 'OK' untuk mendaftar.

Ini akan menunjukkan angka nol di depan sebelum angka dan jika angkanya kurang dari 6 digit, angkanya nol di depannya.

Angka hanya akan tampak memiliki nol di depan sementara nilai dasarnya tidak akan berubah. Jika Anda memilih sel dengan pemformatan khusus, itu akan menunjukkan kepada Anda nomor asli di bilah rumus

Ada banyak placeholder digital yang dapat Anda gunakan dalam format nomor kustom Anda. Tetapi hanya ada dua placeholder utama yang dapat Anda gunakan untuk menambahkan angka nol di depan.

  • 0 – Ini adalah placeholder digit yang menampilkan nol ekstra. Ini menampilkan digit paksa 0-9 apakah digit tersebut relevan dengan nilai atau tidak. Misalnya, jika Anda mengetik 2.5 dengan kode format 000.00, maka akan muncul 002.50.
  • # – Ini adalah tempat penampung digit yang menampilkan digit opsional dan tidak termasuk nol tambahan. Misalnya, jika Anda mengetik 123 dengan kode pemformatan 000#, maka akan muncul 0123.

Selain itu, tanda baca atau karakter lain yang Anda sertakan dalam kode format akan ditampilkan apa adanya. Anda dapat menggunakan karakter seperti tanda hubung (-), koma (,), garis miring (/), dll.

Misalnya, Anda juga dapat memformat angka sebagai nomor telepon dengan menggunakan format kustom.

Kode Format dalam kotak dialog Format Sel:

Hasil:

Mari kita terapkan kode pemformatan ini dalam contoh berikut:

##0000

Seperti yang Anda lihat '0' akan menambahkan nol ekstra sementara '#' tidak menambahkan nol yang tidak signifikan:

Anda juga dapat menggunakan kode format yang telah ditentukan sebelumnya di bagian 'Format khusus' pada kotak dialog Format sel untuk kode pos, nomor telepon, dan nomor jaminan sosial.

Tabel berikut menunjukkan angka dengan nol di depan di mana kode format 'Khusus' yang berbeda diterapkan ke kolom yang berbeda:

Menghapus Nol Awal di Excel

Sekarang, Anda telah belajar cara menambahkan nol di depan di Excel, mari kita lihat cara menghapus nol di depan dari jumlah string. Terkadang, saat Anda mengimpor data dari sumber eksternal, angka mungkin berakhir dengan awalan nol dan diformat sebagai teks. Dalam kasus seperti itu, Anda perlu menghapus nol di depan dan mengubahnya kembali menjadi angka, sehingga Anda dapat menggunakannya dalam rumus.

Ada berbagai cara Anda dapat menghapus angka nol di depan di Excel dan kita akan melihatnya satu per satu.

Hapus Leading Zeros dengan Mengubah Pemformatan Sel

Jika angka nol di depan ditambahkan dengan pemformatan angka khusus, Anda dapat dengan mudah menghapusnya dengan mengubah format sel. Anda dapat mengetahui apakah sel Anda diformat khusus dengan melihat bilah alamat (nol akan terlihat di sel, bukan di bilah alamat).

Untuk menghapus awalan nol, pilih sel dengan nol di depan, klik kotak 'Format Angka', dan pilih opsi pemformatan 'Umum' atau 'Nomor'.

Sekarang, nol di depan hilang:

Hapus Nol Awal dengan Mengonversi Teks menjadi Angka

Jika angka nol di depan Anda ditambahkan dengan mengubah format sel atau dengan menambahkan apostrof sebelum angka atau ditambahkan secara otomatis saat mengimpor data, cara termudah untuk mengubahnya menjadi angka adalah dengan menggunakan Opsi Pemeriksaan Kesalahan. Inilah cara Anda melakukannya:

Anda dapat menggunakan metode ini jika nomor Anda rata kiri dan sel Anda memiliki segitiga hijau kecil (Indikator kesalahan) di sudut kiri atas sel. Ini berarti angka-angka diformat sebagai teks.

Pilih sel-sel itu dan Anda akan melihat peringatan kuning di bagian kanan atas pilihan. Kemudian, klik opsi 'Konversikan ke Angka' dari drop-down.

Angka nol Anda akan dihapus dan angka-angka akan diubah kembali ke format angka (Rata kanan).

Menghapus Nol Utama dengan Kalikan / Bagi dengan 1

Cara lain yang mudah dan terbaik untuk menghilangkan awalan adalah dengan mengalikan atau membagi angka dengan 1. Membagi atau mengalikan nilai tidak mengubah nilai, itu hanya mengubah nilai kembali menjadi angka dan menghilangkan nol di depan.

Untuk melakukannya, ketikkan rumus dalam contoh di bawah ini dalam sel dan tekan ENTER. Angka nol di depan akan dihapus dan string akan diubah kembali menjadi angka.

Kemudian, terapkan rumus ini ke sel lain menggunakan gagang isian.

Anda dapat mencapai hasil yang sama dengan menggunakan Perintah 'Tempel Spesial'. Begini caranya:

Ketik nilai numerik '1' dalam sel (katakanlah di B2) dan salin nilai itu.

Selanjutnya, pilih sel di mana Anda ingin menghapus nol di depan. Kemudian, klik kanan pada pilihan dan kemudian pilih opsi 'Tempel Spesial'.

Di kotak dialog Tempel Spesial, di bawah Operasi, pilih opsi 'Multiply' atau 'Divide' dan klik 'OK'.

Itu saja, nol di depan Anda akan dihapus meninggalkan string sebagai angka.

Hapus Leading Zeros dengan menggunakan Rumus

Cara lain yang mudah untuk menghapus awalan nol adalah dengan menggunakan fungsi VALUE. Metode ini dapat berguna baik nol di depan Anda ditambahkan dengan menggunakan rumus atau apostrof lain atau dengan pemformatan khusus.

=VALUE(A1)

Argumen rumus bisa berupa nilai atau referensi sel yang memiliki nilai. Rumus menghilangkan angka nol di depan dan mengonversi nilai dari teks menjadi angka. Kemudian terapkan rumus ke sel lainnya.

Terkadang, Anda mungkin ingin menghapus nol di depan tetapi ingin menyimpan angka dalam format teks. Dalam kasus seperti itu, Anda harus menggunakan fungsi TEXT() dan VALUE() bersama-sama seperti ini:

=TEXT(VALUE(A1),"#")

Fungsi VALUE mengonversi nilai dalam A1 menjadi angka. Tetapi argumen kedua, '#' mengubah nilai kembali ke format teks tanpa nol tambahan. Akibatnya, Anda akan mendapatkan angka tanpa nol di depan tetapi masih dalam format teks (rata kiri).

Hapus Leading Zeros menggunakan Teks Excel ke Fitur Kolom

Cara lain untuk menghilangkan angka nol di depan adalah dengan menggunakan fitur Text to Columns dari Excel.

Pilih rentang sel yang memiliki angka dengan nol di depan.

Selanjutnya, buka tab 'Data' dan klik tombol 'Teks ke Kolom' di grup Alat Data.

Wizard 'Konversi Teks ke Kolom' akan muncul. Pada langkah 1 dari 3, pilih 'Dibatasi' dan klik 'Berikutnya'.

Pada langkah 2 dari 3, hapus centang semua pembatas dan klik 'Berikutnya'.

Pada langkah terakhir, biarkan opsi format data Kolom sebagai 'Umum' dan pilih tujuan (sel pertama dari rentang) di mana Anda ingin nomor Anda tanpa nol di depan. Kemudian, klik 'Selesai'

Dan Anda akan mendapatkan nomor dengan memimpin dihapus di kolom terpisah seperti yang ditunjukkan di bawah ini.

Itu semuanya.