Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
преобразовать текст в число (при помощи формулы)
 
#1
09.10.2009 12:40:40
Привет всем!  
 
Элементарный вопрос, но я уже второй день не могу его решить :)  
 
Задача: в ячейке A1 находится текст 4.9 GHz  
(возможен, но маловероятен, вариант с запятой: 4,9 GHz)  
в соседней ячейке при помощи формулы надо получить число 4,9  
 
Какую формулу написать?  
Уже десяток вариантов перепробовал - ничего не выходит...  
 
Пробовал и такую формулу: =ЗНАЧЕН(ЛЕВСИМВ(ПОДСТАВИТЬ(A1;",";".");ПОИСК(" ";A1)))  
но она работает некорректно (Проверял в Excel 2003 и 2007):  
на предпоследнем шаге вычислений мы получаем =ЗНАЧЕНИЕ("5.2 ")  
а потом при вставке этого значения в ячейку оно автоматически преобразовывается в дату (её числовое значение)  
 
Есть ли возможность использовать какую-то совсем простую формулу типа =Ч(A1)?  
 
 
Вот пример файла:  http://excelvba.ru/XL_Files/Sample__08-10-2009__15-49-21.zip  
 
Дело в том, что я делаю прайс, который должен работать во всех версиях и локализациях Excel и с разными десятичными разделителями в настройках Excel...  
 
Макросом я сделал бы это за секунду, но тут нужна именно формула.
 
 
 
#2
09.10.2009 13:00:00
Я в свободную ячейку (пусть А1) вводил текст "1,2", затем значен(А1) проверял на ошибку, в зависимости от результата разная формула подставить() - то ли точку на запятую то ли запятую на точку.  
т.е. нужно до преобразования определить десятичный разделитель.  
Сейчас еще формулу придумал, чуть короче =ЕЧИСЛО(A1+0) при разделителе запятая дает истину, при точке ложь.
Bite my shiny metal ass!      
 
 
 
#3
09.10.2009 13:06:04
{quote}{login=EducatedFool}{date=09.10.2009 12:40}{thema=преобразовать текст в число (при помощи формулы)}{post}  
Задача: в ячейке A1 находится текст 4.9 GHz  
(возможен, но маловероятен, вариант с запятой: 4,9 GHz)  
в соседней ячейке при помощи формулы надо получить число 4,9... {/post}{/quote}  
Вариант,если правильно понял: "=ПСТР(B2;1;3)", затем число, 1 знак после... (XL-2003 SP3).
 
 
 
#4
09.10.2009 13:10:12
Вариант_2: "=ПСТР(B2;1;1)&","&ПСТР(B2;3;1)"...
 
 
 
#5
09.10.2009 13:11:22
{quote}{login=Лузер™}{date=09.10.2009 01:00}{thema=}{post}  
т.е. нужно до преобразования определить десятичный разделитель.  
{/post}{/quote}  
 
Тоже вариант, но формула получается длинная...  
 
По сути, задача-то простейшая. Неужели нет универсального варианта?  
Изначально, как мне кажется, лучше заменить в тексте точку на запятую (или наоборот). А вот что делать дальше?  
Файл будет распространяться за рубежом по многим странам.  
Какие там параметры у Excel, я даже предположить не могу.  
 
В данном конкретном случае я, конечно, могу в результат взять первый & третий символы строки - результат будет достигнут.  
Просто теперь интересует принципиальная возможность формулами получить требуемое число.  
 
Ладно, пока все числа имеют вид #.# Ghz  
А если попадутся строки вида # Ghz или #.#№ Ghz...
 
 
 
#6
09.10.2009 13:18:45
Еще можно наоборот  
Ввести число 1,2 и получать разделитель формулой ПСТР(ТЕКСТ(A1;"@");2;1)  
Ваша формула:  
=ЗНАЧЕН(ЛЕВСИМВ(ПОДСТАВИТЬ(A1;".";ПСТР(ТЕКСТ(A4;"@");2;1));ПОИСК(" ";A1)))  
в А1 4.9 GHz (с точкой)  
в А4 число 1,2  
 
И надо бы определиться "4.9 GHz" или "4,9 GHz"? Если может быть по разному, то с помощью ПСТР() пытаться вытащить символ разделителя. Вдруг он всегда второй?
Bite my shiny metal ass!      
 
 
 
#7
09.10.2009 13:21:48
ЛЕВСИМВ(ПОДСТАВИТЬ(A1;".";",");ПОИСК(" G";A1))+0
Живи и дай жить..
 
 
 
#8
09.10.2009 13:24:04
Сработал такой вариант:  
=ЗНАЧЕН(ПОДСТАВИТЬ(ПСТР(A1;1;ПОИСК(" ";A1)-1);".";","))  
 
И вариант от Слэна тоже работает отлично:  
=ЛЕВСИМВ(ПОДСТАВИТЬ(A1;".";",");ПОИСК(" G";A1))+0  
 
 
Не подскажете, на компах с другим десятичным разделителем проблем не возникнет?
 
 
 
#9
09.10.2009 13:28:19
может поможет)
 
 
 
#10
09.10.2009 13:30:43
{quote}{login=Лузер™}{date=09.10.2009 01:18}{thema=}{post}  
И надо бы определиться "4.9 GHz" или "4,9 GHz"? Если может быть по разному, то с помощью ПСТР() пытаться вытащить символ разделителя. Вдруг он всегда второй?{/post}{/quote}  
Определиться не получится - файл будут заполнять менеджеры, которые могут написать что угодно.  
 
Разделителя может не быть вообще - может быть написано 6 Ghz или 12 Ghz
 
 
 
#11
09.10.2009 13:33:41
{quote}{login=Dophin}{date=09.10.2009 01:28}{thema=}{post}может поможет){/post}{/quote}  
 
Тоже неплохой вариант, но с разделителем-запятой в исходных данных не работает  
И не понимает целые числа...
 
 
 
#12
09.10.2009 13:35:01
поменяйте разделитель..  
 
очевидно будет..  
 
навскидку :  
 
ЕСЛИ(ЕОШ(ЛЕВСИМВ(ПОДСТАВИТЬ(A1;".";",");ПОИСК(" G";A1))+0);ЛЕВСИМВ(ПОДСТАВИТЬ(A1;",";".");ПОИСК(" G";A1))+0;ЛЕВСИМВ(ПОДСТАВИТЬ(A1;".";",");ПОИСК(" G";A1))+0)
Живи и дай жить..
 
 
 
#13
09.10.2009 13:40:17
{quote}{login=слэн}{date=09.10.2009 01:35}{thema=}{post}навскидку :  
 
ЕСЛИ(ЕОШ(ЛЕВСИМВ(ПОДСТАВИТЬ(A1;".";",");ПОИСК(" G";A1))+0);ЛЕВСИМВ(ПОДСТАВИТЬ(A1;",";".");ПОИСК(" G";A1))+0;ЛЕВСИМВ(ПОДСТАВИТЬ(A1;".";",");ПОИСК(" G";A1))+0){/post}{/quote}  
 
Спасибо огромное!  
Теперь именно то, что нужно - работает при обоих разделителях.  
 
PS: Надеюсь, в других странах не используются другие десятичные разделители (кроме точки и запятой)...
 
 
 
#14
09.10.2009 13:51:54
{quote}{login=EducatedFool}{date=09.10.2009 01:40}{thema=Re: }{post}  
 
PS: Надеюсь, в других странах не используются другие десятичные разделители (кроме точки и запятой)...{/post}{/quote}Я бы все-таки определял разделитель ...  
Можно даже именем.
Bite my shiny metal ass!      
 
 
 
#15
09.10.2009 13:56:49
Попробуйте во всех предложенных вариантах поставить разделителем, например, тильду "~"  
для смеха  
у меня будет работать и с тильдой
Bite my shiny metal ass!      
 
 
 
#16
09.10.2009 14:40:46
да, хорошо!  
 
только в первый раз формулу надо на правильном компе написать(с правильным разделителем я имею ввиду)  
 
