В Excel е много лесно да разделите първо, средно и фамилно име, които се появяват в една и съща колона, на отделни колони, като използвате различни начини.
Да предположим, че сте получили списък с контакти с всички имена, изброени с пълни имена в една колона и вероятно ще трябва да разделите първото, средното и фамилното име и да ги разделите на отделни колони. Има няколко различни начина, които можете да използвате за разделяне на имена – като използвате функцията Текст към колони, Flash запълване и формули.
В Excel е наистина лесно да разделите имената от една колона на две или повече колони. В тази статия ще ви покажем как да разделите имената на различни колони в excel, като използвате различни начини.
Как да разделяте имена в Excel
Има три лесни начина за разделяне на имена в Excel. В зависимост от структурата на данните и дали искате имената на разделянето да бъдат статични или динамични, изберете един от следните методи:
- Разделяне на имена с помощта на функцията Текст към колони
- Отделете имената с помощта на формули
- Отделете имената с помощта на Flash Fill
Отделете имена с помощта на съветника за текст в колона
Съветникът за текст в колона е един от най-често срещаните начини за разделяне на собствени и фамилни имена в Excel, както и бащини. Уверете се, че има празни колони до имената, които ще разделите, защото пълните имена ще бъдат разделени на отделни колони.
Например, имате набора от данни по-долу с пълни имена и искате да разделите/разделите първото и фамилното име и да ги съхраните в отделни клетки.
Първо маркирайте колоната с пълни имена, които искате да отделите. След това отидете на раздела „Данни“ и щракнете върху опцията „Текст в колони“ в секцията „Инструменти за данни“.
Ще се отвори съветникът за преобразуване на текст в колони. На първата стъпка на съветника изберете опцията „Разграничени“ и щракнете върху „Напред“.
В стъпка 2 от 3 на съветника за преобразуване на текст в колони изберете разделителя, който разделя вашите данни, премахнете всички други отметки и щракнете върху „Напред“. В нашия случай „интервал“ разделя името и фамилията, така че избираме този разделител.
Разделът за визуализация на данни по-долу в прозореца показва как се анализират вашите имена.
На стъпка 3 от 3 избирате формата на данните и местоназначението и щракнете върху „Край“.
Обикновено „Общи“ по подразбиране ще работи добре за повечето типове данни. В полето „Дестинация“ посочете местоположението, където искате да се показва изходът. Трябва да посочите адреса на първата клетка в колоната, където искате резултатите (B2, в нашия случай).
Не забравяйте, че ако не посочите целевата клетка, съветникът ще презапише оригиналните данни, така че не забравяйте да изберете празна колона.
След като щракнете върху бутона „Край“, той незабавно ще раздели пълните имена в две отделни колони (собствено и фамилно име).
Следвайте същите стъпки, ако имате собствено, бащино и фамилно име и имената ви ще бъдат разделени на три колони вместо на две.
Забележка: Резултатът от този метод е статичен. Това означава, че ако промените оригиналното име, трябва да направите това отново, за да разделите имената.
Разделени имена, разделени със запетаи
Ако името и фамилията са разделени със запетаи, следвайте стъпките по-долу, за да премахнете запетая и да разделите името и фамилията.
В следващия пример имената са форматирани в обратен формат (фамилия, собствено име), където фамилното име идва първо, последвано от запетая, след което следва собственото име.
Изберете имената и отидете на Данни –> Текст в колона. На стъпка 1 изберете „Разделител“ и щракнете върху „Напред“. В стъпка 2, под Разделители, отметнете „Запетая“ (,) като ваш разделител, защото имената ви са разделени със запетая.
На последната стъпка избирате формата на данните като „Общ“, посочвате дестинацията и щракнете върху „Край“.
Сега ще имате имена в отделни колони.
Отделете имена с помощта на формули
Съветникът за текст в колони е бърз и лесен за разделяне на имена. Въпреки това, ако искате да редактирате оригиналните имена и искате динамичен метод, който ще се актуализира автоматично всеки път, когато промените имената, тогава разделянето на имена с формули е правилният избор. Можете да използвате функциите НАЛЯВО, НАДЯСНО, MID, LEN и ТЪРСЕНЕ или НАМИРАНЕ, за да разделите имената.
Отделете име и фамилия в Excel с помощта на формули
Вземете първото име
Да приемем, че имате набора от данни по-долу и искате да разделите първото име в отделна клетка. Трябва да комбинирате функцията FIND и LEFT в една формула, за да получите първото име.
Използвайте следната формула, за да получите първото име:
=ЛЯВО(A2,НАМЕРИ(" ",A2)-1)Тази формула използва функцията FIND, за да намери позицията на символа за интервал (“ “) между името и фамилията и изважда 1, за да изключи самия интервал. След това този номер се предоставя на функцията LEFT, която използва този номер на позиция, за да извлече целия текст преди него. Можете също да използвате функцията ТЪРСЕНЕ вместо функцията НАМИРАНЕ.
След като въведете формулата в празна клетка (B2), плъзнете манипулатора за запълване надолу към други клетки, за да приложите тази формула, и всички имена са разделени на колона B, както е показано по-долу:
Можете или да вложите функциите ТЪРСЕНЕ и НАМИРАНЕ във функцията НАЛЯВО, за да извлечете първото име. Основната разлика между двете функции е, че FIND е чувствителен към малки и големи букви, докато SEARCH е без значение.
Ако имате запетая (,) вместо интервал между първото и фамилното име, тогава използвайте запетая като първи аргумент във функцията FIND:
=ЛЯВ(A2,НАМЕРИ(",",A2)-1)Вземете фамилното име
Сега, ако трябва да извлечете фамилното име, използвайте функцията RIGHT. Следната формула ще извлече фамилното име от същия набор от данни:
=НАДЯСНО(A2,LEN(A2)-НАМИРАНЕ(" ",A2))Формулата първо намира позицията на символа за интервал, изважда това число от общата дължина на низа (която се връща от функцията LEN) и след това това число се предоставя на функцията RIGHT, за да извлече толкова много знаци от края на низа (име).
Отделете първо, средно и фамилно име в Excel с помощта на формули
Разделянето на имена, които включват второ име, се нуждае от различни формули, в зависимост от формата на името, който имате.
За да получите първо име когато имате бащино име или среден инициал, използвайте същата формула LEFT FIND, с която вече сте запознати.
Вземете фамилното име
Горната формула DIGHT FIND работи добре, когато има само име и фамилия, няма да е от голяма полза, ако оригиналните ви имена съдържат бащино име или среден инициал. Това е така, защото не сте отчитали два интервала в името.
За да получите фамилното име, когато имате и второ име, използвайте тази формула:
=ДЯСНО(A2,LEN(A2)-ТЪРСЕНЕ(" ",A2,ТЪРСЕНЕ(" ",A2,1)+1))За да извлечете фамилното име, първо определете позицията на втория символ за интервал, като използвате вложените функции SEARCH, добавете 1 към SEARCH(” “,A2,1), за да започнете извличането със следващия знак. След това извадете позицията на 2-ри интервал от общата дължина на низа и получете дължината на фамилното име като резултатно число. След това дайте това получено число на функцията RIGHT, за да извлече броя на знаците от края на низа.
Вземете второто име
Функцията MID използва три аргумента, първият аргумент определя текста или адреса на клетката, вторият определя началната позиция, а последният аргумент казва броя на знаците за извличане на средното име от тази позиция.
Синтаксисът:
=MID(текст, начален_номер, брой_знаци)За да получите средното име, въведете тази формула в празна клетка:
=СРЕДА(A2,ТЪРСЕНЕ(" ",A2)+1,ТЪРСЕНЕ(" ",A2,ТЪРСЕНЕ(" ",A2)+1)-ТЪРСЕНЕ(" ",A2)-1)Нека видим как работи тази сложна формула:
За да извлечете второто име или средния инициал, трябва да определите позицията на двете интервали в пълното име. За да намерите позицията на първия интервал, въведете това ТЪРСЕНЕ(" ",A2) функция в аргумента „start_num“ и добавете 1, за да започнете извличането от следващия символ.
След това, за да разберете дължината на второто име, поставете това ТЪРСЕНЕ(" ",A2,ТЪРСЕНЕ(" ",A2)+1)-ТЪРСЕНЕ(" ",A2)-1 вложена функция в аргумента ‘num_chars’, която изважда позицията на 1-вото пространство от позицията на 2-рото пространство и изважда 1 от резултата, за да премахне крайно интервал. Крайният резултат ще ви каже колко знака да извлечете.
Сега функцията MID с начална позиция на средното име и цифрите за извличане разделя средното име от пълното име (A2).
Отделете имена в Excel с помощта на Flash Fill
Флаш запълването се използва най-вече за автоматично попълване на данни за конкретен модел. Може да се използва и за разделяне на имена. Предлага се само в Excel 2013, 2016, 2019 и 365.
Да приемем, че имате набора от данни по-долу и искате да получите само първите имена от пълните имена.
В съседната клетка до оригиналното име въведете първото име. В този случай въведете „Стив“ в клетка B2.
След това започнете да пишете първото име във втората клетка на колоната. Докато пишете, ако Excel усети шаблон, Flash Fill ще ви покаже автоматично списък с името в други клетки (в сиво).
Когато видите списъка с имена в сиво и ако тези имена са правилни, просто натиснете клавиша „Enter“ и Flash Fill автоматично ще запълни останалата част от колоната с имена.
Следвайте същата процедура, за да разделите фамилните имена в отделна колона.
Резултатът:
Flash Fill работи, като открива модел в тези данни и следва този модел, като ви дава променените данни. Първоначално, когато въведете първото име в първата клетка, Flash Fill не разпознава шаблона. Но когато започнете да пишете първото име отново във втората клетка, Flash Fill разпознава шаблона и ви показва предложението за разделяне на собствени имена. След това просто натиснете клавиша „Enter“.
По принцип функцията Flash Fill е активирана по подразбиране. Ако не работи във вашия Excel, след като въведете първото име в първата клетка, можете просто да изберете втората клетка и да щракнете върху бутона „Flash Fill“ от групата Инструменти за данни в раздела „Данни“.
Като алтернатива можете също да натиснете „Ctrl“ + „E“, за да получите същите резултати.
Сега останалите клетки ще бъдат попълнени с собствени имена.
Понякога може да не видите предложението за шаблон в сиво, в този случай можете да използвате манипулатора за запълване, за да получите резултата Flash Fill.
Първо въведете имената ръчно в две клетки и изберете и двете клетки. След това задръжте курсора на мишката в долния десен ъгъл на селекцията. Ще забележите, че курсорът се променя от малък зелен квадрат (икона на пълнител) в икона на плюс.
След това щракнете двукратно върху тази икона плюс. Това ще запълни останалите клетки. В този момент резултатите са неправилни, ще видите, че и двете имена се повтарят отново и отново. След това, в долния десен ъгъл на получените данни, ще видите малка икона за автоматично попълване, както е показано по-долу. Кликнете върху тази икона „Автоматично попълване“ и изберете „Flash Fill“.
Това ще попълни собствени имена във всички клетки:
Премахнете второто име
Можете също да използвате инструмента Flash Fill, за да се отървете от второто име от пълното име.
Например, да приемем, че имате набора от данни по-долу и искате да получите само първото и фамилното име без бащиното име или средния инициал.
За да получите имена без средно име или среден инициал, въведете ръчно „Лорд Старк“ в съседната клетка. След това във втората съседна клетка въведете „Дейенерис Таргариен“. Докато пишете, Flash Fill ще разпознае шаблон и ще ви покаже списък с имена без средни имена (в сиво).
Ако предложението е правилно, натиснете клавиша „Enter“ и Flash Fill автоматично ще запълни останалите клетки с имена без средното име.
Ако искате да получите само бащините без собствени и фамилни имена, въведете средното име в първите две клетки и използвайте инструмента Flash Fill, за да получите средните имена от всички пълни имена в колона.
Този урок показва как да разделяте имена, докато манипулирате текстовите данни. Тези методи могат да бъдат полезни и при работа с други форми на данни като адреси, имена на продукти, имена на марки и т.н.