当我们在Excel中清理数据时，文本到列和快速填充功能非常有用，但有时您需要使用公式来处理文本。本文将演示常用的17个文本公式示例，包括LEN，TRIM，UPPER，LOWER，PROPER，CONCATENATE，INDIRECT，CHAR，FIND，SEARCH，SUBSTITUTE，LEFT，RIGHT，MID和REPLACE等。

我们从非常简单公式示例开始，然后难度逐渐提高。目录如下：

获取文本字符串的LENgth
将案例更改为UPPER，更低或正确
CONCATENATE文本字符串
使用INDIRECT从文本字符串创建引用
使用CHAR返回特殊字符
替换字符串中的文本
使用TRIM去掉额外的空间
使用FIND和SEARCH获取字符串中文本的位置
使用MID，LEFT和RIGHT从字符串中提取文本
计算字符串中的空格数
计算文本中字符串的出现次数
使用公式将文本拆分为列
获取字符串中的最后一个单词
在字符串中获取第N个单词
将字符串转换为单词数组
将字符串转换为字符数组
使用EXACT进行区分大小写的文本比较

1.获取文本字符串的LENgth

  =LEN("onetwothree")
  Result: 11

当您需要为网页编写标题或填写具有有限数量字符的表单时，这会派上用场。只需打开一个空白电子表格，然后在单元格A1中输入您的标题。在B1中，输入= LEN（A1）。

2.将英文小写更改为全部大写，全部小写或首字母大写

  =UPPER("this text")   Result: THIS TEXT
  =LOWER("THIS TEXT")   Result: this text
  =PROPER("this text")   Result: This Text

3.连接文本字符串

您可以使用CONCATENATE，＆运算符或（较新的）CONCAT和TEXTJOIN函数来连接字符串。以下公式将单元格A1中的名字和单元格B1中的姓氏与中间的空格组合在一起。对于所有四个公式，结果都是“John Smith”。

  A1="John"
  B1="Smith"
  
  =A1 & " " & B1
  =CONCATENATE(A1," ",B1)
  =CONCAT(A1:B1)
  =TEXTJOIN(" ",TRUE,A1:B1)
  
  Result: "John Smith"

注意＆运算符之前和之后的空格不是必需的 – 这里包含空格以帮助使公式更具可读性。

该CONCAT和TEXTJOIN功能是新的功能，需要的Office 365订阅（Excel Online）。CONCAT函数与CONCATENATE类似，只是它允许您使用一系列单元格作为参数。TEXTJOIN函数允许您指定分隔符并忽略空值。

4.使用INDIRECT从文本字符串创建引用

该INDIRECT功能允许您创建一个文本字符串的引用。下面的示例显示了对工作表“Sheet2”中单元格A5的引用。仅当工作表名称包含空格时，才需要工作表名称周围的单引号。

  =INDIRECT("'Sheet 2'!A5")

如果希望工作表名称是用户选择的文本字符串，请使用INDIRECT。例如，如果您有许多相同的工作表，并且希望创建一个使用这些工作表的名称作为查找公式中的引用的汇总表，则可能需要执行此操作。

以下示例在单元格A1中命名的工作表中创建对单元格X5的引用。

  A1="Sheet 2"
  A2=INDIRECT("'" & A1 & "'!X5")

INDIRECT函数在数组公式中非常有用。例如，要创建数字1到N的数组，其中N是单元格A1中包含的数字，您可以使用：

  =ROW(INDIRECT("1:" & A1))

5.使用CHAR返回特殊字符

该CHAR功能供您在一个给定的数字代码返回一个字符。该UNICHAR函数返回一个十进制Unicode值的字符。尽管CHAR函数的大多数数字代码对应于ASCII代码，但有些代码可能不相同（例如代码128-160）。

函数CODE和UNICODE是CHAR和UNICHAR的反面，返回文本字符串中第一个字符的数值。

使用CHAR（34）返回双引号字符

当您连接文本并需要在显示的文本中包含双引号时，您可以使用CHAR（34）或UNICHAR（34）函数。双引号的ASCII和Unicode值都是34。

  =CHAR(34) & "Hi World" & CHAR(34)
  Result: "Hi World" (quotes included)

使用CHAR（10）在字符串中包含换行符

使用公式返回字符串时，请使用CHAR（10）或UNICHAR（10）作为换行符。

  ="abc" & CHAR(10) & "def"
  Result:
  abc
  def

注意要显示有换行符的文本，单元格的“Word Wrap”属性必须设置为打开。

提示要根据数字代码快速生成字符列表，请在空白工作表的单元格A1中输入= CHAR（ROW（））或= UNICHAR（ROW（））并将公式向下复制。

6.替换字符串中的文本

