Как в excel разбить фио на три ячейки

Excel works!

Excel работает за вас

Excel works!

Thanks for Visiting

Как разделить текст в Excel по столбцам?

Предположим, у вас есть большой список ФИО. Необходимо разделить текст в Excel по столбцам, чтобы имя, отчество и фамилия были в разных ячейках. Как разделить текст в Excel по столбцам?

Итак, существует два способа разделить текст по столбцам.

1-й способ разделить текст по столбцам. Мастер текста

Выделите столбец с текстом который хотите разделить. Затем выберите в верхней панели Данные — Текст по столбцам . Появится окно Мастера текста (разбор).

Если хотите разделить текст по пробелам или другим символам выбирайте «с разделителями».

В открывшемся меню выбираем пробел или нужный символ.

Выбираем формат текста и нажимаете «готово». Текст разделен!

Если все данные имеют одинаковую длину, можно разделить по фиксированной ширине (см. картинку 2)

2-й способ. Разделить текст в Excel формулами. Функция ПСТР

Если необходимо разделить текст по столбцам с использованием формулы, воспользуйтесь функциями =ПСТР() и =НАЙТИ().

Для того чтобы найти Фамилию в ячейку В2 занесите формулу

Где функция Найти будет находить кол-во символов до пробела, а функция =ПСТР() возвращать это кол-во символов.

Чтобы получить Имя формула усложняется (необходимо дать функциям знать, где начинается имя т.е. заканчивается 1-й пробел), поэтому введите в ячейку C3

Чтобы найти Отчество в ячейку D3 занесите формулу

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

Как сцепить ячейки Имя, Отчество, Фамилия

Для того чтобы сцепить данные в ячейках воспользуйтесь функцией = СЦЕПИТЬ () или структурой с использованием & (амперсант) — символа сцепки, например, внесите в ячейку D3 формулу

Очень часто с этим вопросом, задают вопрос «Как столбцы сделать строками?»

Источник: excelworks.ru

Делим слипшийся текст на части

Итак, имеем столбец с данными, которые надо разделить на несколько отдельных столбцов. Самые распространенные жизненные примеры:

  • ФИО в одном столбце (а надо – в трех отдельных, чтобы удобнее было сортировать и фильтровать)
  • полное описание товара в одном столбце (а надо – отдельный столбец под фирму-изготовителя, отдельный – под модель для построения, например, сводной таблицы)
  • весь адрес в одном столбце (а надо – отдельно индекс, отдельно – город, отдельно – улица и дом)
  • и т.д.

Способ 1. Текст по столбцам

Выделите ячейки, которые будем делить и выберите в меню Данные – Текст по столбцам (Data – Text to columns) . Появится окно Мастера разбора текстов:

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

На втором шаге Мастера, если мы выбрали формат с разделителями (как в нашем примере) – необходимо указать какой именно символ является разделителем:

Если в тексте есть строки, где зачем-то подряд идут несколько разделителей (несколько пробелов, например), то флажок Считать последовательные разделители одним (Treat consecutive delimiters as one) заставит Excel воспринимать их как один.

Выпадающий список Ограничитель строк (Text Qualifier) нужен, чтобы текст заключенный в кавычки (например, название компании “Иванов, Манн и Фарбер”) не делился по запятой
внутри названия.

И, наконец, на третьем шаге для каждого из получившихся столбцов, выделяя их предварительно в окне Мастера, необходимо выбрать формат:

  • общий – оставит данные как есть – подходит в большинстве случаев
  • дата – необходимо выбирать для столбцов с датами, причем формат даты (день-месяц-год, месяц-день-год и т.д.) уточняется в выпадающем списке
  • текстовый – этот формат нужен, по большому счету, не для столбцов с ФИО, названием города или компании, а для столбцов с числовыми данными, которые Excel обязательно должен воспринять как текст. Например, для столбца с номерами банковских счетов клиентов, где в противном случае произойдет округление до 15 знаков, т.к. Excel будет обрабатывать номер счета как число:

Кнопка Подробнее (Advanced) позволяет помочь Excel правильно распознать символы-разделители в тексте, если они отличаются от стандартных, заданных в региональных настройках.

Способ 2. Как выдернуть отдельные слова из текста

