英文:
Excel: Formatting text into a recognisable date
问题
我有一个包含许多行的.csv文件，其中日期的格式如您在A列中所见。我手动复制了日期，然后在B列中将其更改为我想要的格式，同时Excel也将其识别为日期（而不是文本）。
我的问题是，有没有人知道如何通过一些公式魔法或其他方法让Excel自动以这种方式格式化日期？
谢谢！
英文:
I have a .csv file with many rows where the date is formatted as you see in column A. I manually copied and then changed the date to how I want it to be in column B where it's also recognised by Excel as a date (and not text).
My question is does anyone know how I can get Excel to format the date like this automatically via some forumula magic or some other method?
Thanks
答案1
得分: 4
这是公式的翻译部分：
-
第一个公式是针对Office 365的，它会返回正确的日期。这里会去掉逗号并重新排列顺序。
--将其转换为数字。 -
如果月份缩写与本地设置不匹配，它将无法正常工作。
-
对于较旧的版本，使用下面的公式：
=--(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,""", ""), " ", REPT(" ", 999)), 999, 999)) & " " & TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1, """, ""), " ", REPT(" ", 999)), 1, 999)) & " " & TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1, """, ""), " ", REPT(" ", 999)), 1998, 999)) & " " & TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1, """, ""), " ", REPT(" ", 999)), 3996, 999)))
希望这有所帮助。
英文:
Another formula that should return the correct date. This does require Office 365:
=--TEXTJOIN(" ",,INDEX(TEXTSPLIT(SUBSTITUTE(TRIM(A1),",","")," "),,{2,1,3,5}))
This removes the , and then rearranges the order. The -- turns it into a number.
If the month abbreviations do not match the local settings it will not work
For older versions:
=--(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,",","")," ",REPT(" ",999)),999,999)) & " " &
TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,",","")," ",REPT(" ",999)),1,999)) & " " &
TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,",","")," ",REPT(" ",999)),1998,999)) & " " &
TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,",","")," ",REPT(" ",999)),3996,999)))
答案2
得分: 0
=--SUBSTITUTE(REPLACE(A2,1,8,MID(A2,5,2)&"/"&LEFT(A2,3)&"/"), " @ ", " ")
英文:
Shorter formula for older versions:
=--SUBSTITUTE(REPLACE(A2,1,8,MID(A2,5,2)&"/"&LEFT(A2,3)&"/")," @ "," ")
(English-locale)
=--SUBSTITUTE(REPLACE(A2,1,8,MID(A2,5,2)&"/"&FIND(LEFT(A2,3)," JanFebMarAprMayJunJulAugSepOctNovDec")/3&"/")," @ "," ")
(non-English locale)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论