☰

Невозможно преобразовать текст в требуемый формат дд-мм-гггг в Excel

tag-icon • tag-icon microsoft-excel
Невозможно преобразовать текст в требуемый формат дд-мм-гггг в Excel

Возникла головная боль при попытке решить следующую проблему Excel: Я использую Excel 2003: импортирую тысячи данных из text.txt ниже в формате и пытаюсь разделить дд-мм-гггг

-> 6022015 (7 номеров)

->26012015 (8 цифр)

В Excel, «Инструменты данных»>«Текст в столбце»>«Фиксированная ширина»

-> 6!02!2015 (7 число)

->26!01!2015 (8 число)

Формат данных столбца, выберите Общий, затем закончить. Для 7 чисел успешно разбить их на 3 столбца. Но для 8 чисел возникает следующая ошибка. Ниже приведены результаты

->26012015 изменить на->2 60 12015 " Это должно быть 26 1 2015"

-> 6022015 изменить на->6 2 2015

Может ли кто-нибудь указать на ошибку и предложить решение?

решение1

Надеюсь, ваша дата в формате «ддммгггг», тогда вместо текста в столбце попробуйте найти и заменить."!"сничегозатем примените приведенную ниже формулу.

=IF(LEN(A2)=8,LEFT(A2,2)&"-"&MID(A2,3,2)&"-"&RIGHT(A2,4),"0"&LEFT(A2,1)&"-"&MID(A2,2,2)&"-"&RIGHT(A2,4))

Надеюсь, это то, чего вы ожидали :)

решение2

Предположим, что необработанные данные представлены в формате dmmyyyy(7 цифр) или ddmmyyyy(8 цифр).

  1. Просто скопируйте все данные в столбец A (или импортируйте данные в столбец A).

  2. Введите в ячейку B1 =DATE(RIGHT(A1,4),MID(A1,LEN(A1)-5,2),LEFT(A1,LEN(A1)-6))и скопируйте вниз.

  3. Отформатируйте столбец B как dd-mm-yyyy. Столбец B будет таким, как вам нужно.

решение3

Попробуй это:

=substitute(a1,"!","/")

и на всякий случай, если дата не возвращается, попробуйте добавить ноль:

=substitute(a1,"!","/")+0

И/или, если в ячейке есть пробелы (слева или справа от подробного текста), то обрежьте ячейку следующим образом:

=trim(substitute(a1,"!","/")+0)

решение4

Я бы лично использовал подход wilson, разорвав его на части и собрав снова. Однако, если вы хотите добиться успеха в Text to Columnsмастере, вам нужно иметь либо разделители, либо данные одинаковой длины.

Данные одинаковой длины легко получить. Просто используйте "вспомогательный столбец" рядом, или, ну, в любом удобном месте, и используйте формулу:

=IF(LEN(A1)=7,")"&A1,A1)

скопируйте вниз, насколько это необходимо, чтобы получить столбец из восьми значений символов. Как только у вас это получится, скопируйте столбец и Paste|Values|Specialполучите столбец значений, а не формул. Используйте Text to Columnsмастер для этого, фиксированной ширины, чтобы вы могли указать ему интерпретировать вещи как даты, один столбец (вы не хотите, чтобы он был разделен на части), и укажите ему DMYтип даты.

Это даст вам один столбец вывода, который теперь будет "реальными датами", и вы можете скопировать его, а затем вставить поверх исходных данных. Или по нему, или как угодно.

Главное — сделать так, чтобы все данные были длиной в восемь символов, чтобы их можно было обрабатывать одинаково: Excel не может определить, является ли семизначный элемент данных днем ​​из одной цифры, месяцем из двух цифр или наоборот, поэтому вам придется учитывать этот момент.

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

Кроме того, это работает для вас, ПОТОМУ ЧТО вы ЗНАЕТЕ, что последние четыре цифры — это год, нет двузначных лет, и что все месяцы — это двузначные значения, нет однозначных месяцев. Если вы не можете быть в этом уверены, вам понадобится более сложный подход.

Связанный контент