Как преобразовать текстовую строку в формат «Первая буква заглавная» с учётом исключений в Excel?
В Excel вы можете воспользоваться функцией ПРОПИСН, чтобы легко преобразовать текстовые строки так, чтобы каждое слово начиналось с заглавной буквы. Однако иногда требуется исключить определённые слова из такого преобразования, как показано на следующем снимке экрана. В этой статье я расскажу о нескольких быстрых способах решения этой задачи в Excel.
Преобразование текстовых строк в Первая буква в верхнем регистре с исключениями с помощью формулы
Преобразование текстовых строк в Первая буква в верхнем регистре с исключениями с помощью кода VBA
Преобразование текстовых строк в Первая буква в верхнем регистре с исключениями с помощью формулы
Возможно, приведённая ниже формула поможет вам быстро решить эту задачу. Выполните следующие действия:
Введите эту формулу:
=UPPER(LEFT(A2))&MID(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(« »&PROPER(A2)&« »,« Of »,« of »),« A »,« a »),«Is »,« is »),« Usa »,« USA »)),2,LEN(A2)) в ячейку, где вы хотите получить результат, затем перетащите маркер заполнения, чтобы скопировать формулу. Текстовые строки будут преобразованы так, чтобы первая буква была заглавной, за исключением указанных слов. См. снимок экрана:
Примечание: в приведённой выше формуле A2 — это ячейка, которую вы хотите преобразовать; «Of », «A», «Is», «Usa» — это слова, которые после преобразования будут оформлены с заглавной буквы; «of », «a», «is», «USA» — это слова, которые вы хотите исключить из правила «первая буква заглавная». Вы можете изменить их по своему усмотрению или добавить другие слова с помощью функции ПОДСТАВИТЬ.
Преобразование текстовых строк в Первая буква в верхнем регистре с исключениями с помощью кода 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. Затем нажмите кнопку ОК, и в появившемся окне выберите ячейки, куда следует вывести результаты (см. снимок экрана):
5. Затем снова нажмите кнопку ОК, и в появившемся диалоговом окне укажите тексты, которые необходимо исключить (см. снимок экрана):
6. После этого нажмите кнопку ОК, чтобы закрыть диалоговые окна. Все текстовые строки будут преобразованы в формат «Первая буква заглавная», за исключением указанных слов (см. снимок экрана):
Лучшие инструменты повышения продуктивности в 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек