У Мухаммеда есть длинный список названий компаний в рабочем листе. В названии каждой компании каким-то образом удалены все пробелы, так что (например) они отображаются как «AstroPhysics» вместо «Astro Physics». Мухаммаду нужен способ обработки всех названий компаний для вставки одного пробела между любыми вхождениями заглавной буквы, кроме первой буквы в ячейке. Он задается вопросом, есть ли простой способ сделать это.

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

В этом случае лучшим решением будет выполнить операцию поиска и замены, чтобы заменить непечатаемый символ фактическим пробелом. (Вы можете выбрать непечатаемый символ — удерживая нажатой клавишу Shift, нажимая стрелку вправо в том месте, где, по вашему мнению, он находится, — и скопировать его. Затем вставьте его в поле «Найти», чтобы выполнить поиск.)

Предполагая, что в данных нет непечатаемых символов, вы также можете попробовать использовать немакро-подход. Возможно, самый простой подход — добавить один или два вспомогательных столбца. В первом вы можете ввести формулу, чтобы определить положение второй заглавной буквы в названии компании. Предполагая, что название компании находится в ячейке A1, следующую формулу следует ввести как формулу массива:

=SMALL(FIND(0,SUBSTITUTE(A1,CHAR(ROW(INDIRECT("65:90"))),0)&0),2)

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

=LEFT(A1,B1-1)&" " & RIGHT(A1,(LEN(A1)-B1)+1)

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

Конечно, вы можете преодолеть эти проблемы, если используете макрос для вставки пробелов. Ниже приводится простой подход, при котором просматривается каждый символ в ячейке. Если символ является прописной буквой, то перед символом вставляется пробел. Когда макрос готов, строка вставляется обратно в ячейку.

Function Add_Spaces(ByVal sText As String) As String    Dim CharNum As Long    Dim FixedText As String    Dim CharCode As Long

FixedText = Left(sText, 1)



For CharNum = 2 To Len(sText)

CharCode = Asc(Mid(sText, CharNum, 1))

If CharCode >= 65 And CharCode <= 90 Then          FixedText = FixedText & " " & Mid(sText, CharNum, 1)

Else          FixedText = FixedText & Mid(sText, CharNum, 1)

End If    Next CharNum

Add_Spaces = FixedText End Function

Использовать макрос просто; если название компании находится в ячейке A1, то используется следующее:

=Add_Spaces(A1)

Более компактный подход заключается в использовании регулярных выражений для поиска прописных букв и вставки пробела, как показано ниже:

Function SplitCaps(str As String) As String     Dim objRegex As Object     Set objRegex = CreateObject("vbscript.regexp")



With objRegex         .Global = True         .Pattern = "([a-z])([A-Z])"

SplitCaps = .Replace(str, "$1 $2")

End With End Function

Этот конкретный метод макросов требует, чтобы вы включили регулярные выражения. Вы делаете это в редакторе VB, выбрав Инструменты | Ссылки, а затем прокрутите доступные ссылки, чтобы найти параметр Microsoft VBScript Regular Expressions 5.5. Убедитесь, что установлен флажок слева от ссылки, затем нажмите OK. Затем вы можете использовать функцию SplitCaps из Excel так же, как вы могли бы использовать функцию Add_Spaces.

Конечно, уместность любого подхода будет во многом зависеть от характеристик данных, с которыми вы работаете. Если исходные названия вашей компании включают несколько последовательных заглавных букв (например, «ABCCorp.»), То в конечном итоге в вашем выводе будет слишком много пробелов, например «ABCCorp». становится «A B C Corp.» вместо «ABC Corp.». Кроме того, небуквенные символы могут помешать работе, например «H&M»

становится «H&M» или остается как «H&M» (в зависимости от используемого вами макро-подхода).

Чтобы преодолеть некоторые из этих странных названий компаний, вы можете попробовать другой макро-подход, который проверяет, есть ли несколько последовательных заглавных букв, и соответствующим образом корректирует вывод.

Sub AddSpaces()

Dim i As Integer     Dim j As Integer     Dim PriorCap As Boolean     Dim temp As String

j = 1

Do While Cells(j, 2) <> ""

Cells(j, 3) = ""

PriorCap = True         For i = 1 To Len(Cells(j, 2))

Select Case Mid(Cells(j, 2), i, 1)

Case "A" To "Z", "-"

If PriorCap = False Then                         Cells(j, 3) = Cells(j, 3) & " " & _                           Mid(Cells(j, 2), i, 1)

Else                         Cells(j, 3) = Cells(j, 3) & _                           Mid(Cells(j, 2), i, 1)

End If                     PriorCap = True                 Case Else                     Cells(j, 3) = Cells(j, 3) & _                       Mid(Cells(j, 2), i, 1)

PriorCap = False             End Select         Next i         j = j + 1     Loop End Sub

_Примечание: _

Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах ExcelTips), я подготовил специальную страницу, содержащую полезную информацию.

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

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

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