Если хочется, чтобы такое деление производилось автоматически без участия пользователя, то придется использовать небольшую функцию на VBA, вставленную в книгу. Для этого открываем редактор Visual Basic:

  • в Excel 2003 и старше – меню Сервис – Макрос – Редактор Visual Basic(Tools – Macro – Visual Basic Editor)
  • в Excel 2007 и новее – вкладка Разработчик – Редактор Visual Basic (Developer – Visual Basic Editor) или сочетание клавиш Alt+F11

Вставляем новый модуль (меню Insert – Module) и копируем туда текст вот этой пользовательской функции:

Теперь можно найти ее в списке функций в категории Определенные пользователем (User Defined) и использовать со следующим синтаксисом:

=SUBSTRING(Txt; Delimeter; n)

  • Txt – адрес ячейки с текстом, который делим
  • Delimeter – символ-разделитель (пробел, запятая и т.д.)
  • n – порядковый номер извлекаемого фрагмента

Способ 3. Разделение слипшегося текста без пробелов

Тяжелый случай, но тоже бывает. Имеем текст совсем без пробелов, слипшийся в одну длинную фразу (например ФИО “ИвановИванИванович”), который надо разделить пробелами на отдельные слова. Здесь может помочь небольшая макрофункция, которая будет автоматически добавлять пробел перед заглавными буквами. Откройте редактор Visual Basic как в предыдущем способе, вставьте туда новый модуль и скопируйте в него код этой функции:

Теперь можно использовать эту функцию на листе и привести слипшийся текст в нормальный вид:

Источник: www.planetaexcel.ru

Как разделить имя и фамилию в Excel

1 min

Когда вы работаете с данными в своей книге Microsoft Excel, это не всегда просто цифры. Может быть, ваша таблица содержит имена клиентов, клиентов, сотрудников или контактов. И в зависимости от того, откуда берутся ваши данные, вам, возможно, придется манипулировать ими, чтобы удовлетворить ваши потребности в списке рассылки или базе данных.

Если вам нужно разделить имя и фамилию в Excel, у вас есть несколько гибких опций. Это включает в себя не только имя и фамилию, но и отчество, префиксы и суффиксы. Вот полезное руководство, которое показывает, как разделить имена в Excel.

Как разделить имена в Excel с помощью мастера

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

Читайте также:  Как в excel скопировать только видимые ячейки

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

Разделенные имена, разделенные пробелами

Во-первых, если имена разделены пробелами, просто выполните следующие действия.

  1. Выберите столбец или ячейки, содержащие имена, которые вы хотите разделить. Столбец или ячейки будут выделены.
  2. Нажмите на Данные вкладка и выберите Текст в столбцы в твоей ленте.
  3. Во всплывающем окне выберите разграниченный для типа файла, который лучше всего описывает ваши данные.
  4. Нажмите следующий,
  5. Под Разделители, снимите флажок табуляция и проверить Космос,
  6. Нажмите следующий,
  7. Под Формат данных столбца, выбирать Текст, Если вам нужно выбрать другой Место назначения для ваших результатов, введите его в это поле.
  8. Нажмите Конец,

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

Разделенные имена, разделенные запятыми

Если имя и фамилия разделены запятыми, вам потребуется лишь небольшая корректировка приведенных выше инструкций. На шаге 5 под Разделители, проверять запятая, Вы можете сохранить Космос проверил, если данные также имеют пробелы.

Разделение имен и удаление данных

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

  1. Под Формат данных столбца, Выбрать Не импортировать столбец (Пропустить),
  2. в Предварительный просмотр выбранных данныхщелкните, чтобы выделить столбец данных, которые вы хотите удалить из результатов.
  3. Нажмите Конец,

Как разделить имена в Excel с помощью функций

Если вы выполняете одноразовое разделение, описанные выше методы с использованием мастера преобразования текста в столбцы являются наиболее простыми. Но если вы будете добавлять больше данных, которые вы хотите разделить, вы можете использовать формулы Excel

16 формул Excel, которые помогут вам решить реальные проблемы

16 формул Excel, которые помогут вам решить реальные проблемы
Правильный инструмент – это половина работы. Excel может решать вычисления и обрабатывать данные быстрее, чем вы можете найти свой калькулятор. Мы покажем вам основные формулы Excel и покажем, как их использовать.
Прочитайте больше

, Кроме того, хотя мастер обладает гибкостью, у него есть свои ограничения. Итак, мы рассмотрим некоторые функции для особых обстоятельств.

Разделить имя, отчество и фамилию

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

