☰

如何获取 MS Excel 中单元格的显示值（对于转换为日期的文本）？

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function date-time
如何获取 MS Excel 中单元格的显示值（对于转换为日期的文本）？

如何获取 MS Excel 中单元格的显示值（对于转换为日期的文本）？

Excel 文件来源

我有一个 Excel 文件，它原本是一个 CSV 文件。目前我无法访问原始 CSV 文件。当最初在 Excel 中打开 CSV 文件时，列中的许多单元格Date都会自动转换。

示例值

以下是该列中部分项目的示例Date（间距表示左对齐和右对齐的单元格，主要是为了表明第二个日期未转换）：

图表 1：

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

如果我选择其中一个单元格并右键单击并选择格式，我将得到不同的东西：

m/d/yy
General
General
General

可能的逻辑

因此看起来 Excel 尚未转换 1882 年 12 月 1 日这个日期 - 可能是因为它“太旧”或者可能是因为其他原因。

如果我选择整列并将其更改为“常规”格式，则会给出以下内容（文本左对齐，数字右对齐）：

       25993
12/01/1882
Fall 1988
        1993

我想要的是

我想要有一列，其中格式设置为“常规”或“文本”，并且它将显示以下日期：展览1多于。

我看见这个问题关于使用 DataFormatter 类（看起来像 Java？）以及一些使用宏和 VB 脚本的类似问题 - 但如果可能的话，我真的很想使用 excel 函数来做到这一点。

我尝试过的方法

我试过用这些东西，但没有效果：

CONCATENATE- 获取日期字段，并在其前面添加空格
INDIRECT(ADDRESS(ROW(),5))- 间接获取值
VALUE()- 获取单元格的“值” - 与我想要的相反
TEXT()- 可以明确指定格式，但仍然无法使其工作
CELL()- 可用于获取单元格信息，如“内容” - 没有运气，但我确实看到我也可以用它来获取format它会告诉我单元格是否显示为数字或“常规”，然后可能与IF函数一起使用以某种方式将显示为日期的数字转换回实际日期......可能吗？

复制粘贴法

我看到的一种似乎有效的方法是选择值，将它们复制并粘贴到记事本中，然后将它们复制并粘贴回 excel 中（它有效地给出了显示的值 - 这正是我想要的）

唯一的问题是我希望有一个函数来执行此操作，因为 Excel 表中包含几十万行。

答案1

如果你向我们展示了所有可能的格式，那么这应该可行：

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

如果您有除 m/d/yy 之外的日期格式，请为它们添加测试。

答案2

一般答案：

与其尝试处理 CELL("format"...) 函数的结果，不如使用用户定义函数直接返回单元格的 NumberFormat 属性更容易。

在这种情况下，表达式“=TEXT(A1,NumberFormat(A1))”会直接给您显示的值。

为了实现此功能，您需要在工作表的模块中添加以下内容：

  Public Function NumberFormat(CellRange As Range) As String

  NumberFormat = CellRange.NumberFormat

  End Function

答案3

答案相同，但功能不同（对我有用）：

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

只需使用 =DisplayText(A1)。如果您更改单元格格式，此函数将返回显示的文本

答案4

这对我有用。它与上面的帖子相同，但包含错误检查：

步骤 1：将以下公共函数复制并粘贴到 VBA 模块中：

Public Function CELLTEXT(target As Range) As String
   If target.Cells.Count > 1 Then
    MsgBox "CELLTEXT function requires a single cell as an input, please try again"
     Exit Function
    Else
        CELLTEXT = target.Text
    End If
End Function

第 2 步：在电子表格中尝试。在任意单元格中输入公式，例如：=CELLTEXT(A1)。然后这将输出 A1 显示的内容。

相关内容