gaptek excel detected

pertanyaan gw kali ini seputar ms office,
emang jarang berurusan dengan tabel2 excel selama ini.
palingan cuma isi kolom, ude ga diapa2in.

nah gw mau tanya...
ceritanya gw lagi dapat list daftar nama n numur togel orang tersebut.
karena sistem hanya bisa ngebaca numur togel gsm kudu diawali +62,
contoh : +628123456789 bukan 08123456789
nah gimana caranya supaya excel bisa otomatis ganti yang angka terdepan 0 jadi +62.

kalau ga salah inget
excel memfasilitasi programming *cmiiw
datanya ada 1300an orang, sebanyak 2 file.
kalau musti ganti satu-satu, habis dunk waktu gw cuma ngerjain kek gitu.. :kesal:

thxb4

coba dengan find and replace ga bisa ya etca?
saya ga pernah masuk ke ranah programming.
biasanya sekedar membuat skenario what if
dengan what if, bisa dibuat acak mana nomor buntut yang mau keluar malam ini :luck:

lah ini kan cell, findnya kan keseluruhan angka yang di dalam cell itu kan?

btw nemu ini:

Menambahkan “Angka 0” Sebelum Angka Lainnya pada Excel 2007

Seorang peserta training pernah mengajukan pertanyaan sederhana, yaitu bagaimana caranya untuk menambahkan angka 0 dibelakang angka lainnya pada Excel 2007 ? Misalnya angka 007 ? Karena jika kita menuliskan angka 007 tersebut maka secara otomatis Excel akan merubahnya menjadi angka 7 saja tanpa awalan angka 0. Rasanya semua pengguna Excel pasti tahu jawabannya, yaitu dengan menambahkan simbol ‘ saat menuliskan angka tersebut. Jadi untuk menuliskan angka 007 tersebut, pada sel di Excel kita tulisan ‘007 seperti tampak pada screenshot berikut ini.


http://mugi.or.id/cfs-file.ashx/__ke...0_0A30ECCF.png


Dan hasilnya Excel tetap menyertakan angka 0 dibelakang angka 7.
http://mugi.or.id/cfs-file.ashx/__ke...0_290760AD.png


Namun permasalahan yang dihadapi peserta training tersebut bukan itu, tapi bagaimana caranya menambahkan angka 0 tersebut pada angka yang sebelumnya sudah tertulis ?
Sebagai contoh, misalnya saja kita memiliki tabel yang berisikan beberapa angka seperti berikut ini.
http://mugi.or.id/cfs-file.ashx/__ke...0_0813EE06.png


Pada deretan angka tersebut kita ingin menambahkan angka 0 didepannya hingga hasil yang diharapkan adalah 001, 002, dst. Dengan menggunakan cara sebelumnya bisa saja, yaitu dengan menambahkan simbol ‘, namun itu artinya kita harus mengetikan ulang semua angka tersebut secara manual, jika jumlahnya sedikit tentunya tidak masalah, namun jika jumlahnya banyak jelas sangat tidak efisien. Sebagai solusinya kita dapat menggunakan fasilitas format sel. Caranya sebagai berikut ini.
Pada tabel tersebut, sorot / blok sel A1 hingga A5. Lalu pada sel yang telah disorot tersebut, klik-kanan mouse dan pada menu yang muncul klik pada Format Cells. Atau cara cepatnya gunakan tombol shortcut CTRL + 1.
http://mugi.or.id/cfs-file.ashx/__ke...0_67207B5E.png


Pada kotak dialog Format Cells yang muncul, pastikan tab Number aktif. Lalu pada daftar Category pilih Custom.
http://mugi.or.id/cfs-file.ashx/__ke...0_6C22B90D.png


Pada bagian Custom ini kita dapat mengatur format penulisan angka yang kita inginkan secara bebas. Secara default Excel juga telah menyediakan beberapa format penulisan angka yang umum digunakan. Namun untuk kasus peserta training diatas, maka kita harus menuliskan format sendiri.


Klik pada kolom isian dibawah Type: lalu ketikan “00”# seperti tampak pada screenshot berikut ini.
http://mugi.or.id/cfs-file.ashx/__ke...0_1569DB3F.png

“00” Adalah karakter yang ingin kita tambahkan didepan angka dan simbol # mewakili angka yang sebelumnya sudah tertulis pada tabel.


Klik tombol OK untuk menutup kotak dialog dan hasil akhir yang kita dapatkan dapat dilihat pada screenshot berikut ini.
http://mugi.or.id/cfs-file.ashx/__ke...0_468915DF.png

Bisakah kita menambahkan 4 buah angka 0 ? Jelas bisa, caranya tinggal masuk kembali kedalam kotak dialog Format Cells lalu ganti karakter yang diapit tanda kutip dengan 0000, jadi yang tertulis pada bagian Type: adalah “0000”#.

