用Excel公式拆分几列的单元格 [英] Split cells in several columns with Excel formulas
问题描述
我有一个包含以下内容的单元格:
A
1 text1; text2; text3; text4; text5
我想将它分成五个单元格,每次出现一个;
,如下所示:
BCDEF
1 text1 text2 text3 text4 text5 $我知道Excel有一个文本到列功能,但是我需要这样才能自动化,这样就可以了不是一个选择我在 B1
中输入: = left(A1; FIND(;; A1; 1)-1)
这得到我 text1
,这是我想要的。对于下一个单元格,我需要 text2
。我试过这个:
=右(A1; len(A1)-len(B1)-1)
我的工作表现在如下所示:
BC
1 text1 text2; text3; text4; text5
我现在的问题是,我需要删除第一个;
之后的所有内容,但我似乎无法想像出来。任何想法?
解决方案如果您想要一个等同于文本到列的公式,而A1包含文本要解析,然后在B1中输入:
= TRIM(MID(SUBSTITUTE($ A1,;,REPT(,999)),COLUMNS A:B)* 999-998,999))并复制。
I have a cell with the following content:
A
1 text1;text2;text3;text4;text5
I'd like to divide it into five cells, one for each occurrence of ;
, like this:
B C D E F
1 text1 text2 text3 text4 text5
I know Excel has a "Text to columns" function, but I need this to be automated, so that's not an option. I've entered this in B1
:
=left(A1;FIND(";";A1;1)-1)
This gets me text1
, which is what I want. For the next cell I need text2
. I've tried this:
=right(A1;len(A1)-len(B1)-1)
My sheet now looks like this:
B C
1 text1 text2;text3;text4;text5
My issue now is, that I need to remove everything after the first ;
, but I can't seem to figure it out. Any ideas?
解决方案 If you want a formula equivalent to Text to Columns and A1 contains the text to be parsed, then in B1 enter:
=TRIM(MID(SUBSTITUTE($A1,";",REPT(" ",999)),COLUMNS($A:B)*999-998,999)) and copy across.
这篇关于用Excel公式拆分几列的单元格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!