Как преобразовать текстовую строку в правильный регистр с исключениями в Excel?
В Excel вы можете применить правильную функцию для простого преобразования текстовых строк в правильный регистр, но иногда вам нужно исключить некоторые конкретные слова при преобразовании текстовых строк в правильный регистр, как показано на следующем снимке экрана. В этой статье я расскажу о некоторых быстрых приемах решения этой задачи в Excel.
Преобразование текстовых строк в правильный регистр с исключениями с помощью формулы
Преобразование текстовых строк в правильный регистр с исключениями с помощью кода VBA
Преобразование текстовых строк в правильный регистр с исключениями с помощью формулы
Возможно, следующая формула поможет вам быстро справиться с этой задачей, пожалуйста, сделайте следующее:
Введите эту формулу:
= UPPER (LEFT (A2)) & MID (TRIM (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE ("" & PROPER (A2) & "", "Of", "of"), "A", "a"), "Is "," есть ")," США "," США ")), 2, LEN (A2)) в ячейку, в которой вы хотите получить результат, а затем перетащите дескриптор заполнения, чтобы заполнить эту формулу, и текстовые строки были преобразованы в правильный регистр, но с конкретными исключениями, см. снимок экрана:
Внимание: В приведенной выше формуле A2 это ячейка, которую вы хотите преобразовать, «Оф», «А», «Есть», «США» являются нормальными словами в собственном регистре после преобразования, «Из», «а», «есть», «США» слова, которые вы хотите исключить из правильного падежа. Вы можете изменить их по своему усмотрению или добавить другие слова с помощью функции ЗАМЕНА.
Преобразование текстовых строк в правильный регистр с исключениями с помощью кода VBA
Если приведенную выше формулу сложно понять и изменить в соответствии с вашими потребностями, здесь вы также можете применить код VBA для выполнения этой задачи. Пожалуйста, проделайте следующие шаги один за другим.
1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модулии вставьте следующий код в Окно модуля.
Код VBA: преобразование текстовых строк в соответствующий регистр с исключениями:
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, Затем нажмите F5 нажмите клавишу для запуска этого кода, и появится диалоговое окно, напоминающее вам о выборе исходных ячеек, которые вы хотите преобразовать, см. снимок экрана:
4, Затем нажмите OK, выберите ячейки, в которые вы хотите вывести результаты, во всплывающем окне, см. снимок экрана:
5. Иди на клик OKи во всплывающем диалоговом окне выберите тексты, которые вы хотите исключить, см. снимок экрана:
6, Затем нажмите OK для выхода из диалоговых окон, и все текстовые строки были преобразованы в правильный регистр, но исключают указанные слова, см. снимок экрана:
Лучшие инструменты для офисной работы
Улучшите свои навыки работы с Excel с помощью Kutools for Excel и почувствуйте эффективность, как никогда раньше. Kutools for Excel предлагает более 300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы получить функцию, которая вам нужна больше всего...
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!