Saat menggunakan alat yang tersedia dari pita untuk mengekspor lembar kerja, sebagai file CSV, Arkadiusz mencatat bahwa dia dapat menentukan bahwa dia ingin menggunakan titik koma (;) sebagai pembatas bidang. Namun, jika dia menyimpan file CSV menggunakan makro (FileFormat. =xlCSV atau xlCSVWindows), maka dia tidak dapat menentukan titik koma sebagai pembatas
Ini bekerja dengan cara ini dengan desain di VBA. Implementasi Excel dari rutin ekspor untuk VBA selalu menggunakan apa pun pengaturan regional Windows untuk menentukan bagaimana item dalam CSV harus dipisahkan. Khususnya, rutin melihat bidang Pemisah Daftar untuk pembatas. Ini berarti Anda dapat, jika diinginkan, mengubah pembatas menjadi titik koma dengan mengubah pengaturan Pemisah Daftar di konfigurasi pengaturan regional Anda
Jika Anda tidak ingin mengubah pengaturan regional, Anda dapat menulis makro Anda sendiri yang akan menampilkan file dengan cara apa pun yang Anda inginkan. Pertimbangkan, sejenak, makro berikut, yang akan menampilkan file
Sub CreateFile() Dim sFName As String Dim Rows As Long Dim Cols As Long Dim J As Long Dim K As Long Dim sTemp As String Dim sSep As String sSep = ";" 'Specify the separator to be used sFName = ActiveWorkbook.FullName If Right(sFName, 5) = ".xlsx" Then sFName = Mid(sFName, 1, Len(sFName) - 5) sFName = sFName & ".txt" Open sFName For Output As 1 With ActiveSheet 'Number of rows to export is based on the contents 'of column B. If it should be based on a different 'column, change the following line to reflect the 'column desired. Rows = .Cells(.Rows.Count, "B").End(xlUp).Row For J = 1 To Rows sTemp = "" Cols = .Cells(J, .Columns.Count).End(xlToLeft).Column For K = 2 To Cols sTemp = sTemp & .Cells(J, K).Value If K < Cols Then sTemp = sTemp & sSep Next Print #1, sTemp Next J End With Close 1 sTemp = "There were " & Rows & " rows of data written " sTemp = sTemp & "to this file:" & vbCrLf & sFName Else sTemp = "This macro needs to be run on a workbook " sTemp = sTemp & "stored in the XLSX format." End If MsgBox sTemp End SubMakro ini membuka file teks yang memiliki nama yang sama dengan buku kerja Anda. Kemudian langkah melalui setiap baris dan mulai menyusun string isi sel. (Ini dimasukkan ke dalam variabel sTemp. ) Setiap sel memiliki titik koma yang ditempatkan di antara sel tersebut, seperti yang ditentukan oleh variabel sSep. Nilai gabungan setiap baris disimpan dalam file teks, dan setelah selesai file teks ditutup. Rutinnya sangat cepat, dan setelah selesai akan menampilkan pesan yang menunjukkan berapa banyak baris yang diekspor ke file
Fungsi VBA Split yang digunakan adalah untuk membagi string teks menjadi array. Teks dibagi berdasarkan pembatas yang diberikan – e. g. koma, spasi, titik dua dll
Misalnya, bayangkan kita memiliki string berikut
"Apel. Oranye. Pir. Prem"
Anda dapat melihat bahwa setiap item dipisahkan oleh tanda titik dua. Kami menyebut tanda titik dua sebagai pembatas
Kita dapat membagi string ini menjadi sebuah array
' //excelmacromastery.com/ Sub SplitBasic() Dim arr() As String arr = Split("John:Jane:Paul:Sophie", ":") End Sub _
Setelah berada dalam array, mudah untuk mengakses setiap item
Glosarium
Array – struktur untuk menyimpan sekelompok variabel serupa
– fungsi ini memberikan posisi terakhir dari sebuah array
– fungsi ini memberikan posisi pertama dari sebuah array. Untuk larik, yang dikembalikan oleh fungsi Split, posisi pertama adalah nol
– fungsi ini digunakan untuk mencari string di dalam string dan mengembalikan posisinya
– sama seperti Instr tetapi mencari string dari akhir
Pisahkan Sintaks
Pisahkan ekspresi, pembatas[opsional], batas[opsional], bandingkan[opsional]
Nilai Pengembalian Terpisah
Fungsi Split mengembalikan array
Membagi Parameter Fungsi
ekspresi – ini adalah string teks yang akan dipisahkan
pembatas [opsional] – pembatas karakter yang digunakan dalam string untuk memisahkan setiap item. Jika Anda tidak menggunakan parameter ini maka VBA menggunakan spasi sebagai pembatas
limit [opsional] – ini memungkinkan kita menyetel jumlah item dalam hasil. Jika kita menggunakan 1 sebagai batas maka tidak ada pemisahan yang terjadi dan string penuh dikembalikan
bandingkan [opsional] – jika kita menggunakan huruf sebagai pembatas maka ini menentukan apakah kita mempertimbangkan kasus huruf
VBA Split – Contoh Sederhana
Kode berikut menunjukkan contoh penggunaan fungsi Split dan mencetak hasilnya ke Jendela Segera
' //excelmacromastery.com/ Sub VBA_Split_Print() Dim arr() As String ' Split the string to an array arr = Split("John:Jane:Paul:Sophie", ":") ' Print each item in the array to the Immediate Window(Ctrl + G) Dim name As Variant For Each name In arr Debug.Print name Next End SubKeluaran
Yohanes
Jane
Paulus
Sophie
Saat kami membagi string menjadi array, kami memiliki item di setiap posisi dalam array. Ini berarti kita dapat dengan mudah mengakses item apapun menggunakan posisi array
' //excelmacromastery.com/ Sub VBA_Split_PrintArr() Dim arr() As String ' Split the string to an array arr = Split("John:Jane:Paul:Sophie", ":") Debug.Print arr(0) ' Print John Debug.Print arr(1) ' Print Jane Debug.Print arr(2) ' Print Paul Debug.Print arr(3) ' Print Sophie End Sub
Split mengembalikan array yang dimulai dari posisi nol. Jika kita ingin menggunakan pernyataan For untuk membaca array kita dapat menggunakan LBound dan UBound untuk memberi kita posisi pertama dan terakhir dari array
' //excelmacromastery.com/ Sub VBA_Split_Print() Dim arr() As String ' Split the string to an array arr = Split("John:Jane:Paul:Sophie", ":") ' Print each item in the array to the Immediate Window(Ctrl + G) Dim i As Long For i = LBound(arr) To UBound(arr) Debug.Print arr(i) Next End Sub _
Parameter Batas Terpisah
Parameter Limit digunakan untuk menentukan bagaimana item ditempatkan dalam array. Dengan kata lain, berapa banyak item yang dipecah menjadi string asli
Tabel di bawah menunjukkan hasil penggunaan batas yang berbeda dari string sampel ini
StringLimitResult"John. Jane. Paulus. Sophie"1 John. Jane. Paulus. Sophie "John. Jane. Paulus. Sophie"2 JohnJane. Paulus. Sophie "John. Jane. Paulus. Sophie"3 John
Jane
Paulus. Sophie
"Yohanes. Jane. Paulus. Sophie"4 John
Jane
Paulus
Sophie
Anda dapat mencoba sendiri kodenya
' //excelmacromastery.com/ Sub VBA_Split_Limit() Dim arr() As String ' Split the string to an array arr = Split("John:Jane:Paul:Sophie", ":", 1) ' Print each item in the array to the Immediate Window(Ctrl + G) Dim name As Variant For Each name In arr Debug.Print name Next End Sub
Pisahkan Bandingkan Parameter
Parameter Bandingkan digunakan untuk pembatas yang terdiri dari satu atau huruf
Misalnya, bayangkan kita ingin menggunakan huruf x sebagai pembatas pada string berikut
“12x34X45x”
- Jika kita ingin membagi dengan x hanya dengan huruf kecil – maka kita menggunakan vbBinaryCompare
- Jika kita ingin memisahkan dengan huruf besar atau kecil x – maka kita menggunakan vbTextCompare
- vbUseCompareOption adalah default dan memberi tahu split untuk menggunakan modul Bandingkan pengaturan. Baca lebih lanjut tentang pengaturan perbandingan modul
Kode berikut menunjukkan bagaimana kita menggunakan parameter Bandingkan
' //excelmacromastery.com/ Sub VBA_Split_Compare() Dim arr() As String ' Split the string to an array - not case sensitive arr = Split("12x34X45", "x", , vbTextCompare) ' Print each item in the array to the Immediate Window(Ctrl + G) Dim name As Variant For Each name In arr Debug.Print name Next End Sub _
Tabel berikut menunjukkan hasil dari berbagai argumen Bandingkan
StringDelimiterCompare TypeResult"12x34X45"xvbBandingkanTeks1234
45"12x34X45"xvbBandingkanBiner12
34X45
Membalikkan Pemisahan
Kita dapat menggunakan fungsi Join untuk melakukan kebalikan dari fungsi split. Join mengonversi array menjadi string dan menambahkan pembatas yang diberikan
Ini bisa berguna karena terkadang kita mungkin ingin memisahkan sebuah string, memperbarui satu atau lebih nilai dalam array dan kemudian mengonversi array menjadi string
Contoh ini menunjukkan cara menggunakan Gabung
' //excelmacromastery.com/ Sub UsingJoin() Dim arr As Variant ' create an array using the array function arr = Array("Apple", "Orange", "Pear") Dim s As String ' Covert the array to a string using the colon delimiter s = Join(arr, ":") ' Print the string to the Immediate Window(Ctrl + G) Debug.Print s End Sub
Lihat bagian “Contoh Pemisahan – Menggunakan Penggabungan” untuk contoh penggunaan fungsi Penggabungan dengan fungsi Pemisahan
Contoh Terpisah – Nama
Contoh Split yang sangat bagus adalah ketika berhadapan dengan nama
Bayangkan kita memiliki nama "John Henry Smith" dan kita ingin mengekstrak setiap nama
Kita bisa menggunakan Left dan Instr untuk mendapatkan nama depan
' //excelmacromastery.com/ Sub Instr_Firstname() Dim s As String s = "John Henry Smith" ' Get the position of the first space Dim position As Long position = InStr(s, " ") - 1 ' Prints John Debug.Print Left(s, position) End Sub _Untuk mendapatkan nama belakang sedikit lebih rumit
' //excelmacromastery.com/ Sub Instr_Lastname() Dim s As String s = "John Henry Smith" ' Get the position of the last space Dim position As Long, length As Long position = InStrRev(s, " ") - 1 length = Len(s) ' Prints Smith Debug.Print Right(s, length - position) End Sub _
Mendapatkan nama yang tidak berada di posisi pertama atau terakhir memang sangat berantakan. Namun, dengan menggunakan Split, kami dapat menyederhanakan seluruh proses
' //excelmacromastery.com/ Sub SplitName() Dim s As String: s = "John Henry Smith" Dim arr() As String arr = Split(s, " ") Debug.Print arr(0) ' John Debug.Print arr(1) ' Henry Debug.Print arr(2) ' Smith End Sub
Kita sebenarnya tidak perlu menggunakan array seperti yang bisa kita lihat pada contoh berikut. Tidaklah efisien untuk memanggil fungsi Split 3 kali, bukan 1, tetapi terlihat bagus dalam contoh ini
' //excelmacromastery.com/ Sub VBA_Split_Print() Dim arr() As String ' Split the string to an array arr = Split("John:Jane:Paul:Sophie", ":") ' Print each item in the array to the Immediate Window(Ctrl + G) Dim name As Variant For Each name In arr Debug.Print name Next End Sub 0
Contoh Terpisah – Nama file
Pada contoh berikutnya kami menggunakan Split untuk mendapatkan bagian ekstensi dari nama file
' //excelmacromastery.com/ Sub VBA_Split_Print() Dim arr() As String ' Split the string to an array arr = Split("John:Jane:Paul:Sophie", ":") ' Print each item in the array to the Immediate Window(Ctrl + G) Dim name As Variant For Each name In arr Debug.Print name Next End Sub 1
Ini salah satu yang menarik yang bisa Anda coba sendiri. Diberi nama file lengkap, coba tulis kode untuk mengekstrak nama file tanpa ekstensi atau folder
Misalnya untuk “C. \MyDocs\Jan\MyResume. Doc” kami ingin mengekstrak MyResume
' //excelmacromastery.com/ Sub VBA_Split_Print() Dim arr() As String ' Split the string to an array arr = Split("John:Jane:Paul:Sophie", ":") ' Print each item in the array to the Immediate Window(Ctrl + G) Dim name As Variant For Each name In arr Debug.Print name Next End Sub 2Keluaran
Resume Saya
lagu cinta
savebattle
Contoh Terpisah – Salin ke Rentang
Karena hasil dari Split adalah sebuah array, kita dengan mudah menyalinnya ke sebuah range
' //excelmacromastery.com/ Sub VBA_Split_Print() Dim arr() As String ' Split the string to an array arr = Split("John:Jane:Paul:Sophie", ":") ' Print each item in the array to the Immediate Window(Ctrl + G) Dim name As Variant For Each name In arr Debug.Print name Next End Sub _3
Contoh Terpisah – Hitung Item
Jika kami ingin menghitung jumlah item dalam string yang dibatasi, kami dapat menggunakan Split untuk melakukannya untuk kami
Kami cukup membagi string dan kemudian menggunakan fungsi Ubound untuk mengembalikan jumlah item. Kami sudah melihat bahwa UBound digunakan untuk mengembalikan posisi terakhir dalam array. Karena, array mulai dari nol, kita perlu menambahkan satu untuk mendapatkan jumlah item
' //excelmacromastery.com/ Sub VBA_Split_Print() Dim arr() As String ' Split the string to an array arr = Split("John:Jane:Paul:Sophie", ":") ' Print each item in the array to the Immediate Window(Ctrl + G) Dim name As Variant For Each name In arr Debug.Print name Next End Sub _4
Contoh Terpisah – Menggunakan Gabung
Ini adalah salah satu yang menarik yang mungkin ingin Anda coba sendiri. Ambil tiga string di bawah ini
123. 3456. 96. 345
1234. 1156. 7. 345
1273. 9998. 123. 345
Kami ingin menambahkan satu ke angka ketiga di setiap string. Misalnya, string pertama harus menjadi 123. 3456. 97. 345
Sebelum Anda mencoba sendiri, saya akan memberikan satu petunjuk. Anda dapat menggunakan fungsi Gabung untuk membalikkan operasi Pemisahan. Dibutuhkan array dan pembatas dan membuat string
Anda bisa mulai dengan kode ini
' //excelmacromastery.com/ Sub VBA_Split_Print() Dim arr() As String ' Split the string to an array arr = Split("John:Jane:Paul:Sophie", ":") ' Print each item in the array to the Immediate Window(Ctrl + G) Dim name As Variant For Each name In arr Debug.Print name Next End Sub 5
Ini adalah cara untuk melakukannya
' //excelmacromastery.com/ Sub VBA_Split_Print() Dim arr() As String ' Split the string to an array arr = Split("John:Jane:Paul:Sophie", ":") ' Print each item in the array to the Immediate Window(Ctrl + G) Dim name As Variant For Each name In arr Debug.Print name Next End Sub 6Keluaran
123. 3456. 100. 345
1234. 1156. 8. 345
1273. 9998. 124. 345
Bacaan lebih lanjut
Panduan Utama untuk Fungsi String VBA
Mengekstrak menggunakan fungsi Split
Array VBA
VBA Untuk Putaran
Microsoft Docs – Fungsi terpisah
Jika Anda ingin melihat beberapa contoh dunia nyata menggunakan Split, Anda akan menemukannya di pos Cara Mudah Mengekstrak Dari Setiap String Tanpa Menggunakan VBA InStr
Apa berikutnya?
Tutorial VBA Gratis Jika Anda baru mengenal VBA atau ingin mempertajam keterampilan VBA yang ada, mengapa tidak mencoba Tutorial VBA Utama
Pelatihan Terkait. Dapatkan akses penuh ke webinar pelatihan Excel VBA dan semua tutorialnya
(CATATAN. Berencana membangun atau mengelola Aplikasi VBA? . )