这个SUBSTITUTE函数是非常强大的。它可以用于替换所有字符串，或仅用另一个字符或文本字符串替换第N个字符串。在下面的示例中，我们用空格替换＃字符。

  text = "one#two#three"
  =SUBSTITUTE(text,"#"," ")   Result: "one two three"
  =SUBSTITUTE(text,"#"," ",2)   Result: "one#two three"

7.使用TRIM去掉多余的空格

的TRIM函数删除所有常规空格（ASCII字符32）——除了单词之间的空格。

  =TRIM("   Hi  World  ")
  Result: "Hi World"  (quotes not included)

TRIM不会从文本中删除制表符，换行符或其他非打印字符。要删除非打印ASCII字符0-31（包括制表符），可以使用CLEAN功能。

  text="Hi        World" (contains two tabs)
  =CLEAN(text)
  Result: "HiWorld"

CLEAN功能的问题在于它完全删除了空格字符，因此将由制表符或换行符分隔的单词组合在一起，因此当您更喜欢“Hi World”时，最终可能会出现“HiWorld”。

要将特殊字符更改为常规空格，可以使用SUBSTITUTE函数，然后使用TRIM包装函数以删除多余的空格，如下所示：

  text="Hi        World" (contains two tabs)
  =TRIM( SUBSTITUTE(text,CHAR(9)," ") )
  Result: "Hi World"

注意以下是常用替换字符的CHAR代码的简短列表：制表符Tab（9），换行符Newline（10），回车符Carriage Return（13），空格Space （32），非断开空格Non-Breaking Space（160），特殊引用符号Special Quote Symbols：’（145），’（146），“（147），”（148）

8.使用FIND和SEARCH获取字符串中文本的位置

区分大小写的搜索函数FIND和不区分大小写的搜索函数SEARCH，都会返回另一个字符串中的文本字符串的起始字符位置。

  =FIND("a","ooAooaoo",1)   Result: 6
  =SEARCH("a","ooAooaoo",1)   Result: 3

FIND和SEARCH函数的第三个参数是开始搜索的起始字符位置，默认值为1（第一个字符）。您可以使用嵌套的FIND或嵌套的SEARCH来查找第二次出现的文本字符串的位置，如下所示：

  text="ooAooAoo"
  =FIND("A",text,FIND("A",text,1)+1)
  
  Evaluation Steps
  Step 1: FIND("A","ooAooAoo",3+1)
  Step 2: FIND("A","ooAooAoo",4)
  Step 3: 6

当与FIND或SEARCH结合使用时，你可以使用SUBSTITUTE做一些非常棘手的事情。以下函数允许您在另一个文本字符串中查找第N个字符串出现的位置。在此示例中，我们想知道名称中第3个空格的位置。

  text="Tim A. J. Crane"
  =FIND("#",SUBSTITUTE(text," ","#",3),1)
  
  Evaluation Steps
  Step 1: =FIND("#","Tim A. J.#Crane",1)
  Step 2: =10

9.使用MID，LEFT和RIGHT从字符串中提取文本

MID函数就像是其他的编码语言SUBSTR（）函数，通过指定起始字符位置和要提取的字符数从另一个字符串中提取字符串。REPLACE函数，除了返回原来的文本字符串替换的文本外，与MID相似。LEFT和RIGHT函数就像是从一个字符串的左侧或右侧提取文本的MID函数的简化版本。

  SYNTAX: =MID(text,start_num,num_chars)
  SYNTAX: =REPLACE(text,start_num,num_chars,replace_text)
  SYNTAX: =LEFT(text,num_chars)
  SYNTAX: =RIGHT(text,num_chars)

下面是一个显示这些功能如何工作的示例。

  text = "one#two#three"
  =MID(text,5,3)   Result: "two"
  =REPLACE(text,5,3,"BLAHBLAH")   Result: "one#BLAHBLAH#three"
  =LEFT(text,5)   Result: "one#t"
  =RIGHT(text,7)   Result: "o#three"

我们通常使用SUBSTITUTE而不是REPLACE。

当您在MID，LEFT或RIGHT中使用FIND或SEARCH时，它们功能变得更加强大。以下有一些这方面的示例。

10.计算文本字符串中的空格数

您可以使用此技术计算除空格之外的其他字符。例如，只需将“ ”替换为“,”或“;” 以便可以计算逗号或分号的数量。

  text = "Todd Allen Smith"
  =LEN(text)-LEN(SUBSTITUTE(text," ",""))
  
  Evaluation Steps
  Step 1: LEN("Todd Allen Smith")-LEN("ToddAllenSmith")
  Step 2: 16-14
  Step 3: 2

此示例中的SUBSTITUTE函数返回一个删除了空格的新文本字符串（将所有“ ”替换为“”）。我们从原始长度中减去修改后的文本字符串的长度，以计算原始文本中的空格数。

11.计算文本中字符串的出现次数