Имя:

Фамилия:

Второе имя:

Замените метки ячеек (комбинация букв и цифр) метками для ячеек, которые вы разделяете.

Имена с префиксом, удалить префикс

Имя:

Фамилия:

Имена с суффиксом, суффикс в отдельной колонке

Имя:

Фамилия:

Суффикс:

Имена из двух частей

Имя:

Фамилия:

Фамилии, состоящие из двух частей

Имя:

Фамилия:

Фамилии, состоящие из трех частей

Имя:

Фамилия:

Еще раз, не забудьте заменить метки ячеек на те для ячеек, которые вы разделяете.

Заполните ваши формулы

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

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

В Excel легко разделять имена

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

Для получения дополнительных уроков по Excel, подобных этим, посмотрите, как объединять и разбивать ячейки

Как объединить и разделить ячейки в Microsoft Excel

Как объединить и разделить ячейки в Microsoft Excel
Существует причина, по которой наименьшая единица в электронной таблице Excel называется ячейкой: ее нельзя разделить дальше. Тем не менее, вот как объединить и разделить ячейки, как вы будете.
Прочитайте больше

или быстро удалите все пустые ячейки

Как быстро удалить все пустые ячейки в Excel

Как быстро удалить все пустые ячейки в Excel
У вас есть электронная таблица Excel с пустыми строками и / или столбцами? Не тратьте время на удаление их вручную. Этот метод позволяет легко.
Прочитайте больше

в Excel.

Узнайте больше о: Microsoft Excel, Microsoft Office 2016, Microsoft Office 2019, электронная таблица.

3 лучшие альтернативы Dropbox, протестированные и сравненные

Источник: okdk.ru

Блог Мачула Владимира

  • Home »
  • Редактор Excel »
  • Разделение в Excel данных по столбцам

В статье «Excel — Оптимизация таблицы» оговаривалось, что для корректной работы с таблицей в каждом ее столбце должны содержаться только логически неделимые данные.

Например, в таблице сотрудников на рис.1, требуется отсортировать данные по фамилиям, а затем по именам сотрудников (для упорядочения однофамильцев). Для этого следует распределить фамилии, имена и отчества по отдельным столбцам.

исходная таблица для разделения данных

Когда требуется разделить данные, находящиеся в одном столбце можно использовать встроенную возможность Excel – разбиение содержимого одного столбца на несколько.

Для этого необходимо выполнить следующие действия:

1. Справа от разделяемого диапазона вставить несколько пустых столбцов по числу отделяемых данных. Это делается, чтобы избежать замены данных в соседних столбцах. В нашем случае вставим два новых столбца (рис.2).

добавляем пустые столбцы

2. Выделить столбец, который содержит разделяемые данные (Фамилия Имя Отчество).

3. На панели Работа с данными вкладки Данные нажимаем кнопку Текст по столбцам.

В открывшемся диалоговом окне Мастер текстов (разбор) – шаг 1 из 3 (рис.3) в области Формат исходных данных указать вариант разбиения:
с разделителями – если текст в ячейках разделен пробелами, запятыми, точками с запятой, знаками табуляции и т.п. (это наш случай – разделение пробелами);
фиксированной ширины – если мы разделяем данные на равные (по столбцам) части.

мастер разделения данных Excel

4. Нажимаем кнопку Далее.

5. В окне Мастер текстов (разбор) – шаг 2 из 3 (рис.4) выбираем символ-разделительпробел. Если среди предложенного набора отсутствует нужный знак, следует установить флажок другой и в поле ввести требуемый символ. При этом в нижней части окна будет приведен образец разбиения.

указываем признак разделения данных

Читайте также:  Как в excel посчитать количество ячеек с текстом

6. Нажатием кнопки Далее переходим в следующее диалоговое окно: Мастер текстов (разбор) – шаг 3 из 3 (рис.5), которое позволяет установить формат данных для каждого нового столбца. Кнопка Подробнее открывает диалоговое окно, в котором можно установить дополнительные настройки числовых данных.
Можно при разбиении исключить отдельный столбец. Для этого следует выделить его в образце разбора и включить опцию Пропустить столбец.

указываем признак разделения данных

7. После установки всех требуемых параметров нажимаем кнопку Готово, в результате чего содержимое выделенного диапазона будет разбито на три столбца (рис.6).

применяем разделение данных

