如何让Excel正确识别并存储粘贴的"5:00p"格式时间值？

以下是几种实用的解决方法，既能让Excel正确识别并存储"5:00p"这类格式为可计算的时间值，又能保持该格式的显示效果：

方法一：文本分列快速转换已粘贴的文本 #

1. 选中所有包含"5:00p"这类文本的单元格区域
2. 切换到「数据」选项卡，点击「文本分列」
3. 第一步选择「分隔符号」，点击下一步
4. 第二步取消所有分隔符的勾选，点击下一步
5. 第三步在「列数据格式」中选择「时间」，下拉菜单里选能匹配12小时制的选项（如MDY），点击完成
6. 最后给单元格应用自定义格式：输入h:mm\p\m;h:mm\a\m，即可让上午时间显示为"x:xxa"、下午显示为"x:xxp"

方法二：公式批量转换文本为时间值 #

如果需要批量转换，可在空白单元格输入公式，下拉填充后再转为值：

• 假设目标文本在A1单元格，输入公式：

  =TIMEVALUE(SUBSTITUTE(A1,"p"," PM"))
  

  （如果有上午的"x:xxa"格式，可嵌套IF判断：=TIMEVALUE(IF(RIGHT(A1,1)="p",SUBSTITUTE(A1,"p"," PM"),SUBSTITUTE(A1,"a"," AM")))）
• 填充公式后，选中结果区域，右键选择「复制」，再右键选择「粘贴选项-值」
• 给转换后的单元格设置自定义格式h:mm\p\m;h:mm\a\m即可

方法三：修改系统区域设置让Excel自动识别该格式 #

1. 打开Windows控制面板，找到「区域」设置
2. 在「格式」选项卡点击「其他设置」，切换到「时间」选项卡
3. 在「时间格式」列表中添加或修改格式为h:mmtt，并将AM/PM符号改为小写的a/p
4. 确定后，Excel就能自动识别输入或粘贴的"5:00p"为时间值，再给单元格应用自定义格式即可保持显示效果

方法四：VBA宏一键转换 #

如果经常需要处理这类格式，可创建宏实现一键转换：

1. 按下Alt+F11打开VBA编辑器，插入新模块
2. 粘贴以下代码：

Sub ConvertToTime()
    Dim cell As Range
    For Each cell In Selection
        If cell.Value Like "*[ap]" Then
            Dim timeStr As String
            timeStr = Left(cell.Value, Len(cell.Value) - 1) & " " & UCase(Right(cell.Value, 1)) & "M"
            cell.Value = TimeValue(timeStr)
            cell.NumberFormat = "h:mm\p\m;h:mm\a\m"
        End If
    Next cell
End Sub

3. 保存宏，回到Excel后将该宏添加到快速访问工具栏，选中目标区域点击即可完成转换

内容的提问来源于stack exchange，提问作者LaserBoss