извлекать дату из текста и форматировать ее как дату

Question

Я искал и нашел несколько ответов, но ни один не соответствовал моему формату. Как получить 22 июля 2017 года 13:51 по Гринвичу до 22/07/2018? Мне нужно только извлечь дату в формате, который будет понятен Excel, чтобы я мог отсортировать ее от старого к новому. Спасибо!

Answer 1

Со строкой в ячейке A1 эта формула:

 =DATEVALUE(TRIM(LEFT(A1,11)))

даст дату часть. (это работает, если день состоит из одной цифры или двух цифр, месяц должен состоять из трех символов) Если бы у вас были данные с полными названиями месяцев, а не с аббревиатурами, мы получили бы дату, посмотрев на символ 3- ^й пробел:

=DATEVALUE(TRIM(LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),3)))))

Для VBA попробуйте следующую пользовательскую функцию (UDF):

Public Function DateMaker(s As String) As Date
    Dim arr
    arr = Split(s, " ")
    DateMaker = CDate(arr(0) & " " & arr(1) & " " & arr(2))
End Function

с правильным форматированием:

Пользовательские функции (UDF) очень просты в установке и использовании:

1. ALT-F11 открывает окно VBE
2. ALT-I ALT-M открывает новый модуль
3. вставьте материал и закройте окно VBE

Если вы сохраните книгу, UDF будет сохранен вместе с ней. Если вы используете версию Excel более поздней, чем в 2003 году, вы должны сохранить файл как .xlsm, а не .xlsx

Чтобы удалить UDF:

1. откройте окно VBE, как указано выше
2. очистить код
3. закройте окно VBE

Чтобы использовать UDF из Excel:

 =DateMaker(A1)

Чтобы узнать больше о макросах в целом, смотрите:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

а также

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

а подробности о UDF смотрите в:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Макросы должны быть включены, чтобы это работало!

Answer 2

Итак, мне удалось сделать это ... довольно вручную :). Я не использую Excel, поэтому извините мои методы.

Сначала я выбрал строки, которые содержали данные, все 236 из них. Данные были как 22 июля 2017 13:51 GMT. Итак, я пошел на вкладку DATA, конвертировать текст в столбцы. Я выбрал дату как DMY, но я не думаю, что это имело значение. Тогда у меня было 3 столбца даты: один для дня, один для месяца и один для года. Затем я должен был найти и заменить одну за другой (так как формула, которую я пробовал, не работала) Ян заменен на 1, Февраль заменен на 2 и т.д.

В конце у меня был формат даты, почти готово. Поэтому мне нужно было только объединить их 3 в одну ячейку с разделителем "/". Поэтому я использовал это = E2 & "/" & F2 & "/" & G2, где E2 - день, F2 - месяц, а G2 - год. После этого я только перетащил камеру до последней, и вот она у вас. Теперь мне, вероятно, нужно отформатировать ее как дату, и я могу ее отсортировать. Спасибо за все ваши ответы! Это не самый быстрый и не самый простой способ, но он сделал свою работу :)