TVP

数据整理与清洗

len()

left()

right()

mid()

find()

& 或 concatenate()

trim()

clean()

substitute()

replace()

upper()

lower()

proper()

value() 或 -- 或 *1

Text()

len(text) 函数

left(text,[num_chars])

left("Good morning",1) = G

left("Good morning",4) = Good

Right(text,[num_chars])

right("Good morning",1) = g

right("Good morning",7) = morning

MID(text,Start_num,num_chars)

Find(find_text, within_text, [start_num])

find("i","China") = 3

find("I","China,1) = #VALUE! (大小写不同i、I)

-CONCATENATE(text1,[text2],[text3],…)将多个字符串合并成一个。

-&连接字符串，更加简便。

A1=12,A2=34 - A1 & A2=1234（文本格式的12）

”A"&"B"=AB

trim(text)

clean(text)

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

substitute(A1,"e","E") = bookkEEpEr

substitute(A1,"e","E",1) = bookkEeper

substitute(A1,"e","E",2) = bookkeEper

substitute(A1,"e","E",3) = bookkeepEr

substitute(A1,"e","E",4) = bookkeeper

replace(old_text, Start_num, num_chars, new_text)

bookstore中,从第5个位置s开始，取长度为5的区域（e),即"store"，替换为"keeper !"

LEFT(A2,5)&"."&MID(A2,6,4)&"."&RIGHT(A2,3) = 12345.6789.ABC

SUBSTITUTE(A3,".","-") = 12345-6789-ABC

REPLACE(A4,12,3,"XI-OTC") = 12345-6789-XI-OTC

Trim(A2) = DIN 912 M5x25 - A2K

FIND("M",TRIM(A2)) = 9, 即M位置为9，那么我们只需用Left()取到8就可以了。

LEFT(TRIM(A2),FIND("M",TRIM(A2))-1) = "DIN 912 "

TRIM(LEFT(TRIM(A2),FIND("M",TRIM(A2))-1)) = DIN 912

Trim(A2) = DIN 912 M5x25 - A2K

FIND("-",TRIM(A2)) = 15

TRIM(RIGHT(TRIM(A2),LEN(TRIM(A2))-FIND("-",TRIM(A2)))) = A2K

Mid()出场

MID(TRIM(A2),FIND("M",TRIM(A2)),FIND("-",TRIM(A2))-FIND("M",TRIM(A2))) = "M5x25 "

TRIM(MID(TRIM(A2),FIND("M",TRIM(A2)),FIND("-",TRIM(A2))-FIND("M",TRIM(A2)))) = M5x25

UPPER(text)

LOWER(text)

PROPER(text)

• 发表于:
• 原文链接http://kuaibao.qq.com/s/20180211G0RIQK00?refer=cp_1026
• 腾讯「腾讯云开发者社区」是腾讯内容开放平台帐号（企鹅号）传播渠道之一，根据《腾讯内容开放平台服务协议》转载发布内容。
• 如有侵权，请联系 cloudcommunity@tencent.com 删除。

2024-06-26

2024-06-26

2024-06-26

2024-06-26

2024-06-26

2024-06-26

2024-06-26

2024-06-26

2024-06-26

2024-06-26

2024-06-26

2024-06-26

2024-06-26

2024-06-26

2024-06-26

2024-06-26

2024-06-26

2024-06-26

2024-06-26

2024-06-26