使用公式[duplicate]将分隔文本分隔到列

This question already has an answer here:

这个问题在这里已有答案:

Split cells in several columns with Excel formulas 2 answers

使用Excel公式2个答案分割几列中的单元格

I have strings in cells which look like this (there may be hundreds)

我在单元格中有字符串,看起来像这样(可能有数百个)

713.00;482.00;337.00;530.00;
71.00;32.54;37.00;5300.30;

There are 4 numbers, each number is followed by a semi-colon.

有4个数字,每个数字后跟一个分号。

The lengths of these numbers will vary

这些数字的长度会有所不同

I have the formula for the first cell (Split 1) which is:

我有第一个单元格(Split 1)的公式,它是:

=LEFT(B2,(FIND(";",B2,1)-1))

I just cannot figure out the formualas for the following 3 cells (Split 2, 3 and 4)

我只是想不出以下3个细胞的格式(分裂2,3和4)

NOTE: This needs to be a formula NOT the text to columns function.

注意:这需要是一个公式而不是文本到列的功能。

Any suggestions would be appreciated, and if more info is needed please ask! :)

任何建议将不胜感激,如果需要更多信息,请询问! :)

1 个解决方案

#1

In B1 enter:

在B1输入:

=TRIM(MID(SUBSTITUTE($A1,";",REPT(" ",999)),COLUMNS($A:A)*999-998,999))

and copy across and down:

并反复复制:

To get numbers rather than text substrings, use:

要获取数字而不是文本子字符串,请使用:

=--TRIM(MID(SUBSTITUTE($A1,";",REPT(" ",999)),COLUMNS($A:A)*999-998,999))

and format the results with two decimal places.

并用两位小数格式化结果。

One weakness is that the number 999 is arbitrary, so this formula will fail if the length of the initial string is greater than that number. To guard against this, we can use LEN($A1) instead of 999:

一个缺点是数字999是任意的,因此如果初始字符串的长度大于该数字,则该公式将失败。为了防止这种情况,我们可以使用LEN($ A1)代替999:

=--(TRIM(MID(SUBSTITUTE($A1,";",REPT(" ",LEN($A1))),(COLUMNS($A:A)-1)*LEN($A1)+1,LEN($A1))))

#1