У Manik есть рабочий лист, который в столбце A содержит текстовые значения в формате «mikeDAVIS», где имя человека записано в нижнем регистре, а фамилия — в верхнем регистре. Он хотел бы разделить имена на две отдельные колонки в соответствии с регистром текста.

Это можно сделать с помощью формулы или макроса. Независимо от того, какой подход вы используете, важно выяснить, где текст переключается с нижнего на верхний регистр. Это можно сделать, только изучив каждый символ в строке. Итак, если вы хотите использовать шаблонный подход, вам нужно будет использовать формулу массива. Следующая формула массива возвращает фамилию того, что находится в ячейке A1:

=MID(A1,MATCH(1,(CODE(MID(A1,ROW($1:$255),1))>=65)

* (CODE(MID(A1,ROW($2:$255),1))<90),)+1,255)

Помните, что, поскольку это формула массива, вы должны ввести ее, нажав Ctrl + Shift + Enter. Он возвращает все в ячейке, начиная с первой найденной заглавной буквы. Таким образом, в «mikeDAVIS» он вернет «DAVIS», а в «mikeDavis» он вернет «Davis». Предполагая, что вы используете формулу массива в ячейке B1, вы можете определить имя, используя следующее:

=SUBSTITUTE(A1,B1,"")

Это обычная формула, а не формула массива.

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

=LEFT(A1,MAX((CODE(MID(A$1,ROW(INDIRECT("1:"& LEN(A1))),1))>96)*ROW(INDIRECT("1:"&LEN(A1)))))

Затем вы можете использовать ту же обычную формулу (ту, которая использует функцию ПОДСТАВИТЬ) для получения фамилии.

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

Function GetFirstUpper(MyCell As Range) As Integer     Dim sCellValue As String     Dim i As Integer

sCellValue = Trim(MyCell.Value)

i = 1     Do While (Asc(Mid(sCellValue, i, 1)) > 90 _       Or Asc(Mid(sCellValue, i, 1)) < 65) _       And i < Len(sCellValue) + 1         i = i + 1     Loop     If i > Len(sCellValue) Then         GetFirstUpper = 99     Else         GetFirstUpper = i     End If End Function

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

=LEFT(A1,GetFirstUpper(A1)-1)

=MID(A1,GetFirstUpper(A1),LEN(TRIM(A1))-GetFirstUpper(A1)+1)

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

Function GetFirstName(MyCell As Range) As String     Dim sCellValue As String     Dim i As Integer

sCellValue = Trim(MyCell.Value)

i = 1     Do While (Asc(Mid(sCellValue, i, 1)) > 90 _       Or Asc(Mid(sCellValue, i, 1)) < 65) _       And i < Len(sCellValue) + 1         i = i + 1     Loop     If i > Len(sCellValue) Then         GetFirstName = sCellValue     Else         GetFirstName = Left(sCellValue, i - 1)

End If End Function

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

=GetFirstName(A1)

Небольшая вариация макроса позволит вам аналогичным образом получить фамилию, которая, как предполагается, начинается с первой встреченной заглавной буквы.

Function GetLastName(MyCell As Range) As String     Dim sCellValue As String     Dim i As Integer

sCellValue = Trim(MyCell.Value)

i = 1     Do While (Asc(Mid(sCellValue, i, 1)) > 90 _       Or Asc(Mid(sCellValue, i, 1)) < 65) _       And i < Len(sCellValue) + 1         i = i + 1     Loop     If i > Len(sCellValue) Then         GetLastName = sCellValue     Else         GetLastName = Mid(sCellValue, i)

End If End Function

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

Function GetName(MyCell As Range, sWanted As String) As String     Dim sCellValue As String     Dim i As Integer

sCellValue = Trim(MyCell.Value)

i = 1     Do While (Asc(Mid(sCellValue, i, 1)) > 90 _       Or Asc(Mid(sCellValue, i, 1)) < 65) _       And i < Len(sCellValue) + 1         i = i + 1     Loop     If i > Len(sCellValue) Then         GetName = sCellValue     Else         If LCase(sWanted) = "first" Then             GetName = Left(sCellValue, i - 1)

Else             GetName = Mid(sCellValue, i)

End If     End If End Function

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

=GetName(A1, "First")

Слово «Первый», переданное таким образом в качестве параметра, возвращает имя (все до первой заглавной буквы). Любая другая строка, переданная в качестве второго параметра (например, «Last», «xxx», «Rest» или даже «»), приводит к возвращению фамилии.

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

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

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

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

Этот совет (9091) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365. Вы можете найти версию этого совета для старого интерфейса меню Excel здесь:

link: / excel-Splitting_Cells_by_Case [Разделение ячеек по регистру].