Теперь можно выполнять сортировку по фамилиям и именам сотрудников (рис.7). Для однофамильцев имена будут располагаться в порядке возрастания алфавита.

Источник: machula.ru

Как в excel разбить фио на три ячейки

Модератор форума: _Boroda_, Pelena, китин
Мир MS Excel » Работа и общение » Мозговой штурм » Разделение ФИО в три ячейки с помощью формул (Формулами! “Текст по столбцам” конкретно здесь не предлагать)

Разделение ФИО в три ячейки с помощью формул

Gustav Дата: Пятница, 21.12.2012, 11:44 | Сообщение № 1

Уважаемые коллеги, есть предложение предновогодней разминки на тему этой очень традиционной задачки. Навеяно сообщением отсюда: http://www.excelworld.ru/forum/7-2804-33392-16-1355982200 .

В Интернете решений – масса, от ручного “Текст по столбцам” до пользовательских функций (UDF), формулами – тоже много. Хочется найти – именно формулами, но самое компактное по общему количеству знаков.

Постановка задачи такая. В ячейке A1 находится правильное полное ФИО, без концевых (слева и справа) пробелов и по одному пробелу между словами. В ячейках B1, C1, D1 нужно вывести Фамилию, Имя, Отчество соответственно по отдельности, без (!) концевых пробелов. В формулах можно использовать ссылки на любые из всех четырех ячеек A1:D1 (а не только на исходную A1), т.е. можно, например, как-то использовать результаты, полученные в соседних ячейках.

Подсчет знаков предлагается вести суммированием символов уникальных формул, помещенных в ячейки B1, C1, D1. Это означает, что если все формулы разные, то суммируются количества символов этих трех формул (включая начальные символы = ). Если формула получается одна, но такая, которая в ячейке B1 дает Фамилию, а после копирования в C1 и D1 – Имя и Отчество, то конкурсным результатом является кол-во символов этой одной формулы. Одной считаем и формулу массива, введенную одновременно в 3 ячейки при помощи Ctrl+Shift+Enter (как разновидность копирования). Если же, скажем, в ячейке B1 – одна формула, в C1 – другая, а в D1 – полученная копированием C1, то количество символов решения определяется двумя (уникальными) формулами в B1 и C1.

Можно включать в решение также именованные (Ctrl+F3) формулы. В этом случае помимо чисто формульных символов в поле “Диапазон”, начиная с =, в подсчет включается также само “Имя” именованной формулы (т.е. длина имени).

В качестве начального ориентира есть годовалой давности решение Сержа здесь: http://www.programmersforum.ru/showpost.php?p=894600&postcount=5 .

Там 3 разные формулы: 26 + 108 + 24 = 158 знаков. Пусть это значение и будет начальным приближением. Правда, в этом решении Фамилия и Имя, получающиеся в B1 и C1, содержат правые концевые пробелы. Но я уверен, что в строгом правильном решении знаков может быть существенно меньше.

P.S. Закрепим в правило также разумное ограничение – общая длина текста в A1 не превышает 99 символов.

P.S.2. Сделаем правилом еще одно разумное ограничение, органично напрашивающееся из предыдущего – длина одного компонента (слова) ФИО не превышает 32 символа. Таким образом, максимально 32 символа х 3 слова + 2 пробела = 98 символов, т.е. в пределах 99.

Уважаемые коллеги, есть предложение предновогодней разминки на тему этой очень традиционной задачки. Навеяно сообщением отсюда: http://www.excelworld.ru/forum/7-2804-33392-16-1355982200 .

В Интернете решений – масса, от ручного “Текст по столбцам” до пользовательских функций (UDF), формулами – тоже много. Хочется найти – именно формулами, но самое компактное по общему количеству знаков.

Постановка задачи такая. В ячейке A1 находится правильное полное ФИО, без концевых (слева и справа) пробелов и по одному пробелу между словами. В ячейках B1, C1, D1 нужно вывести Фамилию, Имя, Отчество соответственно по отдельности, без (!) концевых пробелов. В формулах можно использовать ссылки на любые из всех четырех ячеек A1:D1 (а не только на исходную A1), т.е. можно, например, как-то использовать результаты, полученные в соседних ячейках.

