Penulis
Assalamualaikum,
Pagi ini saya akan membagikan 3 tutorial excel yang digabungkan dalam 1 artikel ini. Yaitu :
1. Melengkapi tanggal lahir dari 110475 menjadi 11041975
2. Merubah angka 11041975 itu menjadi format date yaitu 11/04/1975
3. Dan yang terakhir menambahkan tempat lahir Rantau Bais seperti Rantau Bais, 11/04/1975
Cara diatas sebenarnya cukup mudah untuk diterapkan, namun setelah saya sadar kalau sekitar 5-10 baris data maka tidaklah lama.
Namun, kalau data yang akan kita rubah itu sebanyak ratusan sampai ribuan baris data maka dalam pengerjaannya bisa sampai seharian bahkan sambung keesokan harinya.
Karenanya kita harus membuat cara cepat untuk mengakali itu semua.
1. Cara pertama yaitu melengkapi tanggal lahir 110475 menjadi 11041975:
a. Kita cari sel yang paling bawah atau sel yang kosong tempat kita mengolah tanggal lahir
b. Kita pisahkan tanggal lahir tersebut dalam 3 sel, sel 1 (11) sel 2 (04) dan sel 3 (75)
c. Caranya :
- Pilih sel yang pertama tadi dan tulis skrip =LEFT(G15;2)
- Pilih sel yang kedua dan tulis skrip =MID(G15;3;2)
- Dan pada sel yang ketiga tulis skrip =RIGHT(G15;2)
d. G15 adalah tempat sel tanggal lahir itu berada, kolom G baris 15
e. Jika caranya benar akan menjadi seperti gambar di atas 11 04 75
f. Lalu menambahkan 19 didepan 2 angka terakhir tahun lahir menjadi 1975. Caranya:
- Klik sel dimana angka 75 berada
- Lalu tekan Alt+F11 untuk menampilkan Microsoft Visual Basic for Applications
- Klik 2x Sheet1 tempat sheet yang akan kita terapkan rumus
- Muncul kotak dialog kosong, lalu copy paste kan kode berikut :
Option Explicit
Sub addapostrophe()
Dim cell As Range
For Each cell In Range("I39:I" & Range("I65536").End(xlUp).Row)
cell.Select
If cell.Value <> "" Then
cell.Value = "19" & cell.Value
End If
Next
End Sub
- I39 adalah sel tempat angka 75 tadi, (I) berarti kolom I, dan sesuaikan dengan kolom tempat anda meletakkan angka yang ingin ditambah
- Angka 19 adalah angka yang ingin kita tambahkan menjadi 1975
- Setelah itu tekan Segitiga Hijau untuk menjalankan skrip atau tekan F5 dan close. Lihat perubahan yang terjadi di kolom angka 75 tadi, maka akan menjadi 1975
2. Cara kedua untuk membuat 11/04/1975 dari file sel 1 (11) sel 2 (04) dan sel 3 (1975):
- Pilih sel baru yang kosong dan tuliskan =DATE(J39;H39;G39)
- Formatnya adalah =DATE(year;month;day), year (sel 3), month (sel 2), day (sel 1). Memang dalam keadaan terbalik tapi hasilnya akan menjadi sebaliknya, Tanggal, Bulan, Tahun (11/04/1975). (lihat gambar paling atas)
3. Cara ketiga untuk menggabungkan Tempat Lahir dan Tanggal Lahir menjadi Rantau Bais, 11/04/1975, caranya :
- Klik sel kosong dan pastekan skrip =F15&", "&TEXT(K39;"dd/mm/yyy")
- F15 adalah sel tempat lahir itu berada dan K39 adalah sel tempat 11/04/1975 berada
- Jika menerapkan skripnya dengan benar maka perubahannya akan nampak
Jika telah berhasil menggunakan skrip di 1 sel maka anda hanya perlu menarik/sort ke bawah untuk menerapkannya sampai ke bawah batas sel yang anda ingin rubah.
Mudah bukan ? Tidak perlu kawatir jika ingin menambah, mengurangi angka/tanda/huruf tertentu walaupun data sebanyak puluhan atau ratusan baris.
Catatan saya jika angka tahun lahirnya 110400 itu kan artinya 11/04/2000 (karena saat menulis artikel ini masih tahun 2000-an) dan khusus untuk tahun lahir tersebut saya buatkan dengan manual saja. Jika kita menggunakan cara diatas maka hasil dari 110400 menjadi 1900. Maka, kalau ada cara baru, saran baru khusus hal ini bisa berkomentar di bawah atau hubungi kontak email saya.
Sebagai bonus artikel yang saya buat, cara untuk menambahkan angka 19 di depan angka 75 juga bisa diterapkan untuk menambahkan tanda petik satu (') di depan no. ktp, no kk, no hp dan nomor identitas lainnya.
Biasanya jika kita tidak menuliskan tanda petik di depan nomor identitas maka nomor itu bisa berubah dengan sendirinya.
Ex: 1472023107930001 menjadi 1472023107930000, itu akan menjadi kesalahan data yang fatal jika setiap ujung nomor akan berubah jadi 0000.
Jika kita mengetikkan nomor identitas jangan lupa mengetikkan tanda petik satu di awal angkanya, agar angka tidak berubah.
Namun, jika itu data hasil copy paste maka kita perlu menambahkan tanda petiknya dengan cara yang tadi.
Kalau nomor hp tidak ditambahkan tanda petik satu di awal maka angka 0 nya hilang.
Sebagai tutorial tambahan saya bagikan juga cara menghapus salah satu kata, simbol, angka, huruf dalam 1 sel.
Misalnya dalam 20 baris sel berisi NISN. 9753675251 dan kita ingin menghapus NISN. nya saja. Maka kita hanya perlu blok 20 baris sel yang berisi nisn tadi dan kita gunakan perintah Replice (CTRL+F).
Tuliskan di (Find what: NISN.) sebagai kata yang akan kita hapus. Dan untuk (Replace with: ) kita biarkan saja kosong, lalu tekan saja Replace atau Replace All. Maka semua kata NISN. tadi akan terhapus dengan sendirinya di dalam sel.
Saya kira sekian untuk tutorial excel ini, jika ada lagi ilmu baru (bagi saya) maka saya akan bagikan di tutorial website ini. Terima kasih para pembaca.