Превращаем полное ФИО в Фамилию + Инициалы.
Если Фамилия, Имя и Отчество (ФИО) хранятся в одной ячейке (например, в
B1
) и отсутствуют лишние пробелы, то превратить полное ФИО в Фамилию + инициалы можно с помощью этой формулы (см.
файл примера
):
=ЛЕВСИМВ(B1;НАЙТИ(» «;B1;1)+1)&».»&ПСТР(B1;НАЙТИ(» «;B1;НАЙТИ(» «;B1;1)+1)+1;1)&».»
Формула определяет позицию начала Имени и Отчества по пробелам (код символа пробел =32). Очевидно, что порядок следования фамилии и имени с отчеством важен для работы формулы.
Формула
НАЙТИ(» «;B1;1)
в выражении
Иванов Иван Иванович
. Соответственно, выражение
ЛЕВСИМВ(B1;НАЙТИ(» «;B1;1)+1)
выводит фамилию и первый инициал (первую букву имени).
Гарантировать, что в ячейке отсутствуют лишние пробелы можно предварительно применив формулу
СЖПРОБЕЛЫ()
.
Чтобы превратить
Иванов Иван Иванович
(в ячейке
B2
) в
И.И. Иванов
(инициалы перед фамилией) используйте формулу =
ПСТР(B2;НАЙТИ(СИМВОЛ(32);B2;1)+1;1)&».»&ПСТР(B2;ПОИСК(СИМВОЛ(32);B2;НАЙТИ(СИМВОЛ(32);B2;1)+1)+1;1)&». «&ЛЕВСИМВ(B2;НАЙТИ(СИМВОЛ(32);B2;1)-1)
Совет
:
Если бы фамилия, имя и отчество хранились в отдельных ячейках
, то формула была бы гораздо проще
=ЛЕВСИМВ(B1;1)&».»
То же относится и к другим строковым значениям:
Адрес (индекс, страна, город, улица); Название книги (автор, название), банковские реквизиты (номер счета, банк, БИК) и др.
Формулы становятся значительно проще, если разные по смыслу данные хранятся в отдельных ячейках.
Преобразование имени и отчества в инициалы с помощью формул Excel и присоединение их к фамилии. Разделение фамилий, имен и отчеств, расположенных в одной ячейке, по разным ячейкам и наоборот.
Фамилия, имя и отчество в разных ячейках
Если фамилия, имя и отчество записываются в разные ячейки, работать с ними легче, чем при размещении их в одной ячейке. Перечислим преимущества:
- при заполнении таких ячеек в таблицах срабатывает автоподбор значений, так как имена и отчества часто повторяются;
- для заполнения таких ячеек можно использовать раскрывающиеся списки наиболее распространенных имен и отчеств;
- фамилия, имя и отчество, записанные в разные ячейки, легко объединить в одну, а имя и отчество заменить инициалами.
Итак, объединяем фамилию, имя и отчество полностью из разных ячеек в одну:
Здесь можно использовать формулу «СЦЕПИТЬ», как в примере =СЦЕПИТЬ(A1;" ";B1;" ";C1), или просто соединить строки с помощью & (амперсандов) =A1&" "&B1&" "&C1, не забыв добавить между словами пробелы.
В следующем примере мы также объединим фамилию, имя и отчество из разных ячеек в одну, при этом имя и отчество заменив на инициалы:
Здесь также можно использовать формулу «СЦЕПИТЬ», как в примере =СЦЕПИТЬ(A1;" ";ЛЕВСИМВ(B1;1);".";ЛЕВСИМВ(C1;1);".") или & (амперсанды) =A1&" "&ЛЕВСИМВ(B1;1)&"."&ЛЕВСИМВ(C1;1)&".", не забыв добавить между фамилией и инициалами пробел, а к инициалам точки. В этом примере мы извлекаем левые символы из имени и отчества для присоединения их к фамилии в виде инициалов.
Фамилия, имя и отчество в одной ячейке
Использование фамилии, имени и отчества в одной ячейке имеет тоже свои преимущества:
- уменьшается количество колонок в таблице;
- в большинство документов* требуется внесение фамилии, имени и отчества в полном написании.
*Если документы генерируются на основе этой таблицы, то вставка ФИО не потребует дополнительных преобразований.
Лично мне в работе не приходилось преобразовывать фамилию, имя и отчество из одной ячейки в разные, но могу предположить, что для кого-то это бывает необходимо:
Для этого преобразования используются следующие формулы в соответствующих ячейках:
- ячейка B1 —
=ЛЕВСИМВ(A1;НАЙТИ(" ";A1;1)-1) - ячейка C1 —
=ПСТР(A1;НАЙТИ(" ";A1;1)+1;НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1)-НАЙТИ(" ";A1;1)-1) - ячейка D1 —
=ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1))
Чтобы определить начало имени и начало отчества используются позиции первого и второго пробелов, найденных с помощью функции «НАЙТИ».
Необходимость следующего преобразования возникает чаще предыдущего, используется для заполнения различных документов наряду с полным именем:
Для этого преобразования используется следующая формула в ячейке B1 — =СЦЕПИТЬ(ЛЕВСИМВ(A1;НАЙТИ(" ";A1;1));" ";ПСТР(A1;НАЙТИ(" ";A1;1)+1;1);".";ПСТР(A1;НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1)+1;1);".")
Здесь тоже функцию «СЦЕПИТЬ» можно заменить & (амперсандами) — =ЛЕВСИМВ(A1;НАЙТИ(" ";A1;1))&" "&ПСТР(A1;НАЙТИ(" ";A1;1)+1;1)&"."&ПСТР(A1;НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1)+1;1)&"."
Вы можете копировать эти формулы в свои файлы, не забывая изменять адреса ячеек на фактические. Часто инициалы пишут перед фамилией, изменить формулы для такого отображения ФИО несложно.
Автор надстройки PowerQuick
-
17 июля, 2020 -
,
Статьи по Excel
В статье описаны формулы, позволяющие быстро преобразовать ФИО людей в инициалы
Иногда в работе необходимо преобразовать имена и отчества людей в инициалы. Это можно быстро сделать при помощи автозаполнения Excel (см.видео ниже), а также при помощи приведенных ниже формул (в формулах полное имя расположено в ячейке A1):
1. Преобразование Иванов Иван Иванович —> Иванов И.И.:
=ЛЕВСИМВ(A1;ПОИСК(” “;A1)+1)&”.”&ПСТР(A1;ПОИСК(” “;A1;ПОИСК(” “;A1)+1)+1;1)&”.”
2. Для преобразования Иванов Иван Иванович —> И.И. Иванов:
=ПСТР(A2;ПОИСК(” “;A2)+1;1)&”.”&ПСТР(A2;ПОИСК(” “;A2;ПОИСК(” “;A2)+1)+1;1)&”. “&ЛЕВСИМВ(A2;ПОИСК(” “;A2))
3. Для преобразования Иван Иванович Иванов —> Иванов И.И.:
=ПСТР(A4;ПОИСК(” “;A4;ПОИСК(” “;A4)+1)+1;ДЛСТР(A4))&” “&ЛЕВСИМВ(A4;1)&”.”&ПСТР(A4;ПОИСК(” “;A4)+1;1)
4. Для преобразования Иван Иванович Иванов —> И.И. Иванов:
=ЛЕВСИМВ(A3;1)&”.”&ПСТР(A3;ПОИСК(” “;A3)+1;1)&”. “&ПРАВСИМВ(A3;ДЛСТР(A3)-ПОИСК(” “;A3;ПОИСК(” “;A3)+1))
Как можно заметить, формулы достаточно объемны. Быстро вводить данные и многие другие формулы Excel можно при помощи нашей надстройки PowerQuick:
Необходимость объединить текст в Excel появляется очень при работе с многими документами, но многие не знают, как можно очень просто и быстро справится с этой задачей с помощью табличного редактора MS Excel.
В этой статье мы на примерах рассмотрим, как в Excel объединить ячейки с текстом. Объединить текст в нескольких ячейках можно несколькими способами. Рассмотрим каждый из них.
Все рассмотренные далее способы объединения текста применяются для всех версий: Excel 2003, Excel 2007, Excel 2010, Excel 2013, Excel 2016. Рассмотрим каждый из них.
Объединить ячейки с текстом с помощью знака амперсанда
Итак, первый способ. Самый простой способ объединить текст в нескольких ячейках в Excel – это использовать знак амперсанда (&).
Например, у нас есть таблица с тремя столбцами в которых содержится фамилия, имя и отчество сотрудников компании или учащихся, студентов.
Объединить текст в Excel – Таблица с данными
Нам необходимо создать столбец, который будет содержать в одной ячейке фамилию, имя и отчество. То есть объединить текст в нескольких ячейках Excel.
1. Переходим ячейку D2. В данной ячейке мы будем объединять текст из нескольких ячеек.
2. Вводим знак =, и кликаем по ячейке с фамилией (A2), затем вводим знак амперсанда & при помощи сочетания клавиш SHIFT+7.
3. Далее нам необходимо поставить разделитель, чтобы наша фамилия «не слиплась» с именем и отчеством. В данном случае мы будем использовать пробел. Для этого после амперсанда ставим пробел, заключенный в кавычки (» «).
Разделитель необязательно должным пробелом, вы можете использовать любой символ, знак или комбинацию. Для этого необходимо заключить их в кавычки. Например, если вы хотите разделить объединяемый текст с помощью запятых, то используйте запятую и пробел после нее («, «).
4. Далее снова вводим знак амперсанда &, и кликаем по ячейке с именем (B2).
5. Опять таки не забываем по разделитель – пробел в кавычках (» «).
6. Осталось только присоединить отчество. Вводим знак & и кликаем на ячейку С2.
7. Нажимаем клавишу Enter и любуемся полученным результатом.
Получившаяся формула выглядит так:
=A2&» «&B2&» «&C2
И в нашей ячейке D2 появился объединенный текст из трех ячеек.
Объединить текст в Excel – Объединить текст с помощью амперсанда
Чтобы заполнить все оставшиеся ячейки достаточно выделить ячейку, и перетащить маркер заполнения вниз. Теперь все ячейки заполнены объединенным текстом, и столбец «ФИО» выглядит следующим образом:
Объединить текст в Excel – Объединенный текст для всех сотрудников
Если необходимо объединить текст в двух ячейках, например, объединить фамилию и имя, то формула для первого человека будет выглядеть так:
=A2&» «&B2
Объединить текст в Excel – Объединить 2 ячейки с текстом
Давайте рассмотрим еще один случай, когда необходимо объединить текст в нескольких ячейках, но не полностью. Например, получить в ячейке фамилию и инициалы человека.
Для того чтобы объединить ячейки с текстом, и в тоже время ограничить количество символов в имени и отчестве, нам понадобится функция ЛЕВСИМВ. Данная функция возвращает первый символ, или указанное число первых символов тестовой строки.
Для получения желаемого результата введите следующую формулу:
=A2&» «&ЛЕВСИМВ(B2)&». «&ЛЕВСИМВ(C2)&».»
Таким образом мы получим следующую таблицу:
Объединить текст в Excel – Объединенный текст фамилии и инициалов с помощью амперсанда
Обратите внимание, что в данном случае мы в качестве разделителя используем точку и пробел («. «). Перейдем с следующему способу, как объединить текст в ячейках в Excel.
Объединить текст в ячейках с помощью функции СЦЕПИТЬ
В этом примере для объединения текста в ячейках мы будем использовать функцию «СЦЕПИТЬ», которая объединяет две или несколько текстовые строки в одну, позволяя комбинировать их с произвольным текстом. Рассмотрим пример, как в Excel объединить столбцы с текстом.
Добавим к таблице столбец с названием города.
Объединить текст в Excel – Дополнение исходной таблицы
Пусть мы хотим не только объединить ячейки с текстом, но и составить такого вида текст: «Иванов Иван Иванович, проживающий в городе Москва».
Тогда в ячейке мы вводим в следующую формулу:
=СЦЕПИТЬ(A2;» «;B2;» «; C2;», проживающий в городе «;D2)
Заполняем оставшиеся ячейки с помощью маркера заполнения и в результате получаем такой результат:
Объединить текст в Excel – Объединение текста с помощью формулы СЦЕПИТЬ
Рассмотрим случай, когда нам необходимо объединить текст, дату и числа из разных ячеек в одну.
Объединить текст с датой или числами
В данном случае для того чтобы объединить текст, мы будем использовать функции СЦЕПИТЬ и ТЕКСТ. Функцию СЦЕПИТЬ мы уже изучили, давайте теперь ознакомимся функцией ТЕКСТ.
Данная функция ТЕКСТ позволяет изменить представление числа или времени в удобочитаемом виде. В нашем случае мы будем использовать ее для корректного отображения числа и даты при объединении числовых значений и значений даты с текстовой строкой.
Предположим у нас есть следующая таблица:
Объединить текст в Excel – Исходная таблица с данными
Предположим, что мы хотим не просто объединить текст, а составить предложение, в котором будет говорится, что такой-то сотрудник, такого-то числа реализовал столько-то товаров, и сколько это составило в процентах от общего объема продаж.
Для этого мы должны ввести в ячейку (D2) следующую формулу для первого сотрудника:
=СЦЕПИТЬ(A2; » «; ТЕКСТ($B$1;»ДД.ММ.ГГ»);» реализовал «; B2;» единиц товаров, что составило «;ТЕКСТ(C2;»0%»);» от общего объема продаж»)
Не стоит пугаться. Сейчас мы разберем эту формулу по порядку.
1. А2 – ссылка на ячейку фамилии нашего сотрудника.
2. » « – далее мы вставляем разделитель, в нашем случае это пробел.
3. ТЕКСТ($B$1;»ДД.ММ.ГГ») – при помощи функции ТЕКСТ мы задаем вид даты.
$B$1 – это абсолютная ссылка на дату, ДД.ММ.ГГ – это формат отображаемой даты. В нашем случае исходная дата 13 ноября 2017 г., а мы ее преобразовываем в 13.11.17 (ДД.ММ.ГГ).
4. » реализовал « – продолжаем формировать необходимое нам предложение.
5. B2 – ссылка на ячейку, где хранится информация о количестве реализованной продукции.
6. » единиц товаров, что составило « – добавляем текст.
7. ТЕКСТ(C2;»0%») – ссылка на ячейку C2 указывает на количество процентов, а «0%» – процентный формат.
8. » от общего объема продаж» – завершаем наше предложение.
И заполняем для остальных сотрудников с помощью маркера заполнения.
В результате мы получаем следующее:
Объединить текст в Excel – Объединить текст в ячейке с датой и числами
Ну вот, в итоге мы объединили текст из нескольких ячеек и получили полноценные предложения, которые можно будет вставить, к примеру в отчет.
Объединить текст в ячейках с помощью функции ОБЪЕДИНИТЬ
В версии MS Excel 2016 появилась еще одна функция для объединения текста – функция ОБЪЕДИНИТЬ. Если вы пользователь более ранних версий, то для того чтобы объединить текст, используйте вышеописанные способы.
Обратите внимание, что функция Объединить доступна только при наличии подписки на Office 365.
С помощью функции ОБЪЕДИНИТЬ можно объединить текст из нескольких ячеек, строк или диапазонов.
Сперва разберем синтаксис функции ОБЪЕДИНИТЬ:
=ОБЪЕДИНИТЬ(разделитель; игнорировать_пустые; текст1; [текст2]; …)
- разделитель – текстовая строка, которой будет разделятся объединенный текст;
- игнорировать_пустые – принимает значение ИСТИНА (1) или ЛОЖЬ (0). Если необходимо пропустить пустые ячейки, то устанавливаем значение ИСТИНА;
- текст1 – текстовый элемент, который необходимо объединить;
- [текст2]; … – дополнительные текстовые элементы для объединения.
Рассмотрим пример, как объединить текст в Excel используя функцию ОБЪЕДИНИТЬ.
Объединить текст в Excel – Объединить текст с помощью функции ОБЪЕДИНИТЬ
На этом мы закончим статью, как в Excel объединить текст. Мы рассмотрели несколько вариантов, как объединить текст в ячейках в Excel с помощью амперсанда (&), функции СЦЕПИТЬ, функции ОБЪЕДИНИТЬ, а также как объединить текст с датой или числами с соответствующими форматами.
На чтение 5 мин. Просмотров 124 Опубликовано 21.05.2021
Вы когда-нибудь думали о том, чтобы объединить имя и фамилию и вернуть только инициалы в Excel? В этой статье рекомендуется несколько способов сделать это легко.
- Объедините первую букву/инициал имени и фамилии с формулой
- Объединить имена и вернуть/получить инициалы с формулой
- Объединить первые фамилии и вернуть/получить инициалы с Kutools for Excel
Вкладка Office Включить редактирование с вкладками и просмотр в Office, и сделать вашу работу намного проще …
Подробнее … Бесплатная загрузка …
Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%.
- Повторное использование всего: добавьте больше использованные или сложные формулы, диаграммы и все остальное в избранное и быстро повторно используйте их в будущем.
- Более 20 текстовых функций: извлечение числа из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния: несколько книг и листов в одну; Объединить несколько ячеек/строк/столбцов без потери данных; Объедините повторяющиеся строки и суммируйте.
- Инструменты разделения: разделение данных на несколько листов в зависимости от значения; Из одной книги в несколько файлов Excel, PDF или CSV; Один столбец в несколько столбцов.
- Вставить пропуск скрытых/отфильтрованных строк; Подсчет и сумма по цвету фона; Массовая отправка персонализированных писем нескольким получателям.
- Суперфильтр: создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделе, дню, частоте и т. Д. Фильтр жирным шрифтом, формулами, комментарием …
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Подробнее … Бесплатная загрузка …
->
Содержание
- Объединить первую букву/инициал имени и фамилии
- Пакетное объединение нескольких ячеек/строк/столбцов без потери данных и формата даты/числа в Excel
- Объединить имя и фамилию и вернуть/получить инициалы
- Объедините имя и фамилию и верните/получите инициалы с помощью Kutools for Excel
- Демо: объединить имена и вернуть/получить инициалы
- Статьи по теме:
Объединить первую букву/инициал имени и фамилии
Этот метод представит функцию СЦЕПИТЬ для объединения только начальной буквы имени и фамилии, например AGreen , представляющий Энн Грин в Excel.
1 . Выберите пустую ячейку, в которую вы поместите результат конкатенации, и введите в нее формулу = СЦЕПИТЬ (ЛЕВЫЙ (A2,1), B2) (A2 – это ячейка с первым именем, а B2 – это ячейка с фамилией). и нажмите клавишу Enter .
2 . Продолжайте выбирать ячейку результата конкатенации и перетаскивайте ее дескриптор автозаполнения в нужный диапазон.
И тогда вы увидите, что он объединяет несколько имен и возвращает начальную букву имени и фамилии. См. Снимок экрана:
Пакетное объединение нескольких ячеек/строк/столбцов без потери данных и формата даты/числа в Excel
Обычно, когда слияние ячеек с помощью функции слияния в Excel удалит все содержимое ячеек, кроме содержимого первой ячейки.Однако с помощью Kutools for Excel’s Combine (Rows & Columns) утилита вы можете легко объединить несколько ячеек/строк/столбцов без потери данных. Более того, эта утилита также поддерживает оставшийся исходный формат даты и числовые форматы в результатах комбинации. Полнофункциональная бесплатная пробная версия на 30 дней!
Объединить имя и фамилию и вернуть/получить инициалы
Иногда вам может потребоваться объединить имя и фамилию и вернуть только инициалы, такие как AG в Excel. Для этого также можно применить формулу.
1 . Выберите пустую ячейку, в которую вы поместите t в результате конкатенации введите формулу = СЦЕПИТЬ (ЛЕВЫЙ (A2,1), «.», ЛЕВЫЙ (B2,1), «.») (A2 – это ячейка с первым именем, а B2 – это ячейка с фамилию) и нажмите клавишу Enter .
 |
