Loncat ke daftar isi utama

 Bagaimana cara mengubah string teks menjadi case yang tepat dengan pengecualian di Excel?

Pengarang: Xiaoyang Terakhir Dimodifikasi: 2017-08-23

Di Excel, Anda dapat menerapkan fungsi Proper untuk mengonversi string teks ke huruf besar dengan mudah, tetapi, terkadang, Anda perlu mengecualikan beberapa kata tertentu saat mengonversi string teks ke kasus yang tepat seperti gambar berikut yang ditampilkan. Artikel ini, saya akan berbicara tentang beberapa trik cepat untuk menyelesaikan pekerjaan ini di Excel.

Ubah string teks menjadi huruf yang tepat dengan pengecualian menggunakan rumus

Ubah string teks menjadi kapitalisasi huruf besar dengan pengecualian menggunakan kode VBA


Ubah string teks menjadi huruf yang tepat dengan pengecualian menggunakan rumus

Semoga rumus berikut dapat membantu Anda untuk menangani tugas ini dengan cepat, lakukan seperti ini:

Masukkan rumus ini:

= UPPER (LEFT (A2)) & MID (TRIM (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE) (SUBSTITUTE (SUBSTITUTE ("" & PROPER (A2) & "", "Of", "of"), "A", "a"), "Is "," adalah ")," Usa "," USA ")), 2, LEN (A2)) ke dalam sel di mana Anda ingin mendapatkan hasilnya, lalu seret gagang isian untuk mengisi rumus ini, dan string teks telah diubah huruf besar / kecilnya sesuai tetapi pengecualian tertentu, lihat tangkapan layar:

Note: Dalam rumus di atas, A2 adalah sel yang ingin Anda ubah, “Of”, “A”, “Is”, “Usa” adalah kata-kata kasus normal yang tepat setelah mengonversi, "Dari", "a", "adalah", "AS" adalah kata-kata yang ingin Anda kecualikan dari kasus yang tepat. Anda dapat mengubahnya sesuai kebutuhan atau menambahkan kata lain dengan fungsi SUBSTITUTE.


Ubah string teks menjadi kapitalisasi huruf besar dengan pengecualian menggunakan kode VBA

Jika rumus di atas agak sulit dipahami dan diubah sesuai kebutuhan Anda, di sini, Anda juga dapat menerapkan kode VBA untuk menyelesaikan tugas ini. Silakan lakukan dengan langkah-langkah berikut satu per satu.

1. Tahan ALT + F11 kunci untuk membuka Microsoft Visual Basic untuk Aplikasi jendela.

2. Klik Menyisipkan > Modul, dan tempel kode berikut di Jendela Modul.

Kode VBA: ubah string teks ke kasus yang tepat dengan pengecualian:

Sub CellsValueChange()
'Updateby Extendoffice
    Dim xSRg As Range
    Dim xDRg As Range
    Dim xPRg As Range
    Dim xSRgArea As Range
    Dim xRgVal As String
    Dim xAddress As String
    Dim I As Long
    Dim K As Long
    Dim KK As Long
    On Error Resume Next
    xAddress = Application.ActiveWindow.RangeSelection.Address
    Set xSRg = Application.InputBox("Original cells:", "KuTools For Excel", xAddress, , , , , 8)
    If xSRg Is Nothing Then Exit Sub
    Set xDRg = Application.InputBox("Output cells:", "KuTools For Excel", , , , , , 8)
    If xDRg Is Nothing Then Exit Sub
    Set xPRg = Application.InputBox("Cells to exclude:", "KuTools For Excel", , , , , , 8)
    If xPRg Is Nothing Then Exit Sub
    Set xDRg = xDRg(1)
    For I = 1 To xSRg.Areas.Count
        Set xSRgArea = xSRg.Areas.Item(I)
        For K = 1 To xSRgArea.Count
            xRgVal = xSRgArea(K).Value
            If Not IsNumeric(xRgVal) Then
                xRgVal = CorrectCase(xRgVal, xPRg)
                xDRg.Offset(KK).Value = xRgVal
            End If
            KK = KK + 1
        Next
    Next
End Sub
Function CorrectCase(ByVal xRgVal As String, ByVal xPRg As Range) As String
    Dim xArrWords As Variant
    Dim I As Integer
    Dim xPointer As Integer
    Dim xVal As String
    xPointer = 1
    xVal = xRgVal
    xArrWords = WordsOf(xRgVal)
    For I = 0 To UBound(xArrWords)
        xPointer = InStr(xPointer, " " & xVal, " " & xArrWords(I))
        Debug.Print xPointer
        Mid(xVal, xPointer) = CorrectCaseOneWord(CStr(xArrWords(I)), xPRg)
    Next I
    CorrectCase = xVal