如果要计算文本中字符串出现次数（而不是单个字符），则可以使用上述公式的修改版本。在这种情况下，我们只是将结果除以字符串的长度。

  text = "A##B##C"
  string = "##"
  =(LEN(text)-LEN(SUBSTITUTE(text,string,""))) / LEN(string)
  Result: 2

12.使用公式将文本拆分为列

Excel中的文本到列向导（Text-to-Columns Wizard）和快速填充Flash Fill（Ctrl + e）功能使用起来既快速又简单，但有时您可能希望使用公式（以制作更动态或自动化的工作表）。使用公式拆分文本通常涉及LEFT，RIGHT，MID，LEN和FIND（或SEARCH）的组合。我们将从几个简单的公式开始。

提取名字

要从文本字符串中提取第一个单词（或名称），可以使用以下公式，其中text是单元格引用或由双引号括起来的字符串，如“this”。

  text = "Tom Sawyer"
  =LEFT(text,FIND(" ",text)-1)
  
  Evaluation Steps
  Step 1: =LEFT("Tom Swayer",4-1)
  Step 2: =LEFT("Tom Swayer",3)
  Step 3: ="Tom"

在上面的公式中，FIND（“ ”，text）返回文本中第一个空格“ ”的数字位置。我们从该值中减去一个，因此结果中不包含空格。

在第一个空格后提取文本

要在第一个空格之后返回字符串的其余部分，我们使用RIGHT函数，该函数从字符串的末尾提取指定数量的字符。我们通过从字符串的总长度中减去空格的位置来计算要提取的字符数：

  text = "Jay Allen Reems"
  =RIGHT(text,LEN(text)-FIND(" ",text))
  
  Evaluation Steps
  Step 1: =RIGHT("Jay Allen Reems",LEN("Jay Allen Reems")-4)
  Step 2: =RIGHT("Jay Allen Reems",15-4)
  Step 3: =RIGHT("Jay Allen Reems",11)
  Step 4: ="Allen Reems"

support.office.com上的文章“将文本拆分为带有函数的不同列 ”提供了各种公式的示例，这些公式根据可以编写名称的不同方式将名称分成不同的部分。

Google表格中的SPLIT功能

我希望Excel最终包含一个像Google表格中可用的SPLIT功能。例如，要将像“Allen James Reems”这样的名称拆分为单独的单元格，只需要以下简单的公式：

  =SPLIT(text," ")

如果您想在Excel中看到SPLIT功能，请通过excel.uservoice.com 对此建议进行投票。

13.获取字符串中的最后一个单词

对于此示例，我们将使用名称“Allen Jay Reems”来显示如何获取字符串中的最后一个单词，其中空格字符是分隔符。此示例显示了我们如何使用中间步骤构建更复杂的公式。

  delimiter = " "
  last_name
     =RIGHT(text,LEN(text)-position_of_last_delimiter)
  position_of_last_delimiter
     =FIND("^", SUBSTITUTE(text,delimiter,"^",number_of_delimiters))
  number_of_delimiters
     =LEN(text)-LEN(SUBSTITUTE(text,delimiter,""))

A1 =“Allen Jay Reems”的最终公式如下所示，将返回姓氏“Reems”：

  =RIGHT(A1,LEN(A1)-FIND("^", SUBSTITUTE(A1," ","^",LEN(A1)-LEN( SUBSTITUTE(A1," ","") ))))

如果你有一个由逗号分隔的字符串，如“one, two, three, four”，你可以通过在上面的公式中用“,”替换“”来提取最后一个元素，并用TRIM包装整个东西以删除前导空格。

如果您的字符串可能不包含任何空格，那么您可以使用IFERROR包装整个公式以返回空字符串或原始文本。

如果您的字符串包含“^”字符，则需要选择在公式中使用的其他临时分隔符，例如“〜”或其他不常用的字符。

14.在字符串中获取第N个单词

这个功能真的很疯狂，但很有用。原理是：用一堆空格替换分隔符文本，这样你就可以创建一个新的文本字符串，可以将其分成块，每个块包含一个不同的单词。每个单词周围会有很多空间，因此您可以使用TRIM将其删除。

  text = "One#Two#Three" (the original text)
  delimiter = "#" (the delimiter text)
  word_num = 2 (the word to extract)
  
  =TRIM(MID(SUBSTITUTE(text,delimeter,REPT(" ",LEN(text))),(word_num-1)*LEN(text)+1,LEN(text)))
  
  Evaluation Steps
  1: =TRIM(MID(SUBSTITUTE(text,"#",REPT(" ",13)),(2-1)*13+1,13))
  2: =TRIM(MID(SUBSTITUTE(text,"#","             "),14,13))
  3: =TRIM(MID("One             Two             Three",14,13))
  4: =TRIM("  Two        ")
  5: ="Two"