Подсчет знаков предлагается вести суммированием символов уникальных формул, помещенных в ячейки B1, C1, D1. Это означает, что если все формулы разные, то суммируются количества символов этих трех формул (включая начальные символы = ). Если формула получается одна, но такая, которая в ячейке B1 дает Фамилию, а после копирования в C1 и D1 – Имя и Отчество, то конкурсным результатом является кол-во символов этой одной формулы. Одной считаем и формулу массива, введенную одновременно в 3 ячейки при помощи Ctrl+Shift+Enter (как разновидность копирования). Если же, скажем, в ячейке B1 – одна формула, в C1 – другая, а в D1 – полученная копированием C1, то количество символов решения определяется двумя (уникальными) формулами в B1 и C1.

Можно включать в решение также именованные (Ctrl+F3) формулы. В этом случае помимо чисто формульных символов в поле “Диапазон”, начиная с =, в подсчет включается также само “Имя” именованной формулы (т.е. длина имени).

В качестве начального ориентира есть годовалой давности решение Сержа здесь: http://www.programmersforum.ru/showpost.php?p=894600&postcount=5 .

Там 3 разные формулы: 26 + 108 + 24 = 158 знаков. Пусть это значение и будет начальным приближением. Правда, в этом решении Фамилия и Имя, получающиеся в B1 и C1, содержат правые концевые пробелы. Но я уверен, что в строгом правильном решении знаков может быть существенно меньше.

P.S. Закрепим в правило также разумное ограничение – общая длина текста в A1 не превышает 99 символов.

P.S.2. Сделаем правилом еще одно разумное ограничение, органично напрашивающееся из предыдущего – длина одного компонента (слова) ФИО не превышает 32 символа. Таким образом, максимально 32 символа х 3 слова + 2 пробела = 98 символов, т.е. в пределах 99. Gustav

Сообщение Уважаемые коллеги, есть предложение предновогодней разминки на тему этой очень традиционной задачки. Навеяно сообщением отсюда: http://www.excelworld.ru/forum/7-2804-33392-16-1355982200 .

В Интернете решений – масса, от ручного “Текст по столбцам” до пользовательских функций (UDF), формулами – тоже много. Хочется найти – именно формулами, но самое компактное по общему количеству знаков.

Постановка задачи такая. В ячейке A1 находится правильное полное ФИО, без концевых (слева и справа) пробелов и по одному пробелу между словами. В ячейках B1, C1, D1 нужно вывести Фамилию, Имя, Отчество соответственно по отдельности, без (!) концевых пробелов. В формулах можно использовать ссылки на любые из всех четырех ячеек A1:D1 (а не только на исходную A1), т.е. можно, например, как-то использовать результаты, полученные в соседних ячейках.

Подсчет знаков предлагается вести суммированием символов уникальных формул, помещенных в ячейки B1, C1, D1. Это означает, что если все формулы разные, то суммируются количества символов этих трех формул (включая начальные символы = ). Если формула получается одна, но такая, которая в ячейке B1 дает Фамилию, а после копирования в C1 и D1 – Имя и Отчество, то конкурсным результатом является кол-во символов этой одной формулы. Одной считаем и формулу массива, введенную одновременно в 3 ячейки при помощи Ctrl+Shift+Enter (как разновидность копирования). Если же, скажем, в ячейке B1 – одна формула, в C1 – другая, а в D1 – полученная копированием C1, то количество символов решения определяется двумя (уникальными) формулами в B1 и C1.

Можно включать в решение также именованные (Ctrl+F3) формулы. В этом случае помимо чисто формульных символов в поле “Диапазон”, начиная с =, в подсчет включается также само “Имя” именованной формулы (т.е. длина имени).

В качестве начального ориентира есть годовалой давности решение Сержа здесь: http://www.programmersforum.ru/showpost.php?p=894600&postcount=5 .

Там 3 разные формулы: 26 + 108 + 24 = 158 знаков. Пусть это значение и будет начальным приближением. Правда, в этом решении Фамилия и Имя, получающиеся в B1 и C1, содержат правые концевые пробелы. Но я уверен, что в строгом правильном решении знаков может быть существенно меньше.

Источник: www.excelworld.ru

Как разделить текст в ячейке Excel?

Добрый день уважаемый читатель!

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

Разобрать слитый текст на необходимые составляющие возможно произвести с помощью:

Мастер разбора текстов

Рассмотрим самый простой способ разделить текст в ячейке, не по сути, а по исполнению. Для примера, очень наглядно это можно продемонстрировать на ФИО в одной ячейке, которые необходимо разделить на отдельные столбики для удобства сортировки и фильтрации.

