表格中如何将含星期的日期字符串转换为可识别的日期格式？

核心思路 #

• 原始数据列（如B列）保持不变，在相邻空白列（如C列）完成日期转换，实现原始内容与可筛选日期值共存
• 先移除日期字符串中的序数后缀（st/nd/rd/th），再通过Excel日期函数将处理后的字符串转为真正的日期值，最后设置目标格式

具体解决方案 #

方案1：支持正则表达式的Excel版本（365/2021及以后） #

使用REGEXREPLACE批量移除序数后缀，再通过DATEVALUE转换为日期：

=DATEVALUE(SUBSTITUTE(REGEXREPLACE(B822, "(\d+)(st|nd|rd|th)", "$1"), " ", ", ") & " 2022")

• 细节说明：
  1. REGEXREPLACE(...)：匹配数字+序数后缀的组合，只保留数字部分（例如把"30th"转为"30"）
  2. SUBSTITUTE(...)：将空格替换为, ，让DATEVALUE能正确识别日期结构
  3. 拼接年份2022后，DATEVALUE将字符串转为Excel可识别的日期值

方案2：不支持正则的旧版Excel #

用嵌套SUBSTITUTE逐一移除所有序数后缀：

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B822, "st", ""), "nd", ""), "rd", ""), "th", "") & " 2022")

• 细节说明：依次替换掉st/nd/rd/th后缀，拼接年份后通过DATEVALUE转换为日期值

后续格式设置 #

1. 选中转换后的单元格列，右键选择「设置单元格格式」
2. 在「数字」选项卡中选择「日期」，再挑选dd/mm/yyyy格式
3. 设置完成后，日期值会自动补全前置0（例如1号显示为01），且可直接用于未来7天的筛选查询

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