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

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

stuermer01

17-06-2022, 15:44

Добрй день.

Подскажите, как преобразовать текст из ячейки одной формулой, без 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

из ячейки одной формулой, без 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")
JUN »
Вообще стандартное сокращение судя по поиску June, поэтому проверьте и отредактируйте в формуле и остальные сокращения.
Плюс нужно проверить правильность по другим строчкам.

megaloman

17-06-2022, 18:11

Короче? =ПСТР(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

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

megaloman

17-06-2022, 22:04

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

megaloman, да. Тут вопрос, постоянно ли число символов, или оно может меняться. Например начинаться не с TSLA, а скажем с TS
Как понимаю, это биржевые операции. Индексы там, по памяти, стандартные, 4-значные.

stuermer01

19-06-2022, 13:37

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

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

megaloman

19-06-2022, 14:08

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

stuermer01

20-06-2022, 21:28

С годом как-то замудрёно, не лучше ли в какой-то общей ячейке прописывать год и уже оттуда брать его при преобразовании? » Тоже подойдет.
Т.е. вместо "(ГОД(СЕГОДНЯ())-2000)" подставить ячейку с годом?

stuermer01

22-06-2022, 17:31

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

megaloman

23-06-2022, 07:52

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

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

megaloman

24-06-2022, 09:10

Работает все правильно, спасибо! »Работает всё неправильно!цена, которая может быть и пятизначной »Раньше это не звучало.=ПСТР(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)