将带有星期几缩写的文本日期转换为excel日期格式 [英] convert text date with day of week abbreviation to excel date format

问题描述

我试图弄清楚如何从单元格中删除星期几的缩写，然后将文本字符串日期转换为excel日期格式.这是格式始终不变的示例.

I am trying to figure out how to remove the abbreviation for the day of the week from cells and then convert the text string date to an excel date format. This is an example of the format which will always be the same.

2017年8月24日星期四

Thu 8/24/2017

如果我手动删除星期几的字母，然后在excel中使用文本转栏"功能，则可以很好地转换为日期格式.文字转栏功能不适用于包含日期缩写的单元格.

If I manually delete the day of week letters and then use the text to columns function in excel, it works fine converting into a date format. The text to columns function does not work on cells containing the day abbreviation.

我正在寻找最快的方法(VBA或公式)以从多列中删除这些缩写.查找和替换为"将是一个选项，但是我希望减少手动和时间的消耗，因为我将定期使用此电子表格.预先感谢您的帮助！

I am looking for the quickest method (VBA or formula) to remove these abbreviations from multiple columns. Find and replace with "" will be an option, but I am hoping for something less manual and time consuming, as I will be working with this spreadsheet regularly. Thank you in advance for your help!

推荐答案

使用:

=--MID(A1,FIND(" ",A1)+1,LEN(A1))

然后以任何需要的方式对其进行格式化

Then format it any way you want

这篇关于将带有星期几缩写的文本日期转换为excel日期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！