Въпрос към урок: Събиране на текст от курс: Анализи с Excel

Може ли да добавя?

MS Excel
Електронни таблици
MS Office
Excel VBA
Анализи с Excel

Здравейте, тъй като в чести случаи ми е необходимо да имам 1 сложна формула в 1 колона, вместо да правя много нови помощни колони, споделям пълната формула за изваждане на 3тото име от 1 клетка: =RIGHT(B3;(LEN(B3))-(SEARCH(" ";B3;(SEARCH(" ";B3))+1)))

Добро допълнение!

Благодаря!

Формулата работи добре, само ако името е във формат "Иван Иванов Иванов", т.е. в клетка В3 има само име, презиме и фамилия, разделени от интервал.

Често обаче се случва да има повече интервали от необходимото. Например, ако списъкът с имената е изваден от някоя система. По тази причина за всеки случай добре е да се направи малка корекция във формулата:

вместо В3, пишем TRIM(B3). TRIM е функция, която отрязва всички излишни интервали.

Така формулата придобива следния вид: RIGHT(TRIM(B3);(LEN(TRIM(B3)))-(SEARCH(" ";TRIM(B3);(SEARCH(" ";TRIM(B3)))+1)))

Стана твърде дълга като за една клетка, затова предлагам алтернативен вариант:

TRIM(RIGHT(SUBSTITUTE(TRIM(B3);" ";REPT(" ";999));250))

И разбира се, винаги съществува алтернативата Text-to-columns

Чудесно допълнение, Кочо, много благодаря! :)

Интересното е, че миналата седмица ми се случи точно това - имам списък с 3 имена на хора в колона О, но ми трябва колона само с малко и фамилно име, за да им направя сертификати. Ето до каква формула стигнах: =CONCATENATE(LEFT(O2;SEARCH(" ";O2)-1);" ";RIGHT(O2;LEN(O2)-SEARCH(" ";O2;SEARCH(" ";O2)+1)))

Изникна ми друг проблем обаче - тъй като този списък е направен в следствие на самостоятелна регистрация от самите хора, някои от тях бяха въвеждали 3, но други 2 имена => съответно получих грешка тип Value. Това, което направих, беше да пусна формулата за всички и после да ги сортирам по новосъздадената от мен колона - така всички, при които ми се беше получила грешката Value се подредиха най-отдолу и просто ръчно замених диапазона от колоната с този от съседната (оригиналната колона О). Вероятно има и по-умен начин да го направя :) Но стана сравнително лесно. Така че съм много благодарна за тези комбинации от формули! :)

Здравей, Юлия, радвам се, че съм бил полезен.

Не е проблем да се заложи проверка дали в колоната с имената има две или три.

Ако ползваме твоя пример:

=LEN(TRIM(O2))-LEN(SUBSTITUTE(TRIM(O2);" ";""))

Горната формула прави разлика между дължината на въведеното име и дължината на същото име, но с изчистени интервали. Ако е въведено само име и фамилия, ще има един интервал и от там разликата ще е 1, ако е с име, презиме и фамилия, ще има два интервала и от там - разликата ще е две.

И след това с IF се получава това:

=IF(LEN(TRIM(O2))-LEN(SUBSTITUTE(TRIM(O2);" ";""))=2;CONCATENATE(LEFT(O2;SEARCH(" ";O2)-1);" ";RIGHT(O2;LEN(O2)-SEARCH(" ";O2;SEARCH(" ";O2)+1)));O2)

доста сложно се получи, затова формулата може да се разбие в няколко помощни колони.

Когато се работи с имена обаче винаги може да се падне някакво странно име, което да не е обхванато от логиката на формулата ни. Пълното име на Кристияно Роналдо например е Кристиано Роналдо дош Сантош Авейро. А името може да е въведено и с грешка (ТодорКолев). Някои хора пък имат по две фамилии. Въобще няма формула, която да гарантира 100% успеваемост.

TRIM(RIGHT(SUBSTITUTE(TRIM(B3);" ";REPT(" ";999));250))
Кочо би ли обяснил за 999 и 250?

Здравей, Екатерина.

Нека предположим, че в клетка В3 е записано Иван Тодоров

Най-напред правим едно TRIM(B3), с което си гарантираме, че формулата ни ще ползва точно "Иван Тодоров", а не примерно "Иван Тодоров "

След това със SUBSTITUTE казваме на Ексел да замени интервала между "Иван" и "Тодоров" с 999 интервала. Така името става над 1000 символа дълго.

Тогава с RIGHT взимаме 250 символа отдясно, т.е. малко над 240 интервала и 7 символа на "Тодоров"

И вторият TRIM изчиства излишните интервали от стринга

" Тодоров"

И го превръща просто в "Тодоров"

Надявам се да е станало ясно, но ако има нужда - пиши един мейл на kochomindev@gmail.com, за да ти пратя файл

П.П. Доколкото съм запознат, човекът с най-дългата фамилия в света се казва Adolph Wolfeschlegelsteinhausenbergerdorf". Следователно бихме могли да използваме вместо 999 - 90, а вместо 250 - 50, но аз обичам да се подсигурявам

Много благодаря! :)

Вашият отговор:

Научи компютърните програми онлайн от топ експертите на България
Регистрацията в АУЛА ви дава:
  • 20 безплатни урока
  • Трикове и тънкости за софтуера
  • Отговори на вашите въпроси
  • Име
    Имейл
     


    © 2016 Фондация “Аула”. Всички права запазени.
    Autodesk AutoCAD, Autodesk Inventor, Autodesk Civil 3D, Autodesk Revit, Autodesk 3ds Max, Autodesk Maya, Microsoft Word, Microsoft Excel, SharePoint Online, Microsoft PowerPoint, Microsoft Project, Microsoft, Adobe Photoshop, Adobe Illustrator, Adobe InDesign, Adobe After Effects, Adobe Premiere, Adobe Dreamweaver, V-Ray, Solidworks са регистрирани търговски марки на Adobe Systems Incorporated, Microsoft Corporation, Chaos Software Ltd, Autodesk Inc. и Dassault Systèmes SolidWorks Corp.