Bisakah kita menambahkan karakter lain selain angka 0 ? Pasti bisa juga. Pada bagian Type: tersebut, cobalah untuk mengetikan “ABC-”#. Dan hasilnya dapat dilihat pada screenhot berikut ini :-)
http://mugi.or.id/cfs-file.ashx/__ke...0_655F89BD.png


Demikian dan mudah-mudahan tulisan singkat ini ada gunanya.


sumber :

Quote:

---

Originally Posted by etca

lah ini kan cell, findnya kan keseluruhan angka yang di dalam cell itu kan?

---

replace dapat dilakukan sekaligus within sheet/workbook

misal: find 08 -- replace with +628
maka semua nomor yang semula berawal '08 otomatis akan terganti jadi '+628

pake fungsi/formula replace. contoh di cell A1 berisi data 08123456789, di cell B1 kita bikin formula yang isinya "=REPLACE(A1,1,1,"+62")" [hilangkan tanda kutip di awal dan di akhir]. cell B2 dst tinggal copas aja.

fungsi replace di atas mengganti karakter pertama di cell A1, dengan +62.

untuk menyeleksi penggantian misalnya yang diganti hanya yang karakter terdepannya ='0', bisa mengkombinasikannya dengan fungsi/formula IF dan LEFT.

@el sol.. masternya excel hadir
ternyata ada formula khusus untuk replace.
terima kasih buat tipsnya :minum2:

kaco neh.. benerbener dikerjain ama kerjaan tambahan neh..
kalau pake formula ternyata alatnya ga mau kerja,
dia bacanya text only :))

sekarang lagi find replace manual =))

manteb infonya mbah :mrgreen:

==========================

klo nampilin data dari MS Access ke Excel pake macro ada scriptnya mbah?

mumet nih

Quote:

---

Originally Posted by etca

kaco neh.. benerbener dikerjain ama kerjaan tambahan neh..
kalau pake formula ternyata alatnya ga mau kerja,
dia bacanya text only :))

sekarang lagi find replace manual =))

---

yang formula bisa kok dibikin text. copy cell2/range yang berisi formula, terus 'Paste values' ke cell tujuan. hasilnya nanti value yang berupa text.

Quote:

---

Originally Posted by gembel

manteb infonya mbah :mrgreen:

==========================

klo nampilin data dari MS Access ke Excel pake macro ada scriptnya mbah?

mumet nih

---

macronya access atau macronya excel ?

kalau saya lebih milih ke bahasa pemrograman tertentu, kayak delphi atau vb atau yang lain yang sudah ada wrapper untuk MS Office + database-nya entah berupa component atau yang lain, jadi lebih gampang. untuk interaksi dengan MS Office bisa leluasa karena sudah berupa automation/library COM. yang automation Access saya belum pernah coba, kalo Excel pernah beberapa kali bikin report yang sumbernya dari database, tapi bukan Access, dan nggak berupa macro.

berikut contoh penggalan code untuk menampilkan data di excel yang sumbernya dari database, di delphi :

komponen yang digunakan :
Dataset, bisa berupa Table atau Query atau yang lain sesuai kebutuhan, dan komponen lain terkait database.
ExcelApp
ExcelWorksheet
ExcelWorkbook

nama variabel property :
Dataset : sqlData
ExcelApp : ExcelApp
ExcelWorkbook : ExcelWB
ExcelWorksheet : ExcelWS

pra kondisi :
Populate/isi terlebih dahulu Dataset yang digunakan.

garis besar codenya kira2 kayak gini :

Spoiler for code:

Code:

---

procedure TfrmTest.exportToExcel();
var
  row:integer;
begin
  row:=1;
  //membuat+mengkoneksikan dokumen,workbook,worksheet
  ExcelApp.Connect;
  ExcelApp.Visible[0]:=False;
  ExcelApp.Workbooks.Add(1,0);
  ExcelWB.ConnectTo(ExcelApp.ActiveWorkbook);
  ExcelWS.ConnectTo(ExcelWb.ActiveSheet as _Worksheet);
 
  //tampilkan ke excel
  sqlData.first;
  while not(sqlData.Eof) do
  begin
    with ExcelWS,sqlData do
    begin
      Range['A'+inttostr(row),'A'+inttostr(row)].Value:=fields[0].asstring; //field pertama di kolom A
      Range['B'+inttostr(row),'B'+inttostr(row)].Value:=fields[1].asstring; //field kedua di kolom B, dst
      //dst..
      inc(row);
      next;
    end;
  end;
  ExcelApp.Visible[0]:=True;
end;

---

