[Excel]常用函数—数据清洗及转换

TRIM

1. 去掉单元格内容前后的空格

   trim1.png

2. 输入长度超过12的数字时，excel会将格式默认变为科学计数法，用trim可以显示文本格式的数字

   trim2.png

CONCATENATE

字符串连接，可以连接单元格，也可以连接字符串

trim3.png

REPLACE

=Replace(old_text,start_num,num_chars，new_text)

old_text：要替换的字符串

start_num：开始位置

num_chars：替换字符串数量

new_text：新的字符串，为""时即为把替换区域的字符删除

<e.g-1>

trim4.png

=REPLACE(A3,6,3,"***")

A3：替换对象位置为A3

6：从A3中第六个字符开始替换

3：替换三个字符

"***"：替换为“*******”

<e.g-2> 替换的新字符串长度可以与替换长度不相符，即替换位置中的字符被替换为新字符串

trim5.png

SUBSTITUTE

=substitute(text,old_text,new_text,[instance_num])

text：需要替换的文本

old_text：旧文本

new_text：新文本

[instance_num]：指定替换第几次出现的old_text

<e.g-1> 将A4中的3857替换为"****"

trim6.png

<e.g-2> 将A5中第二个ab替换为yz

trim7.png

<e.g-3> 将A6中所有个ab都替换为yz

trim8.png

LEFT／RIGHT／MID

Left/Right：从目标左侧/右侧开始截取字符

Mid：从目标指定位置开始截取字符

<e.g-1> 从左开始截取A7中字符串的前2位

trim9.png

<e.g-2> 从右开始截取A8中字符串的前4位

trim10.png

<e.g-3> 从左侧开始截取某字符串中的几位

trim11.png

<e.g-4> 从目标指定位置截取

trim12.png

LEN/LENB

返回字符串的长度

Len：中文算作1个字符

Lenb：中文算作2个字符

FIND

在指定字符串中查找字符，返回字符第一次出现的位置，可以指定从第几个字符开始查找

=FIND(find_text,within_text,start_num)

find_text：要查找的字符

within_text：查找范围字符串

start_num：从within_text中的第几个字符开始查找

<e.g-1> 在A10的字符串中查找"3"出现的位置，从第一个字符开始查找

trim13.png

<e.g-2> 在A10的字符串中查找"3"出现的位置，从第4个字符开始查找

trim14.png

SEARCH

=SEARCH(find_text,within_text,[start_num])

由公式可看出与find函数相似，search函数不区分大小写，可支持通配符*

TEXT

=text(value,format_text)

value：要转换的数字

format_text：要转换成的格式

<e.g> 将A11中的日期转换为当天的星期，"dddd"为星期几的省略模式

trim15.png

Reference：https://zhuanlan.zhihu.com/p/23345231