[решено] Excel 2019: Как преобразовать текст из ячейки одной формулой?

Компьютерный форум OSzone.net (http://forum.oszone.net/index.php)

- Microsoft Office (Word, Excel, Outlook и т.д.) (http://forum.oszone.net/forumdisplay.php?f=115)

- - [решено] Excel 2019: Как преобразовать текст из ячейки одной формулой? (http://forum.oszone.net/showthread.php?t=351397)

stuermer01

17-06-2022 15:44 2986816

Excel 2019: Как преобразовать текст из ячейки одной формулой?

Добрй день.

Подскажите, как преобразовать текст из ячейки одной формулой, без VBA, по такому правилу:

Ячейка А: TSLA JUN 17 640 CALL

Ячейка В: TSLA220617C00640000

Описание правила:
https://help.yahoo.com/kb/SLN13884.html?guccounter=1

Т.е. нужно вместо названия месяца буквами подставлять номер месяца , а вместо CALL - C, PUT - P.

a_axe

17-06-2022 16:22 2986818

Цитата:

Цитата stuermer01

из ячейки одной формулой, без VBA, по такому правилу: »

Не самая моя сильная сторона, но если решать задачу в лоб, то получается такая немаленькая формула:

Код:

=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ЛЕВСИМВ(A5;НАЙТИ(" ";A5)-1) &ПРАВСИМВ( ГОД(СЕГОДНЯ());2) & ВПР(ПСТР(A5;НАЙТИ(" ";A5;1)+1;НАЙТИ(" ";A5;НАЙТИ(" ";A5;1)+1)-1-НАЙТИ(" ";A5;1));{"Jan";"01":"Feb";"02":"Mar";"03":"Apr";"04":"May";"05":"Jun";"06":"July";"07":"Aug";"08":"Sept";"09":"Oct";"10":"Nov";"11":"Dec";"12"};2;0) & ПСТР(A5;НАЙТИ("^";ПОДСТАВИТЬ(A5;" ";"^";2));ДЛСТР(A5)-НАЙТИ("^";ПОДСТАВИТЬ(A5;" ";"^";2))+1);" ";"");"CALL";"C");"PUT";"P")

Цитата:

Цитата stuermer01

JUN »

Вообще стандартное сокращение судя по поиску June, поэтому проверьте и отредактируйте в формуле и остальные сокращения.
Плюс нужно проверить правильность по другим строчкам.

megaloman

17-06-2022 18:11 2986825

Короче?

Код:

=ПСТР(A1;1;4)&(ГОД(СЕГОДНЯ())-2000)*100+ПОИСКПОЗ(ПСТР(A1;6;4);{"JAN ";"FEB ";"MAR ";"APR ";"MAY ";"JUN ";"JUL ";"AUG ";"SEP ";"OKT ";"NOV ";"DEC "};0)&ПСТР(A1;10;2)&ПСТР(A1;13;1)

a_axe

17-06-2022 21:37 2986836

megaloman, да. Тут вопрос, постоянно ли число символов, или оно может меняться. Например начинаться не с TSLA, а скажем с TS

megaloman

17-06-2022 22:04 2986838

a_axe, Это к постановщику вопрос. Можно немного длиннее:

Код:

=ПСТР(A1;1;НАЙТИ(" ";A1)-1)&(ГОД(СЕГОДНЯ())-2000)*100+ПОИСКПОЗ(ПСТР(A1;НАЙТИ(" ";A1)+1;3);{"JAN";"FEB";"MAR";"APR";"MAY";"JUN";"JUL";"AUG";"SEP";"OKT";"NOV";"DEC"};0)&ПСТР(A1;НАЙТИ(" ";A1)+5;2)&ПСТР(A1;НАЙТИ(" ";A1)+8;1)

bredych

18-06-2022 09:22 2986851

Цитата:

Цитата a_axe (Сообщение 2986836)

Как понимаю, это биржевые операции. Индексы там, по памяти, стандартные, 4-значные.

stuermer01

19-06-2022 13:37 2986896

Цитата:

Цитата a_axe

Тут вопрос, постоянно ли число символов, или оно может меняться. Например начинаться не с TSLA, а скажем с TS »

Может быть от 1 до 5 букв вначале, потом идут цифры. Все буквы вначале надо перенести в новый идентификатор.

Тут есть еще нюанс, что в изначальном идентификаторе отсутствует год, а в новом год есть и нужно подставлять тот год, который соответствует месяцу в идентификаторе. Т.е. месяц всегда находится в будущем или настоящем по отношению к сегодня и если сейчас декабрь, то будущий месяц будет уже в новом году. Т.е. ни при смене года ни при наступлении этой даты формула должна показывать правильный год.

