Одвојите име и презиме у Екцелу (поделите имена помоћу формула)

Гледајте видео - Поделите имена у Екцелу (на име, средње и презиме)

Екцел је невероватан алат када је у питању исецање и сечење текстуалних података.

Постоји толико корисних формула и функција које можете користити за рад са текстуалним подацима у Екцелу.

Једно од врло честих питања које добијам о манипулацији текстуалним подацима је - “Како одвојити име и презиме (или име, средње и презиме) у Екцелу?“.

Постоји неколико једноставних начина за поделу имена у Екцелу. Метод који одаберете зависиће од тога како су ваши подаци структурирани и желите ли да резултат буде статичан или динамичан.

Па почнимо и видимо различите начине за поделу имена у Екцелу.

Поделите имена помоћу текста у колоне

Функција Тект то Цолумнс у програму Екцел омогућава вам брзо раздвајање текстуалних вредности у засебне ћелије у низу.

На пример, претпоставимо да имате скуп података као што је приказано испод и желите да одвојите име и презиме и да их добијете у засебне ћелије.

Испод су кораци за раздвајање имена и презимена помоћу текста у колоне:

1. Изаберите сва имена у колони (А2: А10 у овом примеру)
2. Кликните на картицу „Подаци“
3. У групи „Алати за податке“ кликните на опцију „Текст у колоне“.
4. Направите следеће промене у чаробњаку за претварање текста у колону:
   1. Корак 1 од 3: Одаберите Разграничено (ово вам омогућава да користите размак као сепаратор) и кликните на Даље
   2. Корак 2 од 3: Одаберите опцију Спаце и кликните на Нект
   3. Корак 3 од 3: Поставите Б2 као одредишну ћелију (иначе ће пребрисати постојеће податке)
5. Кликните на Финисх

Горе наведени кораци би одмах поделили имена на име и презиме (са именима у колони Б и презименом у колони Ц).

Напомена: У случају да у ћелијама већ постоје неки подаци (они где се очекује излаз текста у колоне), Екцел ће вам приказати упозорење које вас обавештава да већ постоје неки подаци у ћелијама. Можете изабрати да препишете податке или откажете текст у колоне и прво га ручно уклоните.

Када завршите, можете избрисати податке о пуном имену ако желите.

Неколико ствари које треба знати када користите текст у ступце за одвајање имена и презимена у Екцелу:

1. Резултат овога је статичан. То значи да у случају да имате нове податке или су оригинални подаци имали неке измене, морате то поновити да бисте поделили имена.
2. Ако желите само име или само презиме, можете прескочити колоне које не желите у кораку 3 дијалога „Чаробњак за текст у колону“. Да бисте то урадили, изаберите колону у прегледу коју желите да прескочите, а затим изаберите опцију „Не увози колону (прескочи)“.
3. У случају да не наведете одредишну ћелију, текст у колону ће преписати тренутну колону

Опција Текст у колоне најбоље одговара када имате доследне податке (на пример, сва имена имају само име и презиме или сва имена имају име, средње и презиме).

У овом примеру сам вам показао како да одвојите имена која имају размак као граничник. У случају да је граничник зарез или комбинација зареза и размака, и даље можете да користите исте кораке. У овом случају, можете одредити граничник у кораку 2 од 3 чаробњака. Већ постоји опција да се зарез користи као граничник или можете изабрати опцију „Остало“ и навести прилагођени граничник.

Иако је текст у колоне брз и ефикасан начин за поделу имена, погодан је само када желите да излаз буде статички резултат. У случају да имате скуп података који се може проширити или променити, боље је да користите формуле за одвајање назива.

Одвојите име, средње и презиме помоћу формула

Формуле вам омогућавају да исечете и исечете текстуалне податке и извучете оно што желите.

У овом одељку ћу поделити различите формуле које можете користити за одвајање података о именима (на основу структуре ваших података).

Испод су три формуле помоћу којих можете раздвојити име, средње и презиме (детаљно објашњено касније у следећим одељцима).

Формула за добијање имена:

= ЛИЈЕВО (А2, СЕАРЦХ ("", А2) -1)

Формула за добијање средњег имена:

