Совет. Другие языки - Google-Translated. Вы можете посетить English версия этой ссылки.
Войти
x
or
x
x
Регистрация
x

or

Как преобразовать текстовую строку в правильный случай с исключениями в Excel?

В Excel вы можете применить функцию «Правило» для простого преобразования текстовых строк в правильный случай, но иногда вам необходимо исключить некоторые конкретные слова при преобразовании текстовых строк в соответствующий случай, как показано на следующем скриншоте. В этой статье я расскажу о некоторых быстрых трюках для решения этой задачи в Excel.

Преобразование текстовых строк в правильный случай с исключениями с использованием формулы

Преобразование текстовых строк в правильный случай с исключениями с помощью кода VBA


Преобразование текстовых строк в правильный случай с исключениями с использованием формулы


Может быть, следующая формула поможет вам быстро справиться с этой задачей, пожалуйста, сделайте следующее:

Введите эту формулу:

= ВЕРХНИЙ (ЛЕВЫЙ (A2)) и MID (TRIM (ЗАМЕСТИТЕЛЬ (ЗАМЕЩЕНИЕ (ЗАМЕЩЕНИЕ (ЗАМЕСТИТЕЛЬ («« И ПРОВЕРКА (A2) & »,« Of »,« of »),« A »,« a »),« Is "," is ")," Usa "," USA ")), 2, LEN (A2)) в ячейку, где вы хотите получить результат, а затем перетяните дескриптор заполнения, чтобы заполнить эту формулу, а текстовые строки были преобразованы в правильный случай, но определенные исключения, см. снимок экрана:

Внимание: В приведенной выше формуле, A2 это ячейка, которую вы хотите преобразовать, «Of», «A», «Is», «Usa», являются нормальными правильными словами слова после преобразования, «Из», «а», «есть», «США», слова, которые вы хотите исключить из надлежащего случая. Вы можете изменить их в соответствии с вашими потребностями или добавить другие слова с помощью функции SUBSTITUTE.


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


  • Супер Формула Бар (легко редактировать несколько строк текста и формул); Макет чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
  • Объединить ячейки / строки / столбцы и хранение данных; Содержание сплит-клеток; Объедините дублирующиеся строки и сумму / среднее... предотвратить повторяющиеся клетки; Сравнить диапазоны...
  • Выберите Дубликат или Уникальный Ряды; Выберите пустые строки (все ячейки пусты); Супер найти и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
  • Точная копия Несколько ячеек без изменения формулы ссылки; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
  • Любимые и быстро вставляемые формулы, Диапазоны, графики и рисунки; Шифрование ячеек с паролем; Создать список рассылки и отправлять электронные письма ...
  • Извлечение текстаДобавить текст, Удалить по позиции, Удалить пространство; Создание и печать промежуточных итогов подкачки; Преобразование содержимого ячеек и комментариев...
  • Суперфильтр (сохранить и применить схемы фильтров к другим листам); Расширенный поиск по месяцам / неделям / дням, частоте и более; Специальный фильтр жирным шрифтом, курсивом ...
  • Объединить рабочие тетради и рабочие листы; Объединение таблиц на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Работает с Office 2007-2019 и 365 и поддерживает все языки. Это легко развернуть в вашей компании. Полнофункциональная 60-дневная бесплатная пробная версия.
вкладка kte 201905
  • Включить редактирование и чтение с вкладками в Word, Excel, PowerPoint, Издатель, Доступ, Visio и Проект.
  • Открывайте и создавайте несколько документов в новых вкладках одного и того же окна, а не в новых окнах.
  • Увеличивает вашу производительность на 50% и уменьшает сотни щелчков мышью для вас каждый день!
нижняя часть офиса
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Nathan · 3 months ago
    This would be amazing if only the Macro excluded the part of the sting in CAPS not the entire cell from the exceptions list.