Bagaimana cara mengubah string teks menjadi case yang tepat dengan pengecualian di Excel?
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
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...
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!