= МИД (А2, СЕАРЦХ ("", А2)+1, СЕАРЦХ ("", СУБСТИТУТЕ (А2, "", "@", 1))-СЕАРЦХ ("", А2))

Формула за добијање презимена:

= РИГХТ (А15, ЛЕН (А15) -СЕАРЦХ ("@", СУБСТИТУТЕ (А15, "", "@", ЛЕН (А15) -ЛЕН (СУБСТИТУТЕ (А15, "", ""))))

Узми име

Претпоставимо да имате скуп података као што је приказано испод и желите да брзо одвојите име у једној ћелији и презиме у једној ћелији.

Формула испод ће вам дати име:

= ЛИЈЕВО (А2, СЕАРЦХ ("", А2) -1)

Горња формула користи функцију СЕАРЦХ да добије позицију размака између имена и презимена. Функција ЛЕФТ тада користи овај број размака за издвајање целог текста испред њега.

Ово је прилично једноставна употреба издвајања дела текстуалне вредности. Пошто све што треба да урадимо је да идентификујемо прву позицију размака, није важно да ли име има средње име или не. Горња формула ће добро функционисати.

Хајдемо сада напредовати са сваким примером.

Добијте презиме

Рецимо да имате исти скуп података и овај пут морате да добијете презиме.

Формула испод ће извући презиме из горњег скупа података:

= ДЕСНО (А2, ЛЕН (А2) -СЕАРЦХ ("", А2))

Опет, сасвим јасно.

Овај пут прво проналазимо позицију размака, која се затим користи за откривање броја знакова који су остали након размака (што би било презиме).

То се постиже одузимањем вредности позиције размака са укупним бројем знакова у имену.

Овај број се затим користи у функцији ДЕСНО за дохваћање свих ових знакова с десне стране имена.

Иако ова формула одлично функционише када постоје само име и презиме, не би функционисала у случају да имате и средње име. То је зато што смо узели у обзир само један размак (између имена и презимена). Средње име додаје имену више размака.

Да бисте преузели презиме и када имате средње име, користите доњу формулу:

= ДЕСНО (А15, ЛЕН (А15) -ТРАГАЊЕ ("@", ЗАМЕНА (А15, "", "@", ЛЕН (А15) -ЛЕН (ЗАМЕНА (А15, "", ""))))

Ово је почело да постаје помало сложено … зар не?

Дозволите ми да објасним како ово функционише.

Горња формула прво проналази укупан број размака у имену. То се постиже добијањем дужине имена са и без размака, а затим се одузима оно без размака од оног са размаком. Ово даје укупан број размака.

Функција СУБСТИТУТЕ се затим користи за замену последњег размака симболом „@“ (можете користити било који симбол - нешто што је мало вероватно да ће се појавити као део имена).

Након што је симбол @ замењен уместо последњег размака, лако можете пронаћи положај овог симбола @. Ово се ради помоћу функције СЕАРЦХ.

Сада све што требате учинити је издвојити све знакове десно од овог @ симбола. Ово се ради коришћењем функције ДЕСНО.

Узми средње име

Претпоставимо да имате скуп података као што је приказано испод и желите да извучете средње име.

Следећа формула ће то учинити:

= МИД (А2, СЕАРЦХ ("", А2)+1, СЕАРЦХ ("", СУБСТИТУТЕ (А2, "", "@", 1))-СЕАРЦХ ("", А2))

Горња формула користи функцију МИД, која вам омогућава да наведете почетну позицију и број знакова за издвајање из те позиције.

Почетну позицију је лако пронаћи помоћу функције СЕАРЦХ.

Најтежи део је пронаћи колико знакова треба издвојити након ове почетне позиције. Да бисте ово добили, морате идентификовати колико знакова има од почетне позиције до последњег размака.

Ово се може урадити коришћењем функције СУБСТИТУТЕ и заменити последњи размак знаком „@“. Када то учините, можете лако користити функцију СЕАРЦХ да пронађете позицију овог последњег размака.

Сада када имате почетну позицију и позицију последњег размака, можете лако дохватити средње име помоћу функције МИД.

Једна од предности коришћења формуле за одвајање назива је то што је резултат динамичан. Дакле, у случају да се ваш скуп података прошири и да му се дода још имена или ако се неки називи промене у оригиналном скупу података, не морате да бринете о резултујућим подацима.

Одвојена имена помоћу функције Пронађи и замени

Волим флексибилност која долази са „Пронађи и замени“ - јер у њој можете користити џокер знакове.

Дозволите ми прво да објасним шта је џокер знак.

Џокер знак је нешто што можете користити уместо било ког текста. На пример, можете користити симбол звездице (*) и он ће представљати било који број знакова у Екцелу. Да вам дам пример, ако желим да пронађем сва имена која почињу абецедом А, могу да користим А* за проналажење и замену. Ово ће пронаћи и изабрати све ћелије у којима име почиње са А.

Ако вам и даље није јасно, не брините. Наставите да читате и следећих неколико примера ће вам појаснити шта су џокер знакови и како их користити за брзо раздвајање имена (или било које текстуалне вредности у Екцелу).

У свим доле наведеним примерима обавезно направите резервну копију скупа података. Нађи и замени мења податке на којима се користи. Најбоље је прво копирати и залепити податке, а затим користити опцију Пронађи и замени на копираном скупу података.

Узми име

Претпоставимо да имате скуп података као што је приказано испод и желите да добијете само име.

У наставку су наведени кораци за то:

1. Копирајте податке о имену у колону А и залепите их у колону Б.
2. Када су подаци у колони Б изабрани, кликните на картицу Почетна
3. У групи Уређивање кликните на Пронађи и изабери.
4. Кликните на Замени. Ово ће отворити дијалошки оквир „Пронађи и замени“.
5. У дијалог „Пронађи и замени“ унесите следеће
   1. Пронађите шта: * (знак размака иза којег следи симбол звездице)
   2. Замени са: ово поље оставите празно
6. Кликните на Замени све.

Горе наведени кораци ће вам дати име и уклонити све иза имена.

Ово функционише чак и ако имате имена која имају средње име.

Про врх: Пречица на тастатури за отварање дијалога Пронађи и замени је Цонтрол + Х (држите контролни тастер, а затим притисните тастер Х).

Добијте презиме

Претпоставимо да имате скуп података као што је приказано испод и желите да добијете само презиме.

У наставку су наведени кораци за то:

1. Копирајте податке о имену у колону А и залепите их у колону Б.
2. Када су подаци у колони Б изабрани, кликните на картицу Почетна
3. У групи Уређивање кликните на Пронађи и изабери.
4. Кликните на Замени. Ово ће отворити дијалошки оквир „Пронађи и замени“.
5. У дијалог „Пронађи и замени“ унесите следеће
   1. Пронађите шта: * (симбол звездице иза којег следи размак)
   2. Замени са: ово поље оставите празно
6. Кликните на Замени све.

Горе наведени кораци ће вам дати презиме и уклонити све пре имена.

Ово функционише чак и ако имате имена која имају средње име.

Уклоните средње име

У случају да желите да се решите само средњег имена и да имате само име и презиме, то можете учинити помоћу функције Пронађи и замени.

Претпоставимо да имате скуп података као што је приказано испод и желите да уклоните средње име из њих.

У наставку су наведени кораци за то:

1. Копирајте податке о имену у колону А и залепите их у колону Б.
2. Када су подаци у колони Б изабрани, кликните на картицу Почетна
3. У групи Уређивање кликните на Пронађи и изабери.
4. Кликните на Замени. Ово ће отворити дијалошки оквир „Пронађи и замени“.
5. У дијалог „Пронађи и замени“ унесите следеће
   1. Пронађите шта: * (знак размака праћен симболом звездице иза којег следи знак размака)
   2. Замени са: (овде само ставите размак)
6. Кликните на Замени све.

Горе наведени кораци уклонили би средње име из пуног имена. У случају да нека имена немају средње име, она се не би променила.

Одвојена имена помоћу Фласх Филл -а

Фласх филл је представљен у програму Екцел 2013 и олакшава модификацију или чишћење скупа текстуалних података.

А што се тиче раздвајања података о именима, то је тачно горе у уличици Фласх Филл -а.

Најважнија ствар коју треба знати при коришћењу Фласх Филл -а је да постоји образац који Фласх филл може да идентификује. Када идентификује образац, лако ће вам помоћи да поделите имена у Екцелу (ово ће вам бити јасније када прођете кроз неколико примера у наставку).

Добијте име или презиме од пуног имена

Претпоставимо да имате скуп података као што је приказано испод и желите да добијете само име.

1. У суседну ћелију ручно откуцајте име из пуног имена. У овом примеру бих откуцао Рицка.
2. У другој ћелији ручно откуцајте име из имена суседне ћелије. Док куцате, видећете да вам Фласх Филл аутоматски приказује листу имена (сиво).
3. Кад видите имена у сивој боји, брзо их прегледајте да бисте се уверили да приказује права имена. Ако су тачни, притисните тастер ентер и Фласх Филл ће аутоматски попунити остале ћелије именом.

Фласх Филл треба да му дате образац који може да следи када вам даје измењене податке. У нашем примеру, када откуцате име у прву ћелију, Фласх Филл не може да схвати образац.

Али чим почнете да уносите име у другу ћелију, Фласх Филл разуме образац и покаже вам неки предлог. Ако је предлог тачан, само притисните тастер ентер.

А ако није тачно, можете покушати да унесете ручно у још неколико ћелија и проверите да ли Фласх Филл може да разазна образац или не.

Понекад можда нећете видети узорак у сивој боји (као што је приказано у кораку 2 горе). У том случају следите кораке у наставку да бисте добили резултат Фласх Филл:

1. Унесите текст ручно у две ћелије.
2. Изаберите обе ове ћелије
3. Задржите показивач на доњем десном делу селекције. Приметићете да се курсор мења у икону плус
4. Двапут кликните на њу (леви тастер миша). Ово ће попунити све ћелије. У овом тренутку резултати су вероватно нетачни и нису оно што сте очекивали.
5. У доњем десном углу резултирајућих података видећете малу икону аутоматског попуњавања. Кликните на ову икону Аутоматско попуњавање
6. Кликните на Фласх Филл

Горе наведени кораци дали би вам резултат из Фласх Филл -а (на основу обрасца који је закључио).

Такође можете користити Фласх Филл да бисте добили презиме или средње име. У прве две ћелије унесите презиме (или средње име) и Фласх филл ће моћи да разуме образац

Преуредите назив помоћу Фласх Филл -а

Фласх Филл је паметан алат који такође може дешифровати благо сложене обрасце

На пример, претпоставимо да имате скуп података као што је приказано испод и желите да преуредите име из Рика Новака у Новак, Рик (где презиме долази прво, а затим следи зарез, а затим име).

У наставку су наведени кораци за то:

1. У суседну ћелију откуцајте ручно Новак, Рицк
2. У другу ћелију ручно откуцајте Цоннор, Сусан. Док куцате, видећете да вам Фласх Филл приказује листу имена у истом формату (сивом бојом).
3. Кад видите имена у сивој боји, брзо их прегледајте да бисте се уверили да приказује права имена. Ако су тачни, притисните тастер ентер и Фласх Филл ће аутоматски попунити остале ћелије именима у истом формату.

Уклоните средње име (или само преузмите средње име)

Такође можете користити Фласх Филл да бисте се решили средњег имена или добили само средње име.

На пример, претпоставимо да имате скуп података као што је приказано испод и желите да добијете само име и презиме, а не средње име.

У наставку су наведени кораци за то:

1. У суседну ћелију откуцајте ручно Рицк Новак
2. У другу ћелију ручно откуцајте Сусан Цоннор. Док куцате, видећете да вам Фласх Филл приказује листу имена у истом формату (сивом бојом).
3. Кад видите имена у сивој боји, брзо их прегледајте да бисте се уверили да приказује права имена. Ако су тачни, притисните тастер ентер и Фласх Филл ће аутоматски попунити остале ћелије именима без средњег имена.

Слично, ако само желите да добијете средња имена, откуцајте средње име у прве две ћелије и користите Фласх Филл да бисте добили средње име из свих преосталих имена.

Примери приказани у овом водичу користе имена док манипулишу текстуалним подацима. Исте концепте можете користити и за рад са другим форматима података (као што су адресе, називи производа итд.)