请教一个excel问题

夜游绯依 · 发表于 2023-7-12 10:48

本帖最后由夜游绯依于 2023-7-12 10:49 编辑

从网页系统上导出的记录表(csv,xls,xml格式都有)，但里面的日期改单元格格式也不会变，用公式提取也不行，用过乘1法也不行
导出来的格式是 12/31/2011 11:59:00 AM，
目的是只需要年月 2021/12
求高手指教下

绕指流光 · 发表于 2023-7-12 10:50

本帖最后由绕指流光于 2023-7-12 10:59 编辑

本质原因是导出的内容为文本，用上述的办法均取出的是文本，除非函数转换否则无法用常规格式变化
选中该列，数据--分列--按空格分列，将分列的第一列格式选择为日期或者常规。（分列操作会变换分出的列中所有单元格的value以适应单元格格式）
然后调整日期列格式为YYYY/MM

现在基本上数据库导出组件都是全文本输出的，速度快还不容易出错，就是往往要后手和用户自己解决字段格式问题

夜游绯依 · 发表于 2023-7-12 11:00

绕指流光发表于 2023-7-12 10:50
本质原因是导出的内容为文本，用上述的办法均取出的是文本，除非函数转换否则无法用常规格式变化
选中该列 ...

分列分了，可以分开，但是还是改不了格式，用公式也提不出年月 #VALUE!

绕指流光 · 发表于 2023-7-12 11:16

夜游绯依发表于 2023-7-12 11:00
分列分了，可以分开，但是还是改不了格式，用公式也提不出年月 #VALUE!

分列时有选择将第一列调整格式为日期吗？经过这一步操作后应该这一列都是日期格式了
如果还是不行，可以选中分列后的第一列，按以下步骤操作：
1.调整列格式为日期
2.再次选择分列，直接点完成（相当于对所有单元格按F2后退出）
3.自定义单元格格式为YYYY/MM

wangyi041228 · 发表于 2023-7-12 11:32

本帖最后由 wangyi041228 于 2023-7-12 11:34 编辑

2个思路：
DATEVALUE(TEXT)+单元格格式
改系统，导出时保留更多信息。
不明白大部分求助帖不提供样例文件（只需要有问题的脱敏部分），复现一下几秒就能定位问题，就喜欢干聊。

夜游绯依 · 发表于 2023-7-12 12:21

我把附件弄上来了，最好是单开一行用公式或者vba把这个数据改出来，原数据那列不动

睡魇 · 发表于 2023-7-12 12:40

按空格和/分列，再合并

蒜灵 · 发表于 2023-7-12 12:59

检查一下单元格前面是不是有隐藏字符，比如我们公司有一个软件导出数据前面都自带一个隐藏的制表符

Victorino · 发表于 2023-7-12 13:07

蒜灵发表于 2023-7-12 12:59
检查一下单元格前面是不是有隐藏字符，比如我们公司有一个软件导出数据前面都自带一个隐藏的制表符 ...

附议，之前试图分析建行email账单也是这样

糊状物 · 发表于 2023-7-12 13:20

就是excel默认不支持12/31/2011这种格式的文本自动转日期吧，偶然使用的话像楼上那样分列再合并就好了

—— 来自 Xiaomi MI 8, Android 10上的 S1Next-鹅版 v2.5.4

夜游绯依 · 发表于 2023-7-12 13:58

写了一堆find终于拼出来了……

nexus1 · 发表于 2023-7-12 14:00

要不试试pandas,让chatgpt帮你写

wangyi041228 · 发表于 2023-7-12 14:12

本帖最后由 wangyi041228 于 2023-7-12 14:13 编辑

方案1：修改windows短日期格式，重启Excel，用datevalue(text)，设置单元格日期格式。

方案2：find或类似函数定位拼接字符串。
方案3：修改系统导出表格的设置。
我说一句微软SB没人反对吧。

alvin051414 · 发表于 2023-7-12 14:20

本帖最后由 alvin051414 于 2023-7-12 14:26 编辑

你都分列了那按7#的方法搞呗…用空格和/一起分列，虽然拼接的方式其实用date函数更好一点

kara2000 · 发表于 2023-7-12 16:58

只提取年月份对日期的原始数据进行移位操作就行了

夜游绯依 · 发表于 2023-7-12 17:23

=RIGHT((LEFT(J105,FIND(" ",J105)-1)),4)&"-"&LEFT(J105,FIND("/",J105)-1)

左边要一直复制一坨原始数据进来，用分列搞的话后面弄起来太麻烦了，这样一步到位

jacketbird · 发表于 2023-7-13 13:26

提示: 作者被禁止或删除内容自动屏蔽

cxn · 发表于 2023-7-13 16:28

让相关开发人员改进一下sql语句吧

cxn · 发表于 2023-7-13 16:43

本帖最后由 cxn 于 2023-7-13 16:49 编辑

夜游绯依发表于 2023-7-12 12:21
我把附件弄上来了，最好是单开一行用公式或者vba把这个数据改出来，原数据那列不动 ...

试了一下，可以用power query转化，需要高版本excel，且操作稍微繁琐一点：
选中原有数据列->"数据"选项卡->"从表格"按钮->查询编辑器里不用修改，直接点"关闭并上载"->在新sheet里会自动生成查询结果，修改该列的单元格格式为yyyy/m，收工

蒜灵 · 发表于 2023-7-14 12:56

Excel自带一个函数清理字符串，好像是叫clean，你试试看

		自动登录	找回密码
密码			立即注册

jacketbird jacketbird 当前离线禁止发言精华 \| 战斗力鹅 \| 回帖 0 注册时间 2007-7-31 头像被屏蔽	发表于 2023-7-13 13:26 \| 显示全部楼层提示: 作者被禁止或删除内容自动屏蔽

	回复使用道具举报

[其他] 请教一个excel问题