Как преобразовать текстовую строку в правильный регистр с исключениями в Excel?
В Excel вы можете применить функцию ПРОПИСН для преобразования текстовых строк в правильный регистр легко, но иногда вам нужно исключить определенные слова при преобразовании текстовых строк в правильный регистр, как показано на следующем скриншоте. В этой статье я расскажу о некоторых быстрых приемах для решения этой задачи в Excel.
Преобразование текстовых строк в правильный регистр с исключениями с использованием формулы
Преобразование текстовых строк в правильный регистр с исключениями с использованием кода VBA
Преобразование текстовых строк в правильный регистр с исключениями с использованием формулы
Возможно, следующая формула поможет вам быстро справиться с этой задачей, выполните следующие действия:
Введите эту формулу:
=ПРОПИСН(ЛЕВСИМВ(A2))&ПСТР(СЖПРОБ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(" "&ПРОПИСН(A2)&" "," Of "," of ")," A "," a "),"Is "," is ")," Usa "," USA ")),2,ДЛСТР(A2)) в ячейку, где вы хотите получить результат, и затем перетащите маркер заполнения, чтобы распространить эту формулу, и текстовые строки будут преобразованы в правильный регистр, за исключением определенных слов, см. скриншот:
Примечание: В приведенной выше формуле A2 — это ячейка, которую вы хотите преобразовать, “Of ”, “A”, “Is”, “Usa” — это обычные слова в правильном регистре после преобразования, “of ”, “a”, “is”, “USA” — это слова, которые вы хотите исключить из правильного регистра. Вы можете изменить их по своему усмотрению или добавить другие слова с помощью функции ПОДСТАВИТЬ.
Преобразование текстовых строк в правильный регистр с исключениями с использованием кода VBA
Если приведенная выше формула кажется вам сложной для понимания и изменения под ваши нужды, здесь вы также можете применить код VBA для выполнения этой задачи. Выполните следующие шаги один за другим.
1. Нажмите и удерживайте клавиши ALT + F11, чтобы открыть окно Microsoft Visual Basic for Applications.
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, чтобы закрыть диалоги, и все текстовые строки будут преобразованы в правильный регистр, за исключением указанных слов, см. скриншот:
Лучшие инструменты для повышения продуктивности в Office
Повысьте свои навыки работы в Excel с помощью Kutools для Excel и ощутите эффективность на новом уровне. Kutools для Excel предлагает более300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы выбрать функцию, которая вам нужнее всего...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите режим вкладок для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна вместо новых отдельных окон.
- Увеличьте свою продуктивность на50% и уменьшите количество щелчков мышью на сотни ежедневно!
Все надстройки Kutools. Один установщик
Пакет Kutools for Office включает надстройки для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro — идеально для команд, работающих в разных приложениях Office.
- Комплексный набор — надстройки для Excel, Word, Outlook и PowerPoint плюс Office Tab Pro
- Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
- Совместная работа — максимальная эффективность между приложениями Office
- 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек