在业务数据收集过程中，由于信息来源于多个渠道，这些数据往往不能立即用于计算分析或可视化展示。为了确保数据的准确性和可用性，通常需要借助BI（商业智能）工具对数据进行预处理。
今天，小九将向大家介绍一些基本的文本数据清洗函数。这些函数旨在识别和纠正数据集中的缺陷，包括错误数据、缺失值、重复记录以及异常值。通过这些数据清理步骤，我们能够提高数据的整体质量，确保数据的可靠性，并增强其后续使用的价值。
一、文本数据清洗——英文大小写
在处理数据时，用户常常需要将英文字母进行大小写转换。在九数云中，有三个函数处理大小写十分好用：
1. PROPER（）
目的：将文本数据中的首字母和所有非字母字符后的第一个字母转化为大写，其他字母变为小写。
示例：PROPER("SpreaDSheEt")=Spreadsheet
2. LOWER（）
目的： 将文本数据中的大写字母全部转换成小写
示例：LOWER("SpreaDSheEt")=spreadsheet
3. UPPER（）
目的：将文本数据中的小写字母全部转化大写
示例：UPPER("SpreaDSheEt")="SPREADSHEET"
二、文本数据清洗——清除文本首尾空格
1. TRIM（）
目的：清除文本首尾所有的空格
示例：TRIM(" Monthly Report ")=Monthly Report
三、文本数据清洗——去除字符串
用户有时需清洗掉数据中的一些不需要的字符。例如，去掉表中的“【】”及其中间的内容，如下图所示：
可以结合使用两个函数，先使用 FIND 函数找到“【”和“】”在字符串中的位置，以及【XX】所占的字符数，再使用 REPLACE 函数，根据指定的字符数，用空值替换【XX】这部分的内容。
1. FIND( )
目的：返回字符所在位置
示例：FIND("i","Information")=9
参数说明：FIND(find_text,within_text,start_num)
find_text：需要查找的文本
within_text：包含需要查找文本
start_num：非必填项，从字符串哪个位置开始查找，within_text 里的索引从 1 开始，如果省略 start_num，则假设值为 1
2. REPLACE（）
目的：根据指定的字符数，用其他文本串来替换某个文本串中的部分内容
示例：REPLACE("0123456789",5,4,"*")=0123*89
参数说明：REPLACE(old_text,start_num,num_chars,new_text)
old_text：需要被替换部分字符的文本或单元格引用
start_num：需要用 new_text 来替换 old_text 中字符的起始位置
num_chars：需要用 new_text 来替换 old_text 中字符的个数
new_text：需要替换部分旧文本的文本
四、文本数据清洗——去除字段前后字符
一些订单信息表中，对于销售额、利润等字段会包含货币符号「¥」，想要将字符清除掉进行一些其他的运算。
Excel首尾字符智能清理
用户在使用、更新Excel或是本地数据源的过程中，经常会遇到Excel「文本」的头尾包含「空格或换行符号」等，九数云系统会自动清理单元格字符前后的空格等符号，更加高效便捷。
除了自动清理的字符外，还有一些其他特殊的字符，我们可以结合使用函数来清理：
先用 LEFT 函数截取第一位返回值，与（ 符号进行比较。
如果字符中的第一个字符为（，那么就需要使用 SUBSTITUTE 函数替换字段中的 （、）、￥内容，并使用 CONCATENATE函数拼接「-」负号字符；
如果字符中的第一个字符不是（，那么就使用 SUBSTITUTE 函数替换字段中的 ￥内容；
最后使用 TONUMBER 函数将结果转换成数值类型。
1. LEFT（）
目的：截取前几个字符串
示例：LEFT("Fine software",8)=Fine sof
2. RIGHT()
目的： 截取后几个字符串
示例：RIGHT("Itisinteresting",6)=esting
3. MID()
目的：返回指定位置字符串
示例：MID("Finemoresoftware",9,8)=software
参数说明：MID(text,start_num,num_chars)
text：包含要提取字符的文本串
start_num：文本中需要提取字符的起始位置，文本中第一个字符的start_num为1，以此类推
num_chars：返回字符的长度
4. SUBSTITUTE()
目的：替换指定字符
示例：SUBSTITUTE("database","base","model")=datamodel
将“database”中的“base”替换成“model”
参数说明：SUBSTITUTE(text,old_text,new_text,instance_num)
text：原始字符串。
old_text：需要被替换的部分字符。
new_text：用于替换old_text的字符。
instance_num：指定用new_text替换第几次出现的old_text。可以不指定，若未指定，则替换所有出现的old_text。
5. CONCATENATE()
目的： 将多个字符串合并成一个字符串
示例：CONCATENATE("Average","Price")=AveragePrice
6. TONUMBER()
目的：文本转为数字
示例：TONUMBER("123")=123
7. TOINTEGER（）
目的：文本转为整数
示例：TOINTEGER("123.56")=123
五、文本数据清洗——截取多次出现的指定字符后的内容
例如用户有字段长度不同的字符，需要截取最后一个「_」后的字符，如下图所示：
可以使用 INDEXOF 、FIND、MID、SUBSTITUTE、RIGHT 、LEN等函数组合实现截取字段。
公式示例：
INDEXOF(SPLIT(字段,"_"),LEN(SPLIT(字段,"_"))-1)
1.INDEXOF（）
目的：返回指定位置的字符
示例：INDEXOF("FineReport",2)=n
2. SPLIT（）
目的：分割字段
示例：SPLIT("this is very good"," ")=[this,is,very,good]
3. LEN()
目的：返回字符长度
示例“LEN("Evermoresoftware")=16
六、总结
九数云作为一款强大的数据处理工具，提供了多样化的函数和功能，以支持广泛的文本数据清洗需求。通过灵活运用这些函数的组合和计算能力，我们能够高效地处理和解决日常遇到的各类数据问题。