Excel公式中模拟字符串拆分函数 [英] Simulate string split function in Excel formula

问题描述

我正在尝试在 excel 公式中拆分字符串，就像我可以在许多编程语言中做的那样，例如

I am trying to split a string in an excel formula, something like I can do in many programming languages, e.g.

string words = "some text".split(' ');

问题是我不能确定单元格中有多个单词.如果我尝试使用 FIND() 或 SEARCH() 函数，如果没有空格，它们将返回 #VALUE.是否有任何简单的方法来拆分字符串，以便它返回单个单词(或者甚至更好，以便它返回 第一个 单词或 所有其他 单词)?

The problem is that I can't be sure that there is more than one word in the cell. If I try to use the FIND() or SEARCH() functions, they return #VALUE if there is not space. Is there any easy way to split the string so that it returns the individual words (or even better, so that it returns either the first word or all the other words)?

推荐答案

返回第一个词或所有其他词的公式.

=IF(ISERROR(FIND(" ",TRIM(A2),1)),TRIM(A2),MID(TRIM(A2),FIND(" ",TRIM(A2),1),LEN(A2)))

示例和结果

Text                  Description                      Results

                      Blank 
                      Space 
some                  Text no space                some
some text             Text with space                  text
 some                 Text with leading space          some
some                  Text with trailing space         some
some text some text   Text with multiple spaces        text some text

对公式的评论:

• TRIM 函数用于删除所有前导和尾随空格.文本中的重复间距也被删除.
• FIND 函数然后找到第一个空格
• 如果没有空格，则修剪返回文本
• 否则 MID 功能是用于返回之后的任何文本第一个空格

• The TRIM function is used to remove all leading and trailing spaces. Duplicate spacing within the text is also removed.
• The FIND function then finds the first space
• If there is no space then the trimmed text is returned
• Otherwise the MID function is used to return any text after the first space

这篇关于Excel公式中模拟字符串拆分函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！