Фрэнку нужно ежедневно переводить 4000-5000 имен из верхнего регистра в правильный. Функция PROPER дает ему удовлетворительный результат, который все еще требует ручного просмотра и редактирования, чтобы справиться с сокращениями и т. Д., Которые не должны скрывать. Фрэнк думает, что ему, вероятно, нужна функция для включения поиска в какой-то список исключений, поэтому он задается вопросом, есть ли такая возможность для преобразования регистра в Excel.

Лучший способ справиться с этим будет зависеть от данных, с которых вы начинаете. Например, предположим, что следующие значения двух ячеек в ваших исходных данных:

Big John's Mining, LLC USA

Если ваше исключение заключается в том, что вы не хотите изменять регистр LLC, вам нужен метод, который будет просматривать части каждой ячейки. Если ваше исключение заключается в том, что вы не хотите, чтобы регистр США изменялся, вам нужен метод, который будет оценивать содержимое каждой ячейки в целом.

О втором типе данных легче позаботиться, чем о первом, поэтому давайте рассмотрим первый. Следующая формула основывается на списке исключений, который есть где-то в вашей книге. Этот список исключений необходимо настроить как именованный диапазон с именем Exceptions.

Если ваши исходные данные находятся в столбце A, вы можете поместить эту формулу в ячейку B1, а затем скопировать ее, насколько это необходимо:

=IFERROR(VLOOKUP(A1,Exceptions,1,0),PROPER(A1))

Любая ячейка, которая полностью соответствует чему-либо в вашей таблице исключений, в конечном итоге будет выглядеть точно так же, как исключение, а все, что не полностью соответствует, в конечном итоге будет иметь PROPER.

Что касается первого типа данных (где вам нужно заглядывать внутрь каждой ячейки на предмет исключений), лучше всего полагаться на макрос. Ниже приведен пример того, что вы можете использовать в качестве отправной точки.

Function MyProper(ByVal r As Range) As String     Dim vExceptions As Variant     Dim vReplacements As Variant     Dim vWords As Variant     Dim iRaw As String     Dim J As Integer     Dim K As Integer     Dim sTemp As String

' Exceptions array     vExceptions = Array("USA", "PhD", "LLC", "and", _       "Kentucky", "D.C.")



' Replacements array     vReplacements = Array("USA", "PhD", "LLC", "and", _       "KY", "DC")



' Convert the text to Proper case and store in a string     iRaw = StrConv(r, 3)

' Split the words into an array     vWords = Split(iRaw, " ")

For J = LBound(vWords) To UBound(vWords)

For K = LBound(vExceptions) To UBound(vExceptions)

If UCase(vWords(J)) = UCase(vExceptions(K)) Then                 vWords(J) = vReplacements(K)

End If         Next K     Next J

' Rebuild the cell contents     sTemp = ""

For J = LBound(vWords) To UBound(vWords)

sTemp = sTemp & " " & vWords(J)

Next J

MyProper = Trim(sTemp)

End Function

Это определяемая пользователем функция, поэтому для преобразования исходных данных вы можете использовать следующее:

=MyProper(A1)

Скорость макроса будет зависеть от двух вещей: количества раз, которое он используется на вашем листе (количества слов, которые вам нужно изменить), и того, сколько исключений вы проверяете в макросе. При проверке 4000-5000 слов и дюжины или около того исключений макрос должен работать достаточно быстро, чтобы быть приемлемым. (Это определенно будет быстрее, чем проверять вручную!)

Функция использует два массива: vExceptions и vReplacements. Он разделяет содержимое ячейки на массив vWords с помощью функции Split. (После выполнения функции Split каждый элемент массива vWords будет содержать слово, как определено появлением пробела.) Затем каждый элемент массива vWords сравнивается с каждым элементом массива vExceptions. Если они совпадают (или, точнее, если совпадают версии каждого из них в верхнем регистре), то вместо исходного слова используется соответствующий элемент массива vReplacements. Этот подход имеет дополнительное преимущество, позволяя вам заменять акронимы, как это делается при замене KY на Kentucky и DC на D.C.

Помните, я упоминал, что этот макрос — только хорошая отправная точка.

Очевидно, вам нужно будет изменить его, чтобы отразить ваши списки исключений и замен. Кроме того, вы должны понимать, что если в исходных данных есть знаки препинания, они считаются частью «слов», расчлененных функцией разделения. Например, если исходные данные содержат что-то вроде «Davis, LLC, Stanton», запятые считаются частью слов, за которыми они следуют. (Помните, что разделение выполняется по пробелам.) Таким образом, в результате вы получите «Davis, Llc, Stanton», потому что «LLC» в массиве vExceptions не будет соответствовать «LLC» в vWords массив.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (7840) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.