У Лэнса есть набор имен в книге. Все имена находятся в столбце A, у некоторых есть имя и фамилия, а в других используются имя, отчество и фамилия. Ему нужно разделить имена на отдельные столбцы, но мастер преобразования текста в столбцы не дает удовлетворительных результатов. С разделением все в порядке, но проблема двух имен против трех означает, что Лэнсу нужно много вручную обрабатывать данные после того, как они разделены.

Решение проблемы состоит в том, чтобы не полагаться на мастер преобразования текста в столбцы, а вместо этого использовать ряд формул для преобразования имен в столбцы. Достигаемые вами результаты по-прежнему в значительной степени зависят от состояния данных, которые вы анализируете. Если ваши данные имеют формат «первый средний последний» (при этом отчество необязательно), то вы можете использовать следующую формулу для извлечения имени:

=LEFT(A1,SEARCH(" ",A1,1)-1)

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

=IF(ISERROR(SEARCH(" ", RIGHT(A1, LEN(A1) - SEARCH(" ", A1, 1)), 1)) = TRUE, "", LEFT( RIGHT(A1,LEN(A1) - SEARCH(" ", A1, 1)), SEARCH(" ", RIGHT(A1, LEN(A1) - SEARCH(" ", A1, 1)), 1) - 1))

Несмотря на то, что формула здесь представлена ​​в виде 5 строк, на самом деле это одна формула, и ее следует вводить как таковую. Формула проверяет наличие второго пробела в имени. Если возникает ошибка (нет второго пробела), формула ничего не возвращает; отчества нет. Если обнаружен второй пробел (ошибка не возникает), формула возвращает все, начиная с первого пробела и заканчивая вторым пробелом.

Следующая формула используется для извлечения фамилии:

=RIGHT(A1,LEN(A1)-LEN(B1)-LEN(C1)-IF(C1="",1,2))

Эта формула основывается на результатах, которые были возвращены как для имени, так и для отчества. Он возвращает все, что осталось в исходном имени, после учета длины имени (предполагается в ячейке B1) и отчества (предполагается в C1). (См. Рис. 1.)

image

Рисунок 1. Разделение имен по столбцам.

Опять же, эти формулы работают, если имена в столбце A соответствуют довольно строгим правилам. Имена должны быть в формате «первый последний» или «первый средний последний». Если в имени есть квалификаторы, такие как Ms., Dr., Jr. или III, то вы не получите удовлетворительных результатов. Кроме того, если фамилия содержит пробел (например, «Джон ван Камп» или «Мэри-Энн Сент-Мари») или если имя не содержит пробелов вообще, то вы также не получите удовлетворительных результатов.

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

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

Вы можете найти версию этого совета для старого интерфейса меню Excel здесь:

link: / excel-Separating_Names_into_Individual_Columns [Разделение имен на отдельные столбцы].