在Excel公式中模拟字符串拆分函数 [英] Simulate string split function in Excel formula
问题描述
我正在尝试用excel公式拆分一个字符串,就像我可以在许多编程语言中一样, string words =some text.split('');
问题是我无法确定单元格中有多个单词。如果我尝试使用 FIND()
或 SEARCH()
函数,他们返回# VALUE
如果没有空格。有没有任何简单的方法来分割字符串,以便它返回单个单词(或更好,以便它返回第一个单词或所有其他单词)?
一个公式返回第一个单词或所有其他单词<
= IF(ISERROR(FIND(,TRIM(A2),1)),TRIM(A2) ,MID(TRIM(A2),FIND(,TRIM(A2),1),LEN(A2)))
示例和结果
文本说明结果
空白
空格
一些文本没有空格一些
一些文本文本与空格文本
一些文本与前导空格一些
一些文本与尾随空格一些
一些文本一些文本文本与多个空格文本一些文本
关于公式的评论:
- TRIM 功能用于删除所有前导和尾随空格。文本中的重复间距也被删除。
- FIND 功能然后找到
第一个空格 - 如果没有空格,那么修改的
文本将被返回 - 否则 MID 函数是
,用于返回
第一个空格
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(' ');
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)?
A formula to return 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)))
Examples and results
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
Comments on Formula:
- 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屋!