можно для совсем уж инвариантности чуть поправить: ТЕКСТ(1/10;"@")  
 
еще придумал формулу:  
ЛЕВСИМВ(ПОДСТАВИТЬ(A1;СИМВОЛ(45-(1-2*ЕОШ(".1"+0)));СИМВОЛ(45+(1-2*ЕОШ(".1"+0))));ПОИСК(" G";A1))+0
Живи и дай жить..
 
 
 
#17
09.10.2009 15:19:15
да, вариант с 1/10 совсем хорош. 1/2 для краткости.  
и символ(45±1) - зачет!
Bite my shiny metal ass!      
 
 
 
#18
10.10.2009 02:01:07
Первое, что напросилось на форум:  
=1*ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПСТР(A1;1;ПОИСК(" G";A1));",";ПСТР(1/2;2;1));".";ПСТР(1/2;2;1))
 
 
 
#19
10.10.2009 02:25:16
или еще короче:  
 
=ПРОСМОТР(9E+307;--ПОДСТАВИТЬ(ЛЕВСИМВ(A1;НАЙТИ(" ";A1)-1);{",";"."};{".";","}))
KL
 
 
 
#20
10.10.2009 03:05:10
{quote}{login=KL}{date=10.10.2009 02:25}{thema=}{post}или еще короче:  
 
=ПРОСМОТР(9E+307;--ПОДСТАВИТЬ(ЛЕВСИМВ(A1;НАЙТИ(" ";A1)-1);{",";"."};{".";","})){/post}{/quote}  
А если в качестве разделителя не точка и не запятая? :-)  
Экзотика, конечно, но приходилось сталкиваться с использованием тире или знака равно, а теоретически ведь могут быть и другие символы, если в Сервис - Параметры - Международные использован не системный разделитель.  
 
Кстати, Кирилл, с учетом Ваших лингвистических знаний - не знакомы ли Вам отличные от точки и запятой разделители десятичных разрядов, общепринятые для каких-нибудь языков или стран?
 
 
 
#21
10.10.2009 03:18:44
{quote}{login=ZVI}{date=10.10.2009 03:05}{thema=Re: }{post}А если в качестве разделителя не точка и не запятая? :-)  
Экзотика, конечно, но приходилось сталкиваться с использованием тире или знака равно, а теоретически ведь могут быть и другие символы, если в Сервис - Параметры - Международные использован не системный разделитель.  
 
Кстати, Кирилл, с учетом Ваших лингвистических знаний - не знакомы ли Вам отличные от точки и запятой разделители десятичных разрядов, общепринятые для каких-нибудь языков или стран?{/post}{/quote}  
Если честно, то лично я ничего подобного не встречал. Насколько мне известно, даже в японском Excel десятичный разделитель - точка. Теоретически существуют другие разделители и цифры (в том же японском или арабском), но для них в Excel нет локали. И хотя конечно же возможно, что пользователь поставил другой символ, я бы наверное пренебрег такой вероятностью в общем случае :-)
KL
 
 
 
#22
10.10.2009 03:50:01
Спасибо за инфо, Кирилл. О маловероятности экзотики замечание справедливое.  
Меня смущало, что в датах того же японского в качестве разделителя могут использоваться и иероглифы: Формат ячейки – Дата – Язык: японский или корейский  или китайский – список форматов в листбоксе Тип. И хотя для чисел там нет выбора языковых вариантов, но в то же время ограничений на использование несистемного разделителя почему-то нет. Этой свободой иногда пользуются для выходных (распечатываемых) форм бухгалтерских или финансовых документов, программно устанавливая иной разделитель. Но у Игоря, конечно же, не такой случай :-)
 
 
 
#23
10.10.2009 13:14:45
Если числа имеют вид #.# Ghz то достаточно:  
=--ПОДСТАВИТЬ(ПОДСТАВИТЬ(A1;"Ghz";"");".";",")  
Может просто формат ячеек для ввода сделать:  
Основной "Ghz"
 
 
 
#24
10.10.2009 13:34:52
и по новой..   :)))
Живи и дай жить..
 
 
 
Страницы: 1
Наверх