Jean mengajukan pertanyaan Excel minggu ini:
Saya mencoba mengonversi buku harga pemasok di Excel untuk mencocokkan kode UPC mereka dengan sistem kode UPC kami. Buku harga mereka memiliki kolom di Excel dengan serangkaian angka misalnya. 000004560007 ATAU cel000612004. Yang harus saya lakukan adalah dua kali lipat. Saya perlu menghilangkan tiga nol pertama tanpa menghapus 0 lainnya di sel. Selanjutnya, saya harus menambahkan kode 3 digit "upc" sebelum digit numerik pertama dalam sel. Ini akan menjadi kebutuhan yang berkelanjutan. Saya harus melatih beberapa orang untuk menggunakan sistem Excel.
Sepertinya Jean sudah mempertimbangkan untuk menggunakan Edit-Replace untuk menyingkirkan tiga nol. Ini tidak akan berhasil karena melakukan edit, ganti pada "000004560007" akan menyebabkan kedua kemunculan 000 menghilang.
Pertama, saya ingin menyarankan agar Jean memasukkan kolom sementara di sebelah kode harga saat ini, menulis rumus untuk melakukan transformasi, lalu gunakan Edit-Copy, Edit-PasteSpecial-Values untuk menyalin rumus kembali ke kode harga asli.
Fungsi REPLACE () yang didokumentasikan dengan buruk akan melakukan trik dalam kasus ini. Saya kecewa dengan implementasi REPLACE (). Saya akan berpikir itu akan meminta teks tertentu untuk diganti, tetapi sebaliknya meminta pengguna untuk mencari tahu posisi karakter yang akan diganti. REPLACE akan mengganti bagian dari string teks dengan string baru. Empat argumen yang Anda berikan ke fungsi tersebut adalah sel yang berisi teks lama, posisi karakter dalam teks lama yang ingin Anda ganti, jumlah karakter yang akan diganti, dan teks baru yang akan digunakan.
Asumsi saya yang menyederhanakan adalah bahwa tiga nol mewakili permulaan kode UPC dalam string. Dengan demikian, tidak perlu mencari karakter numerik pertama dalam sel. Setelah rumus menemukan tiga nol, rumus tersebut dapat menggantikan ketiga nol tersebut dengan string "upc".
Untuk menemukan lokasi kemunculan pertama "000" dalam teks, Anda akan menggunakan rumus ini:
=FIND(A2,"000")
Rumus yang perlu dimasukkan oleh Jean di sel B2 adalah:
=REPLACE(A2,FIND("000",A2),3,"upc")
Pikiran saya adalah meminta analis harga menyoroti berbagai sel dan kemudian menggunakan makro ini. Makro akan menggunakan loop dengan "Untuk setiap sel dalam Pilihan" di awal. Dengan perulangan ini, "sel" menjadi variabel rentang khusus. Anda dapat menggunakan cell.address atau cell.value atau cell.row untuk menemukan alamat, nilai atau baris dari sel saat ini dalam loop. Berikut makro:
Public Sub Jean() ' This macro will replace the first occurrence of "000" ' in each selected cell with the string "upc" ' copyright 1999 www.MrExcel.com For Each cell In Selection cell.Value = Replace(cell.Value, "000", "upc", 1, 1, vbTextCompare) Next cell End SubPerhatikan bahwa ini adalah makro tipe destruktif. Saat pengguna menyorot serangkaian sel dan menjalankan makro, sel tersebut akan diubah. Tidak perlu dikatakan lagi bahwa Anda ingin menguji makro Anda secara menyeluruh pada data pengujian sebelum Anda melepaskannya pada data produksi nyata. Ketika Anda memiliki situasi seperti Jean di mana Anda akan terus-menerus diminta untuk mengubah kolom menggunakan rumus yang kompleks, menulis makro sederhana seperti yang diilustrasikan di sini dapat menjadi alat yang efektif dan menghemat waktu.
