1.excel在文本的固定位置插入字符
如上图,现在想要将其转化为日期格式(比如2017/1/1),但是当设置单元格格式为日期时却显示出很多#。我们可以通过在20170101中添加两个斜杠“/”来将其转化为2017/1/1。可以用replace函数,在L1单元格里填入=replace(K1,5,0,"/")即可将20170101转化为2017/0101.
同理,可以在M1中填入=REPLACE(L1,8,0,"/")来使L1中的2017/0101变为2017/01/01。把M1中的值粘贴到其它单元格(粘贴值),比如下图中的N1,这是N1就是日期格式了,比如说可以更改它的格式为2017年1月1日。
说明:
replce()函数的功能:根据指定的字符数,REPLACE 将部分文本字符串替换为不同的文本字符串。
replace()函数的语法:REPLACE(old_text, start_num, num_chars, new_text)
参数:
old_text 必需。 要替换其部分字符的文本。
start_num 必需。 old_text 中要替换为 new_text 的字符位置。
num_chars 必需。 old_text 中希望 REPLACE 使用 new_text 来进行替换的字符数。
new_text 必需。 将替换 old_text 中字符的文本。
2.excel进行日期和时间的合并
如上图所示,J列是日期,K列是小时,怎样把他们两个合并到一个单元格中。可以在L1单元格中输入=K1&":00",
然后在M1单元格中输入=TEXT(J1,"yyyy/m/d")&" "&TEXT(L1,"h:mm"),结果如下图。
最后可以把M列中得到的数据粘贴到其他行(粘贴值),使其摆脱对公式中涉及的列的依赖。
以上两种方法感觉比较复杂,估计有其它更好的方法。
2023/11/29更新
上面的第一步,即把数字格式的20210101变成日期形式的2021/1/1,除了用上面的方法外,还可以用date函数。假如A2处是20210101,则可以通过DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))来将其变成日期格式的2021/1/1.
现在假设B2已经是时间格式的日期,如2021/1/1,而C2是数字形式的小时,如1、2、3、4,则可以通过
=CONCATENATE(TEXT(B2, "yyyy/m/d"), " ", C2, ":00:00")
或者
=TEXT(B2, "yyyy/m/d") & " " & C2 & ":00:00"
两种方式中的任意一种来得到最终需要的时间形式 2021/1/1 1:00:00
2023/12/1更新
使用上面的方法得到的只是2021/1/1 0:00:00的形式,实际上它并不是日期格式。可通过以下方法得到日期格式。
假设我们现在有两列数据,分别为列A和列B。列A是20210101形式的数字,它代表日期;列B是0,1,2,3……23形式的数字,它代表时间,即小时。现在我们想要把它们两个合并在一块变成2021/1/1 1:00形式的日期格式。步骤如下:
1. 将列A的20210101变成日期格式:
在C2处键入公式 =DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
2. 将列B的0,1……23变成小时格式:
在D2处键入公式 =TIME(B2,0,0)
3. 合并日期和时间:
在E2处键入公式 =C2+D2
4. 然后设置E2的单元格格式,使其变成我们需要的格式 2021/1/1 1:00