Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Удаление последнего слова в ячейке и замена на новый символ
 
#1
02.07.2020 11:35:11
Здравствуйте!

Есть задача над которой бьюсь уже пару дней.

Столбец с ФИО клиентов. Например:
в ячейке А2
Иванов Иван Иванович
Задача вывести в ячейку "Иван Иванович И."

Через доп. ячейки задачу я кое-как сделал.
1. в В2
Код
=ПСТР(A2;ПОИСК(" ";A2)+1;300)&" "&ПСТР(A2;1;ПОИСК(" ";A2))
 /поменял местами Фамилию и имя с отчеством/ Результат:
Иван Иванович Иванов
2. в С2
Код
=ЛЕВСИМВ(B2;ПОИСК(" ";B2;ПОИСК(" ";B2)+1))
/удалил отчество/ Результат:
Иван Иванович
3. в D2 =C2&" "&ПСТР(A2;1;1)&"." /Приписал к результату первую букву фамилии/ Результат:
Иван Иванович И.
Далее вроде даже смог уместить все формулы в одну ячейку без использования дополнительных

Жил бы долго и счастливо, если бы не некоторые иностранцы без отчества.
например у Месси Лионеля возвращаемый результат должен быть "Лионель М."
у меня же
Иванов   Иван ИвановичИван   Иванович  И.
Месси ЛионельЛионель Месси  М.
Петров-Водкин   Кузьма СергеевичКузьма Сергеевич  П.
по количеству символов не получается сделать, потому как у условного Бенедикта Камбербэтча символов в имени больше чем у условного Ким Ильи Юрьевича

если вы дочитали до конца, то помогите написать универсальную формулу для искомого результата
 
 
 
#2
02.07.2020 11:46:02
Олег Дьяченко,а как же иностранцы, у которых несколько имен? Как они занесены в систему? Всякие Остап Сулейман Берта Мария Бендер-бей? Если просто Имя и Фамилия, то достаточно добавить условие по счету количества пропусков (если один - формула для иностранцев, если два - то Ваша уже готовая)
Изменено: Hypohelix - 02.07.2020 11:47:28
Программный код, как и яды, лучше тестировать по капельке
Люблю изобретать велосипеды с колесами произвольной формы
 
 
 
#3
02.07.2020 12:55:37
Цитата
Hypohelix написал:
а как же иностранцы, у которых несколько имен? Как они занесены в систему? Всякие Остап Сулейман Берта Мария Бендер-бей?
Пока не встретил таких. Значит скоро появятся.
ну если попробовать подсчитать количество пробелов через ДЛСТР и ПОДСТАВИТЬ и соответственно настроить формулу, до, скажем, 10...

Спасибо. Подумаю в этом направлении
 
 
 
#4
02.07.2020 13:13:05
Олег Дьяченко,на самом деле, это не такое уж частое явление. По крайней мере у нас. И там проблема именно в том, что чаще имени два. Допустим Рассел Майор Пивот и т.д. Но я не знаю, как у них это отображается в документах. Возможно, имеет смысл делать еще проверку на окончание отчества "*ович" и "*овна" для таких случаев. Посмотрите возможности самого Excel и Power Query в части разбивки по разделителю. Или задача должна быть выполнена именно в формуле?
Программный код, как и яды, лучше тестировать по капельке
Люблю изобретать велосипеды с колесами произвольной формы
 
 
 
#5
02.07.2020 13:20:58
Цитата
Hypohelix написал: Power Query
пока еще не выучил Query.

По окончанию не получится, потому как окончание может быть и без классического "вич"
еще может быть "кызы" или "улы", а так же  просто имя дедушки, например "Дархан Жумахан",

для Казахстана "вна" и "вич" - это иностранцы :-)
 
 
 
#6
02.07.2020 15:29:57
Хм, если Фамилия всегда первая - имеет смысл
Код
=ПРАВСИМВ(B2;ДЛСТР(B2)-ПОИСК(" ";B2))&" "&ПСТР(B2;1;1)&"."

То есть, отсекаем часть, что не Фамилия, добавляем первую букву Фамилии, если правильно понял Вашу формулу
Программный код, как и яды, лучше тестировать по капельке
Люблю изобретать велосипеды с колесами произвольной формы
 
 
 
#7
07.07.2020 07:17:27
Hypohelix, Спасибо.
Фамилия всегда первая.

Так что ваша формула работает, пока не доказано обратное
 
 
 
#8
07.07.2020 07:59:27
Код
=ПСТР(A1;ПОИСК(" ";A1)+1;99)&" "&ЛЕВБ(A1)&"."
Соблюдение правил форума не освобождает от модераторского произвола
 
 
 
Страницы: 1
Наверх