Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Перевод текстового формата времени в числовое значение
 
#1
11.09.2017 23:59:13
необходимо перевести текст типа :
Скрытый текст

везде разный текст и разные окончания, таких дат в книге сотнями..возможно хоть как нибудь автоматизировать формулу, чтоб она понимала каждую ячейку иниверсально и переводила ее либо в дни либо в часы, да хоть в секунды. к примеру 2 дня и 12 часов переводит в 2,5 дня.
Изменено: Alikadievmr - 12.09.2017 00:32:24
 
 
 
#2
12.09.2017 00:25:13
Прокрепите к первому сообщению пример в файле.
 
 
 
#3
12.09.2017 02:47:03
Alikadievmr, Приведите данные к единообразию, можно с помощью инструмента "Найти и выделить" >> "Заменить" (ctrl + H), разбейте текст по столбцам. ( Данные >>> Текст по столбцам), потом спокойно переводите все в любой вид.
 
 
 
#4
12.09.2017 23:14:12
Александр П., я бы с удовольствием, но книга сдержит огромное количество страниц, такой способ будет очень проблематичен
Изменено: Alikadievmr - 12.09.2017 23:14:33
 
 
 
#5
14.09.2017 23:34:41
нет других идей?
 
 
 
#6
15.09.2017 00:06:23
Есть идеи. Есть.
Макросом можно без всяких дополнительных преобразований. Писать неинтересно. ждите желающего.

Для формул подправить все равно надо. Выделить диапазон. Найти/Заменить:
"нед" на " нед"
"мес" на " мес"
и т.д. по всем началам слов. Это для отделения чисел от текста пробелом.

Далее добавим пробелы вначале и задвоим остальные (для корректного извлечения чисел):
=ПОДСТАВИТЬ(" "&СЖПРОБЕЛЫ(A4);" ";"  ")

Осталось определить каждую часть и умножить (разделить) на ее долю. Формула для всех одинакова:
ПСТР(B2&"0000";ПОИСК("текст";B2&"0000текст")-4;3)/доля
Вот она:
=ПСТР(B3&"0000";ПОИСК("год";B3&"0000год")-4;3)*365+ПСТР(B3&"0000";ПОИСК("мес";B3&"0000мес")-4;3)*30+ПСТР(B3&"0000";ПОИСК("ден";B3&"0000ден")-4;3)+ПСТР(B3&"0000";ПОИСК("час";B3&"0000час")-4;3)/24+ПСТР(B3&"0000";ПОИСК("мин";B3&"0000мин")-4;3)/1440+ПСТР(B3&"0000";ПОИСК("сек";B3&"0000сек")-4;3)/86400
Целая часть - дни, после запятой - время. Задать ячейкам нужный формат.

P.S. Недели упустил. И "год"-"лет".  Добавляйте сами, логика должна быть понятна
 
 
 
#7
15.09.2017 02:21:05
большое спасибо, буду пробовать! ))
 
 
 
#8
15.09.2017 09:54:33
Не учтены все возможные варианты. Предлагаю использовать следующую пользовательскую функцию.
Код
Function CDay(time As Variant)CDay = Mid(time & "0000", Application.WorksheetFunction.Search("лет", time & "0000лет") - 4, 3) * 365 + _
       Mid(time & "0000", Application.WorksheetFunction.Search("год", time & "0000год") - 4, 3) * 365 + _
        Mid(time & "0000", Application.WorksheetFunction.Search("мес", time & "0000мес") - 4, 3) * 30 + _
         Mid(time & "0000", Application.WorksheetFunction.Search("ден", time & "0000ден") - 4, 3) + _
         Mid(time & "0000", Application.WorksheetFunction.Search("дня", time & "0000дня") - 4, 3) + _
         Mid(time & "0000", Application.WorksheetFunction.Search("дне", time & "0000дне") - 4, 3) + _
          Mid(time & "0000", Application.WorksheetFunction.Search("нед", time & "0000нед") - 4, 3) * 7 + _
           Mid(time & "0000", Application.WorksheetFunction.Search("час", time & "0000час") - 4, 3) / 24 + _
            Mid(time & "0000", Application.WorksheetFunction.Search("мин", time & "0000мин") - 4, 3) / 1440 + _
             Mid(time & "0000", Application.WorksheetFunction.Search("сек", time & "0000сек") - 4, 3) / 86400
End Function

 
 
 
 
#9
15.09.2017 11:21:41
Еще такая функция:
Код
Function AmDays(r As Range) As Double
Dim aSpl
Dim dSum As Double, k As Double
Dim j As Long
    aSpl = Split(r.Value, " ")
    
    For j = 1 To UBound(aSpl)
        k = 0
        
        Select Case Left$(aSpl(j), 1)
        Case "г": k = 365
        Case "н": k = 7
        Case "д": k = 1
        Case "ч": k = 1 / 24
        Case "с": k = 1 / 86400
        Case "м"
            If Left$(aSpl(j), 2) = "ме" Then _
                        k = 30 Else: k = 1 / 1440
        End Select
        
        If k > 0 Then dSum = dSum + aSpl(j - 1) * k
    Next j
    
    AmDays = dSum
End Function

Разделением данных код не грузил. Данные предварительно обработать:
Цитата
Выделить диапазон. Найти/Заменить: "нед" на " нед", "мес" на " мес" и т.д. по всем началам слов. Это для отделения чисел от текста пробелом.
 
 
 
#10
16.09.2017 01:46:09
не знаю почему но в каждом варианте одна из ячеек не хочет работать. vikttur в вашем примере не работает 12 лет. а в первом примере я ни как не смог 485 недель заставить работать. Вариант с макросом супер! правда как его исправить?
Изменено: Alikadievmr - 16.09.2017 01:46:39
 
 
 
#11
16.09.2017 01:48:46
перезагрузил excel и макрос заработал)), а все же первый пример с 485 недель почему не работает?
 
 
 
#12
16.09.2017 10:45:00
Цитата
Alikadievmr написал: пример с 485 недель почему не работает?
Потому что в 4 символа не попадает старший разряд числа.
При наличии 3-значных нужно пробел заменять не на 2, а на 3 пробела и извлекать не 4, а 6 или 7 символов... Где-то так.
 
 
 
#13
24.12.2017 14:14:45
помогли с формулой VBA и очен признателен, вот прос еще, как я могу добавить в нее еще преобразователи

2к в 2000
2кк в 20000
2ккк в 200000
350м в 350000000
7,65м в 7650000
(комбинаций много)

пользуюсь вот этим примером VBA

Option Explicit

Function AmDays(r As Range) As Double
Dim aSpl
Dim dSum As Double, k As Double
Dim j As Long
   aSpl = Split(r.Value, " ")
   
   For j = 1 To UBound(aSpl)
       k = 0
       
       Select Case Left$(aSpl(j), 1)
       Case "г": k = 365
       Case "л": k = 365
       Case "н": k = 7
       Case "д": k = 1
       Case "ч": k = 1 / 24
       Case "с": k = 1 / 86400
       Case "м"
           If Left$(aSpl(j), 2) = "ме" Then _
                       k = 30 Else: k = 1 / 1440
       End Select
       
       If k > 0 Then dSum = dSum + aSpl(j - 1) * k
   Next j
   
   AmDays = dSum
End Function
Изменено: Alikadievmr - 24.12.2017 14:17:17
 
 
 
#14
24.12.2017 15:17:57
Вопрос не связан с темой, ведь это уже не о преобразовании времени. Создайте новую тему с новым примером и с названием, отражающим суть задачи. Можно добавить ссылку на эту тему.
 
 
 
Страницы: 1
Наверх