Перейти к основному содержанию

 Как преобразовать текстовую строку в правильный регистр с исключениями в 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 для выхода из диалоговых окон, и все текстовые строки были преобразованы в правильный регистр, но исключают указанные слова, см. снимок экрана:

Лучшие инструменты для офисной работы

Популярные опции: Найдите, выделите или определите дубликаты   |  Удалить пустые строки   |  Объедините столбцы или ячейки без потери данных   |   Раунд без формулы ...
Супер поиск: Множественный критерий VLookup    VLookup с несколькими значениями  |   VLookup по нескольким листам   |   Нечеткий поиск ....
Расширенный раскрывающийся список: Быстрое создание раскрывающегося списка   |  Зависимый раскрывающийся список   |  Выпадающий список с множественным выбором ....
Менеджер столбцов: Добавить определенное количество столбцов  |  Переместить столбцы  |  Переключить статус видимости скрытых столбцов  |  Сравнить диапазоны и столбцы ...
Рекомендуемые функции: Сетка Фокус   |  Просмотр дизайна   |   Большой Формулный Бар    Менеджер книг и листов   |  Библиотека ресурсов (Авто текст)   |  Выбор даты   |  Комбинировать листы   |  Шифровать/дешифровать ячейки    Отправлять электронные письма по списку   |  Суперфильтр   |   Специальный фильтр (фильтровать жирным шрифтом/курсивом/зачеркиванием...) ...
15 лучших наборов инструментов12 Текст Инструменты (Добавить текст, Удалить символы, ...)   |   50+ График Тип (Диаграмма Ганта, ...)   |   40+ Практических Формулы (Рассчитать возраст по дню рождения, ...)   |   19 Вносимые Инструменты (Вставить QR-код, Вставить изображение из пути, ...)   |   12 Конверсия Инструменты (Числа в слова, Конверсия валюты, ...)   |   7 Слияние и разделение Инструменты (Расширенные ряды комбинирования, Разделить клетки, ...)   |   ... и более

Улучшите свои навыки работы с Excel с помощью Kutools for Excel и почувствуйте эффективность, как никогда раньше. Kutools for Excel предлагает более 300 расширенных функций для повышения производительности и экономии времени.  Нажмите здесь, чтобы получить функцию, которая вам нужна больше всего...

вкладка kte 201905


Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
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
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations