Разделить ФИО на столбцы в Excel
Разбивает «Фамилия Имя Отчество» из одной ячейки на три столбца формулами. Держит двойные фамилии через дефис и строки без отчества, лишние пробелы убирает сам.
Коротко
Задача: в столбце одна ячейка «Фамилия Имя Отчество», а нужны три отдельных — фамилия, имя, отчество. Делается формулами, без «Текста по столбцам» и без макросов, поэтому результат пересчитывается сразу, как только поправите исходную строку.
Разбор идёт по пробелам: первое слово — фамилия, второе — имя, всё, что после второго пробела, — отчество. В примере исходное ФИО лежит в столбце B с шестой строки, результат раскладывается в C (Фамилия), D (Имя), E (Отчество), а в F собирается короткая форма «Фамилия И.О.».
Формулы держат неудобные случаи. «Иванов Иван Иванович» разложится на три части. «Ким Ольга» без отчества даст пустое отчество, а не ошибку. «Салтыков-Щедрин Михаил Евграфович» с двойной фамилией через дефис не сломается — дефис для формулы не пробел. «Смирнов Пётр Алексеевич» с двумя пробелами между фамилией и именем разберётся верно, потому что лишние пробелы снимает СЖПРОБЕЛЫ. Всё это уже лежит в файле — откроете и увидите формулы на живых строках.
То же самое формулой в Excel
=ЕСЛИ($B6="";"";ЛЕВСИМВ(СЖПРОБЕЛЫ($B6);ЕСЛИОШИБКА(НАЙТИ(" ";СЖПРОБЕЛЫ($B6))-1;ДЛСТР(СЖПРОБЕЛЫ($B6))))) — фамилия — слово до первого пробела; нет пробела вовсе — берётся вся строка=ЕСЛИ($B6="";"";ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ПСТР(СЖПРОБЕЛЫ($B6);НАЙТИ(" ";СЖПРОБЕЛЫ($B6))+1;ЕСЛИОШИБКА(НАЙТИ(" ";СЖПРОБЕЛЫ($B6);НАЙТИ(" ";СЖПРОБЕЛЫ($B6))+1);ДЛСТР(СЖПРОБЕЛЫ($B6))+1)-НАЙТИ(" ";СЖПРОБЕЛЫ($B6))-1));"")) — имя — слово между первым и вторым пробелом=ЕСЛИ($B6="";"";ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ПСТР(СЖПРОБЕЛЫ($B6);НАЙТИ(" ";СЖПРОБЕЛЫ($B6);НАЙТИ(" ";СЖПРОБЕЛЫ($B6))+1)+1;100));"")) — отчество — всё после второго пробела; второго пробела нет — ячейка пустая, а не #ЗНАЧ!=ЕСЛИ($C6="";"";$C6&" "&ЕСЛИ($D6="";"";ЛЕВСИМВ($D6;1)&".")&ЕСЛИ($E6="";"";ЛЕВСИМВ($E6;1)&".")) — короткая форма «Фамилия И.О.» из уже разобранных столбцов C, D, EРазбор ФИО: вставляете список — раскладывается на три столбца
Песочница: вставляете ФИО списком в жёлтый столбец, а рядом сами заполняются фамилия, имя, отчество и короткая форма «Фамилия И.О.». Формулы уже протянуты вниз и держат двойные фамилии через дефис, строки без отчества и лишние пробелы. Меняете строку — всё пересчитывается.
Файл
razdelit-fio.xlsx · формулы работают в Excel 2016 и новее
и в Google Таблицах · без макросов · формулы проходят автопроверку
перед публикацией
Пошагово
- Столбец с исходным ФИО оставьте как есть. В примере это
B, первая строка данных — шестая. - Фамилия. В
C6вставьте первую формулу. Она берёт слово до первого пробела. - Имя. В
D6— вторую формулу: слово между первым и вторым пробелом. - Отчество. В
E6— третью: всё, что идёт после второго пробела. - Выделите
C6:E6и потяните за уголок вниз на все строки — формулы размножатся на весь список. - Короткая форма. Нужна «Иванов И.И.» — поставьте в
F6четвёртую формулу. Она собирает фамилию и инициалы из готовыхC,D,E. - Если результат нужен как обычный текст, а не формулы, выделите столбцы, скопируйте и вставьте через «Специальная вставка → Только значения».
Где обычно ломается
Разбор по пробелам спотыкается на четырёх вещах. Все четыре формулы выше уже закрыли.
- Двойные пробелы. «Смирнов Пётр» с двумя пробелами между словами без обработки разберётся неверно: вторым «словом» станет пустота. Поэтому исходная строка везде обёрнута в
СЖПРОБЕЛЫ— она схлопывает повторные пробелы и убирает их по краям. Не снимайте эту обёртку. - Двойная фамилия через дефис. «Салтыков-Щедрин Михаил Евграфович» разбирается правильно сам собой: дефис для формулы не пробел, поэтому «Салтыков-Щедрин» остаётся одним словом и целиком уходит в фамилию. Никаких доработок не нужно.
- Нет отчества. «Иванов Иван» — только два слова. В строке нет второго пробела, и наивная формула вернула бы
#ЗНАЧ!. Здесь второй пробел ищется черезЕСЛИОШИБКА, поэтому отчество выходит пустым, а имя и фамилия считаются как обычно. - Другой порядок слов. Если в исходнике сначала имя, потом фамилия — «Иван Иванов», — формулы разложат это буквально: в столбец «Фамилия» попадёт «Иван». Разбор идёт по позиции слова, а не по смыслу. Поменяйте местами столбцы результата или заголовки — саму строку трогать не нужно.