7

Как я могу получить отображаемое значение ячейки в MS Excel (для текста, который был преобразован в даты)?

Происхождение файла Excel

У меня есть файл Excel, который изначально был файлом CSV. В настоящее время у меня нет доступа к исходному файлу CSV. Когда файл CSV был первоначально открыт в Excel, многие ячейки в столбце Date были автоматически преобразованы.

Пример значения

Вот пример некоторых элементов из столбца Date (интервал представляет выровненные по левому и правому краю ячейки, в основном, чтобы показать, что вторая дата не была преобразована):

экспонат 1:

        3/1/71
12/01/1882
Fall 1988
          1993

И если я выберу одну из этих ячеек, щелкну правой кнопкой мыши и выберу форматирование, я получу разные вещи:

m/d/yy
General
General
General

Возможная логика

Похоже, что Excel не конвертировала дату 12/01/1882 - возможно, потому что она "слишком старая" или может быть по другой причине.

Если я выберу весь столбец и изменим его на "Общее", то получу следующее (текст выровнен по левому краю, номера выровнены по правому краю):

       25993
12/01/1882
Fall 1988
        1993

Что я хочу

Мне бы хотелось, чтобы в одном столбце был установлен формат "Общие" или "Текст", и в нем были бы даты, которые были показаны на выставке 1 выше.

Я видел этот вопрос об использовании класса DataFormatter (выглядит как Java?) и некоторые аналогичные вопросы, в которых используются скрипты для макросов и VB - но, если это вообще возможно, мне бы очень хотелось, чтобы для этого использовалась функция Excel.

Что я пробовал

Я пытался использовать эти вещи, которые не работали:

CONCATENATE - получить поле даты и поставить перед ним пробел
INDIRECT(ADDRESS(ROW(),5)) - получить значение косвенно
VALUE() - получить "значение" ячейки - напротив того, что я хочу
TEXT() - может явно указать формат, но все равно не может заставить его работать
CELL() - может использоваться для получения информации о ячейке, такой как "содержимое" - не повезло с этим, но я действительно видел, что могу использовать ее также для получения format который сообщит мне, отображается ли ячейка как число или как "общая" msgstr "который затем может быть использован с функцией IF для преобразования числа, отображаемого в качестве даты, в фактическую дату ... возможно?

Копировать Вставить метод

Один из методов, который мне показался подходящим, состоит в том, чтобы выбрать значения, скопировать и вставить их в блокнот, а затем скопировать и вставить их обратно в Excel (это эффективно дает отображаемое значение - то, что я хочу)

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

|источник

3 ответа3

4

Общий ответ:

Вместо того, чтобы пытаться возиться с результатами функции CELL("формат" ...), может быть проще использовать пользовательскую функцию для прямого возврата свойства NumberFormat ячейки.

В этом случае выражение "= TEXT(A1, NumberFormat(A1))" даст вам отображаемое значение довольно напрямую.

Чтобы включить это, вам необходимо следующее в модуле рабочего листа:

  Public Function NumberFormat(CellRange As Range) As String

  NumberFormat = CellRange.NumberFormat

  End Function
|источник
3

Если вы показали нам полный спектр возможных форматов, это должно работать:

=TEXT(A1, IF(CELL("format",A1)="D4", "m/d/yy", "General"))

Если у вас есть форматы даты, отличные от m/d/yy, добавьте тесты для них.

|источник
2

Тот же ответ, но с другой функцией (это сработало для меня): 

Public Function DisplayText(ByVal pRange As Range) As String  
  DisplayText = pRange.Text  
End Function

Просто используйте = DisplayText(A1). Если вы измените формат ячейки, эта функция вернет отображаемый текст

|источник

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .