Как извлечь, отчество и Фамилию из одной ячейки в отдельных ячейки в Excel
В этой статье мы покажем вам, как извлечь имя, отчество и фамилию из заданной текстовой строки, используя несколько методов в Microsoft Excel.
Часто вы сталкиваетесь с ситуацией, когда вам дается список имен, содержащий полные имена, и вам нужно разделить их на основе имени, отчества и фамилии, а затем вернуть результат в разные ячейки. Это очень распространенная проблема для любого пользователя Excel, и вы тоже наверняка сталкивались с ней.
Это очень распространенная проблема для многих пользователей Excel, но в Excel нет специальной встроенной функции, которая могла бы извлечь имя, отчество и фамилию из ячейки, содержащей полное имя.
Но в Excel есть несколько методов, которые могут помочь вам в этом. И вот о чем эта статья-урок.
Сегодня мы покажем вам два разных метода, которые можно использовать для разделения имен на разные ячейки.
Самый распространенный способ хранения имени в Excel — это сначала взять имя, затем пробел, затем начальную букву в середине (если есть), затем пробел и затем фамилию. Иногда вместо пробелов для разделения используется запятая.
Переходя к первому примеру, как вы можете видеть, есть несколько имен в «столбце A», которые мы хотим извлечь и вернуть в столбцах B, C и, D как имя, отчество и фамилию соответственно.
Пример 1
_Примечание: в этом руководстве используются случайные имена образцов, просто чтобы объяснить, как извлекать значения и возвращать их в разных столбцах. _
====== _ Обратите внимание, что в этом руководстве используются случайные имена выборок, просто чтобы объяснить, как извлекать значения и возвращать их в разных столбцах. _
Здесь мы будем использовать функцию «Текст в столбец», которая используется для разделения простого содержимого ячеек, такого как имя и фамилия, на отдельные столбцы.
Эта функция находится на вкладке «Данные» в категории «Инструменты для работы с данными». Вы также можете использовать сочетание клавиш «ALT + D + E» («link: / tips-excel-shortcut-keys [Подробнее о сочетаниях клавиш]«) для доступа к мастеру «Текст в столбец».
=== Выберите диапазон от A9 до A17, а затем нажмите «ALT + D + E».
====== _ Обратите внимание, что в этом руководстве используются случайные имена выборок, просто чтобы объяснить, как извлекать значения и возвращать их в разных столбцах. _
И теперь нам нужно выполнить 3 простых шага, и, как вы можете видеть в верхней части окна мастера, мы находимся на первом шаге.
Выберите «С разделителями». Нажмите «Далее». На первом этапе нам нужно сообщить Excel, как наши данные хранятся и форматируются в файле Excel.
Теперь мы находимся на втором ^ шаге этого мастера, и, как вы можете видеть ниже, здесь нам нужно установить разделители, которые содержат наши данные.
====== _ Обратите внимание, что * в этом руководстве используются случайные имена выборок, просто чтобы объяснить, как извлекать значения и возвращать их в разных столбцах ._
Здесь вы можете увидеть несколько флажков «Разделители»; Таким образом, вы можете либо установить эти флажки, либо указать разделитель в поле «Другое». Если ваши данные содержат другие разделители, которых здесь нет. Вы можете выбрать один или несколько разделителей, если в ваших данных более одного разделителя.
====== _ Обратите внимание, что * в этом руководстве используются случайные имена выборок, просто чтобы объяснить, как извлекать значения и возвращать их в разных столбцах ._
Отметьте разделитель «Пробел» и нажмите «Далее»
====== _ Обратите внимание, что * в этом руководстве используются случайные имена выборок, просто чтобы объяснить, как извлекать значения и возвращать их в разных столбцах ._
Переходим к последнему шагу мастера «Текст в столбец».
Выберите «Общие» в формате данных столбца. Выберите B9 как «Место назначения» и заблокируйте его * Нажмите «Готово»
====== Обратите внимание, что * случайные имена образцов используются в этом руководстве, просто чтобы объяснить, как извлекать значения и возвращать их в разных столбцах .
Обратите внимание, что если вы не укажете новую ссылку на целевую ячейку, извлеченные столбцы заменят исходные данные.
Теперь вы можете видеть, что имена в «столбце A» были извлечены в несколько столбцов. Но если вы присмотритесь, то заметите, что в 11-й строке «Стив Смит» нет инициалов от середины, а его фамилия перемещена в столбец отчества.
====== _ Обратите внимание, что * в этом руководстве используются случайные имена выборок, просто чтобы объяснить, как извлекать значения и возвращать их в разных столбцах ._
Это потому, что «Текст в столбец» используется только для разделения простого содержимого на разные ячейки на основе их разделителей. И, поскольку в его имени всего один пробел, оно разделилось только на следующие 2 ячейки. Это единственный недостаток этой функции.
=== Пример 2
_Примечание: во втором примере мы взяли «Мэр Том С. Брэдли в качестве полного имени в ячейке H9, просто чтобы показать пользователям, что, когда в среднем имени больше 1 инициалов, функция извлечет их обоих. _
Теперь мы переходим к примеру 2 ^ nd ^, где мы будем использовать несколько функций вместе, чтобы получить то, что мы ищем. Это усовершенствованный метод извлечения «Имя», «Среднее» и «Фамилия» в разные столбцы. И, чтобы сделать то же самое, мы взяли аналогичные данные в столбце H, и мы собираемся разделить имена на три части: Имя, Отчество и Фамилия в столбцах I, J и K соответственно. B
====== Обратите внимание, что * случайные имена образцов используются в этом руководстве, просто чтобы объяснить, как извлекать значения и возвращать их в разных столбцах .
Это лучшее решение, которое следует учитывать, когда вы хотите разделить имена на «Первое», «Среднее» и «Последнее», но не хотите повторять предыдущий метод каждый раз, когда вы это делаете. Вы можете поместить формулы в ячейки, и в момент обновления ссылочной ячейки имена будут автоматически извлечены в определенные столбцы, в которые вы ввели формулы.
=== Для извлечения имени
Выберите ячейку I9. Введите следующую формулу без кавычек «= LEFT (H9, SEARCH (» «, H9) -1)» * Нажмите Enter
====== _ Обратите внимание, что * в этом руководстве используются случайные имена выборок, просто чтобы объяснить, как извлекать значения и возвращать их в разных столбцах ._
Как вы можете видеть, функция извлекла имя из H9 и вернула его в ячейку I9 с «Mayor». Как вы знаете, функция LEFT используется для извлечения крайнего левого символа и link: / text-excel-search-function [SEARCH]
функция возвращает позицию конкретного символа, который она находит первым в строке. Итак, здесь функция ПОИСК сначала нашла пробел в 6 ^ й ^ позиции в строке, и чтобы исключить пробел, мы указали отрицательное значение, которое помогло ЛЕВОЙ строке выбрать только 5 символов.
=== Для извлечения фамилии
Выберите ячейку K9. Введите следующую формулу в двойных кавычках _ «= TRIM (RIGHT (SUBSTITUTE (H9,» «, REPT (» «, 255)), 255))» _ * Нажмите Enter
====== Обратите внимание, что * случайные имена образцов используются в этом руководстве, просто чтобы объяснить, как извлекать значения и возвращать их в разных столбцах .
Чтобы извлечь фамилию, у нас есть вложенная функция REPT и SUBSTITUTE внутри функции RIGHT. Затем, наконец, мы вложили все условия в TRIM, чтобы удалить все ненужные пробелы. Давайте посмотрим, как все эти утверждения работали вместе, чтобы вернуть фамилию.
REPT вложен в SUBSTITUTE, который повторяет одиночный пробел 255 раз, а затем SUBSTITUTE заменяет одиночный пробел на 255 пробелов в предоставленной строке. И теперь каждый инициал разделен 255 пробелами, а затем функция ВПРАВО используется для извлечения самых верхних 255 символов, а затем TRIM используется для удаления всех лишних пробелов, а затем возврат только с последней буквой имени.
=== Чтобы извлечь отчество
Теперь выберите ячейку J9. Введите следующую формулу без двойных кавычек: «_ = IF (LEN (H9) -LEN (SUBSTITUTE (H9,«
»,« »))> 1, TRIM (SUBSTITUTE (SUBSTITUTE (H9, I9) , «»), K9, «»)), «») «_ * Нажмите Enter
====== _ Обратите внимание, что * в этом руководстве используются случайные имена выборок, просто чтобы объяснить, как извлекать значения и возвращать их в разных столбцах ._
Update: _ _Если вам нужно отделить отчество, которое совпадает с именем или фамилией, используйте следующую формулу в столбце J вместо формулы, упомянутой выше: `= TRIM (MID (H9, LEN (I9) +1, LEN (H9) -LEN (I9) -LEN (K9))) ` Извлечение среднего начального символа выглядит довольно сложно, поскольку нам нужно убедиться, что функция оценивает все условия и извлекает из него только второе имя. Для этого у нас есть вложенные функции LEN, SUBSTITUTE, TRIM внутри IF
В первом аргументе функции IF visiblefunction логическая проверка проверяет используется. И в случае, если https://www.youtube.com/watch?v=SeqWla6pvfw[IF visiblecondition не соответствует логическому тесту, функция вернет пустой текст.
Вот как вы можете использовать функции настройки и простые методы для извлечения «имени», «среднего инициала» и «фамилии» из ячейки, содержащей полное имя.
== Видео: Как извлечь имя, отчество и фамилию из одной ячейки в отдельные ячейки в Excel Ознакомьтесь с этим интересным видеоуроком, в котором мы подробно рассказываем обо всех функциях и методах, используемых для извлечения имен в разные ячейки.
Если вам понравились наши блоги, поделитесь ими с друзьями на Facebook. А также вы можете подписаться на нас в Twitter и Facebook .
Мы будем рады услышать от вас, дайте нам знать, как мы можем улучшить, дополнить или усовершенствовать нашу работу и сделать ее лучше для вас. Напишите нам на [email protected]