megaloman

19-06-2022 14:08 2986899

stuermer01,

Цитата:

Цитата stuermer01

Может быть от 1 до 5 букв вначале, потом идут цифры. »

второй вариант, который чуть длиннее, не привязан к числу букв. Главное, чтобы потом был пробел.
С годом как-то замудрёно, не лучше ли в какой-то общей ячейке прописывать год и уже оттуда брать его при преобразовании?

stuermer01

20-06-2022 21:28 2986991

Цитата:

Цитата megaloman

С годом как-то замудрёно, не лучше ли в какой-то общей ячейке прописывать год и уже оттуда брать его при преобразовании? »

Тоже подойдет.
Т.е. вместо "(ГОД(СЕГОДНЯ())-2000)" подставить ячейку с годом?

stuermer01

22-06-2022 17:31 2987120

Цитата:

Цитата megaloman

второй вариант, который чуть длиннее, не привязан к числу букв. Главное, чтобы потом был пробел. »

Сорри, в постановке задачи забыл указать страйк - дополнил жирным шрифтом. Формула показывает правильно, но только до буквы С/Р, а потом нужно еще добавить 8-значный код страйка, в котором последние 3 символа под дробную часть отведены.
Т.е. 640 -> 00640000; 641.5 -> 00641500

megaloman

23-06-2022 07:52 2987151

Вложений: 1

stuermer01, Жизнь как в сказке: чем дальше, тем страшнее.

Цитата:

Цитата stuermer01

Т.е. вместо "(ГОД(СЕГОДНЯ())-2000)" подставить ячейку с годом? »

Я разместил год в ячейке "B1"
Не понимаю, что такое страйк, но если перед цифрами должно быть 2 нуля (подробности Вы тщательно скрываете :)), то

Код:

=ПСТР(A2;1;НАЙТИ(" ";A2)-1)&($B$1-2000)*100+ПОИСКПОЗ(ПСТР(A2;НАЙТИ(" ";A2)+1;3);{"JAN";"FEB";"MAR";"APR";"MAY";"JUN";"JUL";"AUG";"SEP";"OKT";"NOV";"DEC"};0)&ПСТР(A2;НАЙТИ(" ";A2)+5;2)&ПСТР(A2;НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+8)+1;1)&ПСТР("00"&ПОДСТАВИТЬ(ПСТР(A2;НАЙТИ(" ";A2)+8;НАЙТИ(" ";A2;НАЙТИ(" ";A2)+8)-(НАЙТИ(" ";A2)+8));".";"")&"0000000";1;8)

stuermer01

23-06-2022 20:21 2987202

Цитата:

Цитата megaloman

Не понимаю, что такое страйк, но если перед цифрами должно быть 2 нуля »

Это цена, которая может быть и пятизначной и с дробной частью. Под целую часть резервируются 5 знаков, под дробную (после запятой) - 3 знака.
Работает все правильно, спасибо!

megaloman

24-06-2022 09:10 2987217

Вложений: 1

Цитата:

Цитата stuermer01

Работает все правильно, спасибо! »

Работает всё неправильно!

Цитата:

Цитата stuermer01

цена, которая может быть и пятизначной »

Раньше это не звучало.

Код:

=ПСТР(A6;1;НАЙТИ(" ";A6)-1)&($B$1-2000)*100+ПОИСКПОЗ(ПСТР(A6;НАЙТИ(" ";A6)+1;3);{"JAN";"FEB";"MAR";"APR";"MAY";"JUN";"JUL";"AUG";"SEP";"OKT";"NOV";"DEC"};0)&ПСТР(A6;НАЙТИ(" ";A6)+5;2)&ПСТР(A6;НАЙТИ(" ";A6;НАЙТИ(" ";A6;1)+8)+1;1)&ПСТР(ЕСЛИОШИБКА(ПСТР(A6;НАЙТИ(" ";A6)+8;НАЙТИ(" ";A6;НАЙТИ(" ";A6)+8)-(НАЙТИ(" ";A6)+8))*1;ПОДСТАВИТЬ(ПСТР(A6;НАЙТИ(" ";A6)+8;НАЙТИ(" ";A6;НАЙТИ(" ";A6)+8)-(НАЙТИ(" ";A6)+8));".";",")*1)*1000+100000000;2;8)

Время: 11:28.