Для выполнения задачи вызываем диалоговое окно «Мастер текстов (разбор)» и в 3 шага разделяем текст:

  1. Для начала нужно выделить данные, которые необходимо разделить, следующим шагом на вкладке «Данные» в разделе «Работа с данными» нажимаете иконку «Текст по столбцам» и в вызванном диалоговом окне мастера указываем формат рабочего текста. Выбираем 2 вида форматов:
  • С разделителями – это когда существует текст или символ, который условно будет отделять будущее содержимое отдельных ячеек;
  • Фиксированной ширины – это когда при помощи пробелов в тексте имитируется столбики одинаковой ширины.
  1. Вторым шагом, в нашем примере, указываем символ, выполняющий роль разделителя. В случаях, когда в тексте идут подряд пару разделителей, несколько пробелов, к примеру, то установка флажка для пункта «Считать последовательные разделители одним» укажет для Excel принимать их за один разделитель. Дополнительное условие «Ограничитель строк» поможет указать, что текстовые значения, содержащиеся в кавычках не делить (к примеру, название фирмы «Рудольф, Петер и Саймон»);
  2. Последним шагом, для уже разделённых столбиков, нужно указать в диалоговом окне мастера, предварительно выделив их, выбрать необходимый формат получаемых данных:
  • Общий – не проводит изменения данных, оставляя их в первоначальном виде, будет оптимальным выбором в большинстве случаев;
  • Текстовый – данный формат, в основном, необходим для столбиков с числовыми значениями, которые программа в обязательном порядке должна интерпретировать как текст. (К примеру, это числа с разделителем по тысяче или номер пластиковой карточки);
  • Дата – этот формат используется для столбиков с датами, кстати, формат самой даты можно выбрать в выпадающем списке.

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

Рассоединяем текст с помощью формул

Для этого способа нам понадобятся возможности сочетаний функций ПОИСК и ПСТР. При помощи функции ПОИСК мы будем искать все пробелы, которые есть между словами (например, между фамилией, именем и отчеством). Потом функцией ПСТР выделяем необходимое количество символов для дальнейшего разделения.

И если с первыми двумя словами понятно, что и как разделять, то разделителя для последнего слова нет, а это значит что нужно указать в качестве аргумента условно большое количество символов, как аргумент «число_знаков» для функции ПСТР, например, 100, 200 или больше.

А теперь поэтапно рассмотрим формирование формулы для разделения текста в ячейке:

  • Во-первых, нам необходимо найти два пробела, которые разделяют наши слова, для поиска первого пробела нужна формула: =ПОИСК(” “;B2;1), а для второго подойдет: =ПОИСК(” “;B2;C2+1);
  • Во-вторых, определяем, сколько символов нужно выделить в строке. Поскольку позиции разделителя мы уже определили, то символов для разделения у нас будет на один меньше. Значит, будем использовать функцию ПСТР для изъятия слов, с ячейки используя как аргумент «количество_знаков» результат работы предыдущей формулы. Для определения первого слова (Фамилии) нужна формула: =ПСТР(B2;1;ПОИСК(” “;B2;1)), для определения второго значения (Имя): =ПСТР(B2;ПОИСК(” “;B2;1)+1;ПОИСК(” “;B2;ПОИСК(” “;B2;1)+1) -ПОИСК(” “;B2;1)), а теперь определим последнее значение (Отчество): =ПСТР(B2;ПОИСК(” “;B2;ПОИСК(” “;B2;1)+1)+1;100).

В результате мы разделили ФИО на три слова, что позволит с ними эффективно работать.

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

Выдергиваем слова с помощью макросов VBA

Рассмотрим два способа разделить текст в ячейке:

  1. Выдергиваем отдельные слова по разделителю;
  2. Делим текст без пробелов.

Способ №1.

Поскольку вас интересует автоматическое деление текста, значит надо написать хорошую функцию на VBA и внедрить ее в рабочую книгу. Для начала переходим на вкладку «Разработчик» и выбираем «Visual Basic» или вызываем эту возможность с помощью горячего сочетания клавиш Alt+F11. (детальнее в статье «Как создать макрос в Excel»).

Создаем новый модуль в меню «Insert» наживаем пункт «Module» и переносим в него нижеприведенный код:

Источник: topexcel.ru

Добавить комментарий

Adblock
detector