在Google表格中，这是小菜一碟。SPLIT函数返回一个数组，因此您可以使用以下命令返回字符串中的第3个单词：

  =INDEX(SPLIT(text,delimiter),3)

15.将文本字符串转换为单词数组

想要将“One＃Two＃Three”转换成可以在其他公式中使用的{“One”;“Two”;“Three”}这样的数组？这就是Google表格中的SPLIT功能所做的，但是在Excel中执行此功能仍然是可能的 – 只是有点复杂。首先，从上一节中的公式开始，用以下内容替换word_num：

  =ROW(INDIRECT("1:"&((LEN(text)-LEN(SUBSTITUTE(text,delimiter,"")))/LEN(delimiter)+1)))

要在单元格数组中显示结果，请记住使用Ctrl + Shift + Enter。如果要在行而不是列中显示此公式的结果，请使用TRANSPOSE。

要将数组创建为内联文本字符串，可以使用以下公式：

  text = "One#Two#Three" (the original text)
  str = "#" (the delimiter text)
  ="{"&CHAR(34)&SUBSTITUTE(text,str,CHAR(34)&";"&CHAR(34))&CHAR(34)&"}"
  
  Resulting text string: {"One";"Two";"Three"}

16.将文本字符串转换为字符数组

如果要将文本字符串拆分为单个字符数组，例如将“abcd”转换为{“a”;“b”;“c”;“d”}，则公式非常简单。此公式作为数组公式（Ctrl + Shift + Enter）输入。

  =MID(text_string,ROW( INDIRECT("1:"&LEN(text_string)) ),1)

要将字符串中的每个字符转换为其数字代码，请使用CODE或UNICODE包装上述函数。该公式将作为多单元格数组输入，以显示不同单元格中的每个数值。

Google表格中的以下公式会将文本字符串转换为逗号分隔的数字代码值列表。

  text="Hello"
  =ARRAYFORMULA( TEXTJOIN(",",TRUE, CODE(MID(text,ROW( INDIRECT("1:"&LEN(text)) ),1))) )
  Resulting text string: "72,101,108,108,111"

17.使用EXACT进行区分大小写的文本比较

如果您需要确定单元格中的文本是否为大写，小写还是首字母大写，则可以使用EXACT公式将原始文本与转换后的文本进行比较。

如果单元格A1中的文本分别为大写、小写或首字母大写，则以下公式返回TRUE：

  =EXACT(A1,UPPER(A1))
  =EXACT(A1,LOWER(A1))
  =EXACT(A1,PROPER(A1))

注意：SUMIF和COUNTIF文章提供了许多基于文本的比较的不同示例。

在这里可以下载本文内容的示例文档。

改写自Jon Wittwe

用excel做文本清洗操作的17个终极公式

1.获取文本字符串的LENgth

2.将英文小写更改为全部大写，全部小写或首字母大写

3.连接文本字符串

4.使用INDIRECT从文本字符串创建引用

5.使用CHAR返回特殊字符

使用CHAR（34）返回双引号字符

使用CHAR（10）在字符串中包含换行符

6.替换字符串中的文本

7.使用TRIM去掉多余的空格

8.使用FIND和SEARCH获取字符串中文本的位置

9.使用MID，LEFT和RIGHT从字符串中提取文本

10.计算文本字符串中的空格数

11.计算文本中字符串的出现次数

12.使用公式将文本拆分为列

提取名字

在第一个空格后提取文本

Google表格中的SPLIT功能

13.获取字符串中的最后一个单词

14.在字符串中获取第N个单词

15.将文本字符串转换为单词数组

16.将文本字符串转换为字符数组

17.使用EXACT进行区分大小写的文本比较

Write a Comment Cancel Reply

用excel做文本清洗操作的17个终极公式

1.获取文本字符串的LENgth

2.将英文小写更改为全部大写，全部小写或首字母大写

3.连接文本字符串

4.使用INDIRECT从文本字符串创建引用

5.使用CHAR返回特殊字符

使用CHAR（34）返回双引号字符

使用CHAR（10）在字符串中包含换行符

6.替换字符串中的文本

7.使用TRIM去掉多余的空格

8.使用FIND和SEARCH获取字符串中文本的位置

9.使用MID，LEFT和RIGHT从字符串中提取文本

10.计算文本字符串中的空格数

11.计算文本中字符串的出现次数

12.使用公式将文本拆分为列

提取名字

在第一个空格后提取文本

Google表格中的SPLIT功能

13.获取字符串中的最后一个单词

14.在字符串中获取第N个单词

15.将文本字符串转换为单词数组

16.将文本字符串转换为字符数组

17.使用EXACT进行区分大小写的文本比较

相关文章

You might also like

软科中国大学排名前20名（2020年~2024年）

数据可视化分析：中国中外合作大学排名（2020年~2024年）

七所高校专利申请变化趋势（2000~2023）

Write a Comment Cancel Reply