End Function
Function WordsOf(xRgVal As String) As Variant
    Dim xDelimiters As Variant
    Dim xArrRtn As Variant
    xDelimiters = Array(",", ".", ";", ":", Chr(34), vbCr, vbLf)
    For Each xEachDelimiter In xDelimiters
        xRgVal = Application.WorksheetFunction.Substitute(xRgVal, xEachDelimiter, " ")
    Next xEachDelimiter
    xArrRtn = Split(Trim(xRgVal), " ")
    WordsOf = xArrRtn
End Function
Function CorrectCaseOneWord(xArrWord As String, xERg As Range) As String
    With xERg
        If IsError(Application.Match(xArrWord, .Cells, 0)) Then
            CorrectCaseOneWord = Application.Proper(xArrWord)
        Else
            CorrectCaseOneWord = Application.VLookup(xArrWord, .Cells, 1, 0)
        End If
    End With
End Function

3. Lalu tekan F5 kunci untuk menjalankan kode ini, dan kotak prompt muncul untuk mengingatkan Anda untuk memilih sel asli yang ingin Anda ubah, lihat tangkapan layar:

4. Dan kemudian klik OK, pilih sel tempat Anda ingin menampilkan hasil di kotak yang muncul, lihat tangkapan layar:

5. Pergi di klik OK, dan di kotak dialog popup, pilih teks yang ingin Anda kecualikan, lihat tangkapan layar:

6. Dan kemudian klik OK untuk keluar dari dialog, dan semua string teks telah diubah ke kasus yang tepat tetapi mengecualikan kata-kata yang ditentukan, lihat tangkapan layar:

Alat Produktivitas Kantor Terbaik

🤖 Kutools AI Ajudan: Merevolusi analisis data berdasarkan: Eksekusi Cerdas   |  Hasilkan Kode  |  Buat Rumus Khusus  |  Analisis Data dan Hasilkan Grafik  |  Aktifkan Fungsi Kutools...
Fitur Populer: Temukan, Sorot, atau Identifikasi Duplikat   |  Hapus Baris Kosong   |  Gabungkan Kolom atau Sel tanpa Kehilangan Data   |   Putaran tanpa Formula ...
Pencarian Super: VLookup Beberapa Kriteria    VLookup Nilai Berganda  |   VLookup di Beberapa Lembar   |   Pencarian Fuzzy ....
Daftar Drop-down Lanjutan: Buat Daftar Drop Down dengan Cepat   |  Daftar Drop Down yang Bergantung   |  Multi-pilih Drop Down List ....
Manajer Kolom: Tambahkan Jumlah Kolom Tertentu  |  Pindahkan Kolom  |  Alihkan Status Visibilitas Kolom Tersembunyi  |  Bandingkan Rentang & Kolom ...
Fitur Unggulan: Fokus Kisi   |  Tampilan Desain   |   Bar Formula Besar    Manajer Buku Kerja & Lembar   |  Perpustakaan Sumberdaya (Teks otomatis)   |  Pemetik tanggal   |  Gabungkan Lembar Kerja   |  Enkripsi/Dekripsi Sel    Kirim Email berdasarkan Daftar   |  Filter Super   |   Filter Khusus (filter tebal/miring/coret...) ...
15 Perangkat Teratas12 Teks Tools (Tambahkan Teks, Hapus Karakter, ...)   |   50 + Grafik jenis (Gantt Chart, ...)   |   40+ Praktis Rumus (Hitung usia berdasarkan ulang tahun, ...)   |   19 Insersi Tools (Masukkan Kode QR, Sisipkan Gambar dari Jalur, ...)   |   12 Konversi Tools (Angka ke Kata, Konversi Mata Uang, ...)   |   7 Gabungkan & Pisahkan Tools (Lanjutan Gabungkan Baris, Pisahkan Sel, ...)   |   ... dan banyak lagi

Tingkatkan Keterampilan Excel Anda dengan Kutools for Excel, dan Rasakan Efisiensi yang Belum Pernah Ada Sebelumnya. Kutools for Excel Menawarkan Lebih dari 300 Fitur Lanjutan untuk Meningkatkan Produktivitas dan Menghemat Waktu.  Klik Di Sini untuk Mendapatkan Fitur yang Paling Anda Butuhkan...

Deskripsi Produk


Tab Office Membawa antarmuka Tab ke Office, dan Membuat Pekerjaan Anda Jauh Lebih Mudah

  • Aktifkan pengeditan dan pembacaan tab di Word, Excel, PowerPoint, Publisher, Access, Visio, dan Project.
  • Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru.
  • Meningkatkan produktivitas Anda sebesar 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari!
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
This would be amazing if only the Macro excluded the part of the sting in CAPS not the entire cell from the exceptions list.
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations