excel vba 如何将日期周几转换成文字_【EXCEL必知必会】函数[1]—文本清洗

阅读全文大概需要4.5分钟。

本文是专栏《Excel必知必会》的第五篇教程，如果想了解专栏内容规划，请参阅开篇。温馨提示：如果您已经特别熟悉Excel，大可不必再看这篇文章，或只挑选部分。文中对Excel的说明和操作基于Mac Excel2016和Windows Excel 365

正文导读

要想学好Excel，函数是必备技能，甚至是后面学习VBA的基础，只会VBA但是不懂Excel函数，常常会浪费精力实现函数本身的功能。

Excel内置函数几千个，实在太多了，我们只需要学习常用/通用的就好，这里我把要讲的函数分成了5类：

• 文本清洗
• 关联匹配
• 逻辑运算
• 计算统计
• 时间序列

每篇讲一类，这5类函数基本上覆盖了日常工作90%的场景，因为每个函数会有多种用法，大家其实只用记住基本使用方法就好，记不住了，查一查就好了。

本文的xmind极其有用，可充当函数快速查阅字典，建议下载收藏，传送门在文末。

进入主题之前，先分享下官方文档

官方教程：Excel 帮助和学习 - Microsoft 支持

公式和函数

包括文字和视频内容

关于公式和函数的区别：<1. 函数就是指Excel内置的函数，Max、Min、vLookup...；<2. 公式其实就是个字符串，以“=“开始，公式包括函数、运算符号、操作数

函数实在太多了，为了快速索引，官方，给了两种函数的排序：

• 字母排序：

Excel 函数（按字母顺序）​support.microsoft.com

• 类别排序：

Excel 函数（按类别列出）​support.microsoft.com

强烈建议添加到浏览器书签，随用随查！

正文开始

文本清洗类函数

关于函数的语法；1. “( )”内代表参数，各参数以逗号分隔；2. “[ ]”内参数为可选参数；

【1】链接字符：「&」

A1 = hello, B1 = World, C1= A1 & B1 = helloWorld「+」 不适用工作表中，但在VBA中也可以作为来链接字符使用

【2】转义字符：「""」

A1 = hello, B1 = """" & A1 = "hello

【3】字符串长度（字符个数）：「Len」

语法：Len(text)

A1 = hello, B1 =Len(A1) = 5

【4】移除首字母前以及尾字母后的空格：「Trim」

语法：Trim(text)

A1 = Trim(" Hello ") = hello；
只去除字符串s首尾的空格，单词间的空格无法消除；
VBA 中提供了LTRIM和RTRIM函数；

【5】 字符串截取函数：「Left」/「Right」/「Mid」

<1. LEFT

语法：Left(text,[num])；从字符串的第一个字符开始返回指定个数的字符，默认个数为1

A1 = LEFT("hello",3) = hel

<2. RIGHT

语法：Left(text,[num])；从字符串的最后一个字符开始返回指定个数的字符，默认个数为1

A1 = RIGHT("hello",3) = llo

<3. MID

语法：MID(text,startnum,num_chars)；从字符串中指定位置开始返回特定数目的字符

A1 = MID("hello",2,3) = ell

【6】 设置数字格式并将其转换为文本：「Text」

语法：TEXT(value,format_text)；将数字按照文本格式进行格式化

value：数字/数值；format_text：希望将数字格式化的文本形式；Text函数就相当于单元格格式化，区别就是单元格格式化不改变单元格内容，而TEXT函数则完全将数字转换成文本

主要包括：

• 日期和时间相关

A1 = Now() = 2020/12/14 21:55

Now()函数返回当前日期和时间

<1. 提取年月日

=TEXT(A1,"yyyy/mm/dd") = 2020/12/14；=TEXT(A1,"mm月dd日") = 12月14日

<2. 分别提取年/月/日

=TEXT(A1,"yyyy") = YEAR(A1) = 2020； =TEXT(A1,"yy") = 20；=TEXT(A1,"m") = MONTH(A1) = 12；=TEXT(A1,"mmm") = Dec；=TEXT(A1,"mmmm") = September；
=TEXT(A1,"d") = 14；=TEXT(A1,"ddd") = Mon；=TEXT(A1,"dddd") = Monday；

<3. 提取时间

=TEXT(A1,"hh:mm:ss") = 21:55:24；=TEXT(A1,"hh:mm") = 21:55；=TEXT(A1,"mm:ss") = 55:24

<4. 分别提取小时/分钟/秒/毫秒

=TEXT(A1,"hh") = HOUR(A1) = 21；=MINUTE(A1) = 55；关键字“m”与Month冲突；=TEXT(A1,"ss") = SECOND(A1) = 24；=TEXT(A1,"ss.000") = 34.570；

<5. 12小时制式

=TEXT(A1,"hh:mm:ss AM/PM") = 09:55:24 PM；=TEXT(A1,"hh:mm:ss 上午/下午") = 09:55:24 下午；

• 小数位和千分位

“#“代表有效填充，“0”代表固定填充，参考专栏文章大基本功[2]！日期/时间的本质就是数字，当然可以按照数字进行运算。

<1. 保留指定小数位

=TEXT(43.1,"0.00") = 43.10；=TEXT(43.1,"#.##") = 43.1；=TEXT(43.1,"000.00") = 043.10；=TEXT(43.1,"####.##") = 43.1；

<2. 使用千分位分隔符

=TEXT(9873421,"0,0.00") = 9,873,421.00；=TEXT(9873421,"#,#.0") = 9,873,421.0；

• 数值转换相关

<1. 货币

=TEXT(6543.1,"$#,#.00") = $6,543.10；=TEXT(6543.1,"¥#,#.00") = ¥6,543.10；

<2. 百分比

=TEXT(0.465,"%0.0") = %46.5；=TEXT(0.465,"0.0%") = 46.5%；

<3. 科学计数法

=TEXT(6543200,"0.00E+0") = 6.54E+6；=TEXT(-0.0023465,"0.00E+0") = -2.35E-3；

<4. 分段显示

=TEXT(13856784321,"0000-0000-0000") = 0138-5678-4321；

• 条件格式相关

<1. 数值判断

=TEXT(2,"正数;负数;零") = 正数；=TEXT(-2,"正数;负数;零") = 负数；

<2. 条件判断

=TEXT(86,"[>=85]优秀;[>=60]及格;不及格") = 优秀；=TEXT(65,"[>=85]优秀;[>=60]及格;不及格") = 及格；

【7】 文本组合函数：「Concat」/「TextJoin」

<1. CONCAT

语法：CONCAT(text1,[text2], ...) ；将列表或单元格对象/Range合并为一个字符串

=CONCAT(1,2,3,"hello") = 123hello
A1 = hello, B1 = world, C1= CONCAT(A1:B1) = helloworld

<2. TextJoin

语法：TEXTJOIN(delimitor, ignore_empty, text1, [text2], …)；将列表或单元格对象以指定分隔符形式合并成一个字符串；

当ignore_empty = TRUE, 空值忽略；ignore_empty = FALSE，空值保留，使用分隔符区分

A1=hello， B1=world， C1=""， D1=！， D1=TEXTJOIN(",",TRUE,A1:D1) = hello,world,!
A1=hello， B1=world， C1=""， D1=！， D1=TEXTJOIN(",",FALSE,A1:D1) = hello,world,,!

【8】 字符/字符串查找「Find」/「Search」

<1. Find

语法：Find(text, within_text, [start_num])；返回匹配字符串位置

text 代表查找对象；within_text 代表包含对象，被查找字符串； start_num 可选项， 指定开始查找字符串位置

• 区分大小写

=FIND("o", "hello WORLD") = 5；=FIND("O", "hello WORLD") = 8；=FIND("hell", "hello WORLD") =1；

• start_num 指定开始查找位置

=FIND("l", "hello WORLD") = 3；=FIND("l", "hello WORLD",4) = 4；

<2. Search

语法：Search(text, within_text, [start_num])；返回匹配字符串位置

text 代表查找对象；within_text 代表包含对象，被查找字符串； start_num 可选项， 跳过指定数量的字符后查找

• 不区分大小写

=SEARCH("o", "hello WORLD") = 5；=SEARCH("O", "hello WORLD") = 5；

• start_num 指定开始查找位置

=SEARCH("l", "hello WORLD") = 3；=SEARCH("l", "hello WORLD",4) = 4；

• 支持通配符

*：代替任意[>=0]个字符；=SEARCH("f*c", "Hello OFFICE") = 8?：代替1个字符；=SEARCH("f?c", "Hello OFFICE") = 9~：代表转义字符；=SEARCH("*", "H*llo OFFICE") =1；=SEARCH("~*", "H*llo OFFICE")=2

【9】 字符/字符串替换「Replace」/「Substitute」

<1. Replace

语法：Replace(old_text, start_num, num_chars, new_text)

将原字符串中指定位置，指定长度的字符替换为新字符

A1= '340621190002318987；B1=REPLCAE(A1, 7, 8, "****") = 340621****8987

<2. Substitute

语法：Replace(old_text, start_num, num_chars, new_text,[instance_num])

将原字符串中指定字符串替换为新字符串，并可以指定替换次数

• 区分大小写

=SUBSTITUTE("hello OFFICE", "o", "*") = hell* OFFICE=SUBSTITUTE("hello OFFICE", "O", "*") = hello *FFICE

• instance_num 指定替换次数

=SUBSTITUTE("hello office", "o", "*") = hell* *ffice=SUBSTITUTE("hello office", "o", "*", 1) = hell* office

正文结束

---

传送门：链接: https://pan.baidu.com/s/143RxXuGLzxlda_64bbXAeg 提取码: g3qu

Xmind 建议以2020版打开，体验更佳！

最后，每一次点赞，收藏都是对创作最大的鼓励。