Kalau kesulitan menentukan mana method2 pada komponen yang terkait dengan excel, biasanya saya melakukan langkah berikut :
1. test dengan membuat/merekam macro di excel.
2. menjalankan langkah2 yang diperlukan, misal membuat grafik,dll.
3. kalau sudah selesai nanti bisa dilihat hasilnya.
4. baru kemudian dibersihkan, mana bagian yang perlu, mana yang nggak, soalnya ketika perekaman macro sering kali ada aktivitas yang nggak perlu juga dioutputkan kodenya, ini yang perlu dibersihkan.
5. yang terakhir tinggal konversi ke bahasa pemrograman yang kita pake.

dokumentasi/help-nya excel biasanya cukup membantu.

tolong koreksi kalo salah.

contoh :
kolom A no telp (0878123456
kolom B kasih XXX
kolom C (formula gabungin B dan A )=B&A hasilnya XXX0878123456

replace XXX0 dengan +62

done

Quote:

---

macronya access atau macronya excel ?

---

macro excel mbah

ini ud kelar belum ?
yg punya PR ke mana ?

Quote:

---

Originally Posted by GiKu

ini ud kelar belum ?
yg punya PR ke mana ?

---

entahlah, klo masalah saya blum jelas nih mbah

Quote:

---

Originally Posted by gembel

entahlah, klo masalah saya blum jelas nih mbah

---

masalah yg kemaren ya
kolom isian jenis kelamin di formulir sunat masal ?

Quote:

---

Originally Posted by opera

contoh :
kolom A no telp (0878123456
kolom B kasih XXX
kolom C (formula gabungin B dan A )=B&A hasilnya XXX0878123456

replace XXX0 dengan +62

done

---

Quote:

---

Originally Posted by el sol

yang formula bisa kok dibikin text. copy cell2/range yang berisi formula, terus 'Paste values' ke cell tujuan. hasilnya nanti value yang berupa text.

---

@all,
tengkyu tengkyu.. udah rebes dari kemarin kok datanya nyang di excel,
tadi dah trial di alatnya, dan udah bekerja dengan baik, file excelnya bisa keambil datanya.

besok tinggal demo di depan nyonyah besar.. ::elaugh::

Quote:

---

Originally Posted by GiKu

masalah yg kemaren ya
kolom isian jenis kelamin di formulir sunat masal ?

---

klo nampilin data dari MS Access ke Excel pake macro ada scriptnya mbah?


:kacamata:

excel versi berapa ?

kalo 2007 ke atas, boleh ke menu DATA
lihat bagian GET EXTERNAL DATA
pilih FROM OTHER SOURCES

silakan dimulai dari sana

atau pilih menu DATA
lihat di bagian CONNECTIONS
pilih CONNECTIONS

excel 2003 mbah, jadi intinya:

* di excel dapat memanggil data yang terdapat di access 2003.
* ketika di excel ada perubahan(tambah/edit) otomatis terseimpan di access.

temanya ada backend DB dan interface

youli mau jadikan excel sebagai user interfacenya

kenapa gak bikin langsung di MS ACCESS-nya ?



btw, di 2003 apa ud ada menu bertema koneksi ke sumber2 di luar excel ?

Quote:

---

Originally Posted by GiKu

temanya ada backend DB dan interface

youli mau jadikan excel sebagai user interfacenya

---

excel sebagai UInya mbah. Saat ini jika menggunakan excel, data tersebut mencapai 30 MB yang berat sekali ketika dibuka. Oleh karena itu mo nyoba menggunakan database Access.

Quote:

---

kenapa gak bikin langsung di MS ACCESS-nya ?

---

terlalu banyak perhitungan mbah, dan usernya belum tentu ngerti

Quote:

---

btw, di 2003 apa ud ada menu bertema koneksi ke sumber2 di luar excel ?

---

saya ga paham mbah

yg mau disimpan ke DB hanya data atau termasuk kalkulasi-nya ?

btw, ini kan lapaknya etca - gpp nih ?

Quote:

---

Originally Posted by gembel

terlalu banyak perhitungan mbah, dan usernya belum tentu ngerti

---

kalo user nggak ngerti kan nanti bisa diajarin sebentar, atau bikin dokumentasi/manual instruction.
saran saya sih aplikasi/UI-nya jangan di excel bakalan repot nanti.
terus ke depannya ada kemungkinan aplikasinya dipake di banyak komputer apa cuman satu aja ?

Quote:

---

Originally Posted by GiKu

yg mau disimpan ke DB hanya data atau termasuk kalkulasi-nya ?

btw, ini kan lapaknya etca - gpp nih ?

---

gpp kan samasama seputaran excel, ;)

btw.. excelaaaaamaaattt pagi temanteman semua ::elaugh::

Quote:

---

Originally Posted by el sol

kalo user nggak ngerti kan nanti bisa diajarin sebentar, atau bikin dokumentasi/manual instruction.
saran saya sih aplikasi/UI-nya jangan di excel bakalan repot nanti.
terus ke depannya ada kemungkinan aplikasinya dipake di banyak komputer apa cuman satu aja ?

---

tuh mbel
penting juga dicari tau, itu data mau dipake oleh single user apa multiuser

kalo di access ada yg namanya semacam "file locking", kalo file datanya lagi dipake (misal oleh oleh si excel yg jadi UI-nya), yg lain gak bisa ikutan make

aduh benerbener mules deh garagara excel ::elaugh::
gw dapat data dari HP per groupnya, trus diminta menjadi database di mysql
sementara s/w bantu + operasionalnya inputnya dari format excel =)).

ok nanya lagi,
di EXCEL ada fitur otomatis jadi huruf besar ga? yang kek di ms word itu.
mengubah data excel baris jadi kolom gimana?

misal :
Last name: Efendi
General mobile: +62812xxxx

Last name: HUSIN
General mobile: +62812xxxx

Last name: IRAWATI
General mobile: 0219310xxx

Last name: Mardi
General mobile: 0219553xxx

Last name: MARDIANA
General mobile: 0812917xxx

nah data di atas mau gw ubah di excel dengan format :
| NAMA | NO.HP|
--------------------
| Efendi | +62812xxxx
| HUSIN | +62812xxxx
dst...

bisa ga?

Spoiler for curcol:

daku kalau dah kek gini merasa ga profesional banget,
dapat job dari petinggi kerajaan (owner tempat kerja) di luar jobdesk.
apa daya =)),

=UPPER(nomor field)

contow

isi di A1 --> Efendi

rumus di A2 --> =UPPER(A1)

Quote:

---

Originally Posted by GiKu

=UPPER(nomor field)

contow

isi di A1 --> Efendi

rumus di A2 --> =UPPER(A1)

---

ok thx,
nee gw dah pindah gedung, *info ga penting.


trus kalau delete dua baris secara otomatis bisa ga?
soalnya tiap data sebaris loncat dua baris.

kenapa gak minta it nya aja proses tuh data mysql export ke excel lsg ke format yang diinginkan

bukan dari mysql
jadi ceritanya begini :
1. dari hengpong kan ada group2 tuw hape import ke csv dan txt
2. dari file csv/txt impor ke excel
3. dari excel - langsung di running di programnya, otomatis masuk ke mysql.

IT kagak dilibatkan di project ini *sigh,
makanya gw ga enak kalau melibatkan dia, secara ini datanya dari big bos.
data pribadi suatu komunitasnya si big bos.
kecuali kalau big bosnya yang nyuruh langsung ITnya,
*itu beda perkara :)

ooo salah baca :D kirain ambil dari mysql ke excel...
klo baris jadi kolom di paste special pilih tranpose (bener ga ini yang dimaksud)

ya udah pake cara manual aja

datanya dibersihin dulu. untuk string "Last name: " direplace dengan string kosong, dan "General mobile: " direplace dengan string "'" (tanda petik tunggal, untuk menjadikannya sebagai text, bukan angka).

aktifkan menu "Developer". bikin macro, caranya pilih tombol "Macro", isi nama macronya, misalnya : Macro1. isi macronya kira2 seperti ini :

Code:

---

Sub Macro1()
    Dim i, j As Integer
    Dim CellInit
   
    Set CellInit = Selection.Cells(1, 1)
       
    For Each c In Selection
        'nama
        If c.Row Mod 3 = 1 Then
            CellInit.Offset(i, 1).Value = c.Value
            i = i + 1
        'telepon
        ElseIf c.Row Mod 3 = 2 Then
            CellInit.Offset(j, 2).Value = "'" & c.Value
            j = j + 1
        End If
    Next c
End Sub

---

mod 3 dicode di atas mengasumsikan ada baris kosong antar nomor HP dengan nama.
dari contoh di atas, ada baris kosong antara 0219310xxx dengan Mardi :

Code:

---

Last name: IRAWATI
General mobile: 0219310xxx

Last name: Mardi
General mobile: 0219553xxx

---

kalau nggak ada baris kosong, tinggal diganti, untuk yang mod 3 =1 diganti jadi mod 2 =1 dan untuk mod 3=2 di ganti jadi mod 2=0.

terus buat button. assign macro untuk button itu dengan macro yang baru dibuat tadi. caranya dengan klik kanan di button, pilih assign macro, pilih macro yang tadi dibuat.

cara menjalankan :
- pilih/seleksi dulu range datanya, misal range datanya ada di A1:A200
- klik button yang baru dibuat tadi

mudah2an berhasil