ShowDocs
搜索文章Ctrl K

外观

← 返回Office列表
使用指南

Excel 文本清洗、日期与时间计算 ​

数据看起来整齐，不代表类型正确。本篇先在辅助列做转换，核对后再决定是否替换原列，避免把无法恢复的原始编号或日期覆盖掉。

图示跟练把脏文本和日期变成可分析数据

点击步骤切换图示，再到 Office 中跟做；每一步都可跳到下方详细说明。

Excel练习文件 · 界面示意
③ 复制操作 / 设置示意

源值保留 A 列

清洗结果放 B 列

详细输入与检查见本步正文 ↓
简化示意，非软件截图。以 Windows 中文界面为主，实际按钮位置与功能取决于版本；可在应用搜索框查找命令。

第 1 / 3 步：开始 → 复制

查看本步详细操作 →

清理空格与不可见字符 ​

假设 A2 是导入的部门名称，可先用 =TRIM(A2) 清除首尾普通空格，并压缩中间重复普通空格。但 TRIM 不负责清理所有 Unicode 空白，网页复制的数据可能含不间断空格。

text
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))

这能处理一些常见情况，不代表能清除所有异常字符。CLEAN 主要针对特定不可打印字符；中文全角空格等仍需明确替换。真实姓名、地址中的空格可能有意义，不能不加判断全部删除。

提取、连接与分列 ​

任务示例公式说明
取前三个字符=LEFT(A2,3)按字符位置提取
从第 4 位取 2 个字符=MID(A2,4,2)起点从 1 开始
连接部门和编号=B2&"-"&A2输出文字
数值 7 显示成三位文字=TEXT(A2,"000")结果是文本 007
替换指定文字=SUBSTITUTE(A2,"旧部门","新部门")注意是否只应修改完整字段

“数据 → 分列”适合把稳定分隔符的数据拆开。分列前确认右边列为空或设置输出位置，否则可能覆盖已有内容；在导入向导中把编号列设为文本。支持 TEXTSPLIT 等新函数的版本也可以用动态数组拆分，但共享给旧版用户前要检查兼容性。

日期是真实数值还是文字 ​

对日期单元格执行 =ISNUMBER(B2)，真实 Excel 日期通常返回 TRUE。把文字设置成“日期格式”不一定能转换为真实日期。若来源为严格 2026-10-15 格式，可明确拆分：

text
=DATE(VALUE(LEFT(A2,4)),VALUE(MID(A2,6,2)),VALUE(RIGHT(A2,2)))

此公式假设长度和位置都正确，而且 DATE 会对部分越界月份或天数做进位，并非严格日期校验器。导入后还应对比转换后的年月日与原始字段，非法值不能悄悄变成另一天。来源混用月/日/年和日/月/年时，优先在 Power Query 中指定区域设置。

月末、时间差与工作日 ​

text
=EOMONTH(DATE(2026,10,15),0)
=DATE(2026,11,1)-DATE(2026,10,1)
=NETWORKDAYS(DATE(2026,11,2),DATE(2026,11,6))

第一式结果格式化为日期后是 2026-10-31，第二式为 31 天，第三式在没有节假日参数时是 5 个工作日。NETWORKDAYS 默认周六、周日不算工作日，且计算区间包含符合条件的首尾日期；本地节假日与调休需要另外维护，不能认为它自动知道所有法定安排。

Excel 时间是一天的分数。开始时间 A2、结束时间 B2 都包含完整日期时，=(B2-A2)*24 得到小时数。只保存时分会丢失跨日信息；MOD(B2-A2,1) 只适合明确假设不超过一天的情况，不能用来掩盖负时间或多天记录。总时长显示可用 [h]:mm，避免超过 24 小时后显示回绕。

练习与验收 ​

清洗 " 运营 " 后应为“运营”；把文字 2026-10-15 转成日期后，用 YEAR、MONTH、DAY 分别得到 2026、10、15。最后计算从 2026-10-31 22:00 到 2026-11-01 02:30 的时间差，应为 4.5 小时。

把文本辅助列和数值辅助列标清用途，避免将 TEXT 的漂亮输出拿去做时间区间比较。

图示跟做：把脏文本和日期变成可分析数据 ​

按上方图示切换三个关键步骤；下面保留完整操作和检查结果，便于跟做与回查。请先使用练习副本。

第 1 步：先保留原始输入 ​

定位：开始 → 复制。

新建清洗练习表，在 A2 输入带前后普通空格的“ 运营 ”，B2 输入 =TRIM(A2)。用 LEN 比较前后长度，确认只是去除了多余普通空格。遇到不间断空格，可先用 SUBSTITUTE 替换 CHAR(160)，但不要把合法的产品名内部空格一概删除。

第 2 步：按来源约定转换日期 ​

定位：数据 → 分列。

把类似 2026-10-01 的文本日期复制到空白测试列，选择“数据 → 分列”，在最后一步按来源顺序选日期 YMD。确认目标区域不会覆盖右侧数据。随后用 ISNUMBER 检查结果，再设置显示格式；仅把数字格式改为日期，并不保证文本已转换。

第 3 步：用已知日期检查转换结果 ​

定位：公式 → 插入函数。

对已转换的日期输入 =EOMONTH(日期单元格,0)，结果应为 2026-10-31，必要时给结果设置日期格式。再用 YEAR、MONTH、DAY 分别检查年、月、日。对于 03/04/2026 一类有歧义的原文，先确认是日/月还是月/日，不要只凭系统自动识别。

没有得到预期结果时 ​

长编号发生精度损失后，把格式改成文本不能恢复丢失位数；应回到原始文本来源重新导入。日期和编号的转换要分列处理，不能一套规则清洗所有字段。

继续阅读