Преобразовать группу букв в дату в Excel

Question

Я пытаюсь преобразовать буквенный код даты в дату. Пример: SWITCHGEAR = 1,2,3,4,5,6,7,8,9 & 0 . Поэтому, когда я ввожу комбинацию букв « SIRISC », это будет читать 13/03/14 . Я перепробовал почти все, поэтому кто-нибудь может указать мне правильное направление.

Answer 1

Вот решение формулы массива. Для текстового кода в A1 введите в другую ячейку, вставив строку формул и нажав Ctrl+Shift+Enter:

=TEXT(SUM(MOD(FIND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"SWITCHGEAR"),10)*10^(LEN(A1)-ROW(INDIRECT("1:"&LEN(A1))))),"00"&CHAR(34)&"/"&CHAR(34)&"00"&CHAR(34)&"/"&CHAR(34)&"00")

Разбивая это:

FIND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"SWITCHGEAR") генерирует массив номеров индексов, которые совпадают с тем, где в SWITCHGEAR каждая буква кода в A1. Для вашего примера SIRISC генерирует массив {1,3,10,3,1,5} .

Оборачивая это выражение в MOD(...,10) , массив становится {1,3,0,3,1,5} .

Этот массив затем умножается на массив {10^5,10^4,10^3,10^2,10^1,10^0} чтобы сгенерировать массив {100000,30000,0,300,10,5} .

SUM затем используется, чтобы добавить числа в этом массиве, чтобы сформировать 130315 .

Затем TEXT используется для применения пользовательского формата 00"/"00"/"00 поэтому этот номер отображается как 13/03/15 .

Answer 2

Выберите ячейки, содержащие комбинации букв, и запустите этот небольшой макрос:

Option Base 1
Sub DateMaker()
    Dim r As Range
    a = Array("S", "W", "I", "T", "C", "H", "G", "E", "A", "R")
    b = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "0")
    For Each r In Selection
        t = r.Text
        For i = 1 To 10
            t = Replace(t, a(i), b(i))
        Next i
        r.Value = DateSerial(CInt("20" & Right(t, 2)), CInt(Mid(t, 3, 2)), CInt(Left(t, 2)))
        r.NumberFormat = "dd/mm/yy"
    Next r
End Sub

Итак, если вы начнете с:

Вы бы в конечном итоге с:

Answer 3

Если предположить, что:

1. Текст SIRISC находится в A1 .

2. В Sheet2 A1 вы должны поставить RSWITCHGEA

Затем попробуйте эту формулу на ячейке Sheet1 B1:

=CONCATENATE(FIND(MID(A1,1,1),Sheet2!A1,1)-1,FIND(MID(A1,2,1),Sheet2!A1,1)-1,"/",FIND(MID(A1,3,1),Sheet2!A1,1)-1,FIND(MID(A1,4,1),Sheet2!A1,1)-1,"/",FIND(MID(A1,5,1),Sheet2!A1,1)-1,FIND(MID(A1,6,1),Sheet2!A1,1)-1)