Формула слишком сложна для запоминания? Сохраните формулу как запись Auto Text для повторного использования одним щелчком мыши в будущем! Подробнее… Бесплатная пробная версия |
2 . Продолжайте выбирать ячейку результата конкатенации и перетаскивайте ее дескриптор автозаполнения в нужный диапазон.
И тогда вы увидите, что он объединяет несколько имен и возвращает только инициалы, такие как AG , представляющее полное имя Энн Грин . См. Снимок экрана:
Объедините имя и фамилию и верните/получите инициалы с помощью Kutools for Excel
Вы также можете попробуйте Kutools for Excel’s Удалить по позиции , чтобы объединить имена и вернуть инициалы, такие как A.Green или AG, представляющие полное имя Энн Грин.
Kutools for Excel – Включает более 300 удобных инструментов для Excel. Полнофункциональная 30-дневная бесплатная пробная версия, кредитная карта не требуется! Получить сейчас
1 . Выберите столбцы с собственными именами, которые вы объедините, и нажмите Kutools > Text > Удалить по позиции .
Примечание : для возврата A.Green после конкатенации выберите первый столбец имени; для возврата A.G. после конкатенации выберите столбец с именем и столбец с фамилией. В нашем случае мы выбираем и столбец имени, и столбец фамилии.
2 . В открывшемся диалоговом окне «Удалить по позиции» введите 999 в поле Числа , установите флажок Указать и введите 2 в следующее поле и нажмите кнопку ОК . Смотрите снимок экрана:
Kutools for Excel – включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Получить сейчас
И тогда вы увидите, что все символы удалены, кроме первого в каждой ячейке выбранных столбцов.
3 . Выберите пустую ячейку, в которую вы поместите начало конкатенации, и введите формулу = СЦЕПИТЬ (I2, «.», J2, «.») (I2 – это ячейка с именем, а J2 – это ячейка с фамилией). в него, а затем перетащите маркер автозаполнения в нужный диапазон.
Примечание . Если вы удалите все символы, кроме первой буквы, только в столбце имени, примените эту формулу = СЦЕПИТЬ (I2, “.”, J2).
И тогда вы увидите, что имена и фамилии были объединены и возвращены инициалы. См. Снимок экрана:
Демо: объединить имена и вернуть/получить инициалы
Kutools for Excel включает более 300 удобных инструментов для Excel, которые можно бесплатно попробовать без ограничений в течение 30 дней. Загрузить и бесплатную пробную версию !
Статьи по теме:
Объединение с сохранением форматирования числа/даты в Excel
Объединение диапазона с добавлением/включением кавычек в Excel
Объединить диапазон/ячейки с запятой, пробелом, разрывом строки в Excel
Объединить ячейки из другого листа/разных листов в Excel