Excel-从单元格拆分字符串 [英] Excel - Split String from Cells

问题描述

我有一个大型CSV文件，其中包含几百行包含HTML的几百行.我需要将某些部分分成新的列.例如，一个单元格可以包含:

I have a large CSV with a couple of columns containing HTML over hundreds of rows. I need to separate certain parts into new columns. For example, a cell can contain:

<style>.some-class{property:value;}</style><div class="title">This is a title</div><div class="description">This is a description></div>

我想将标题和说明的内容移到单独的列中，同时删除所有标记.

I want to move the contents of title and description into separate columns, while removing all markup.

是否可以使用公式而不是宏或VBA来做到这一点?

Is there way to do this using formulas rather than macros or VBA?

谢谢

推荐答案

为防止超长公式，将公式拆分为两列并根据需要隐藏列可能更容易.例如，如果文本是"A"列，则将这些公式放在以下单元格中: 进入B1:

To prevent super long formulas, may be easier to split formulas over two columns and hide the column if you want. For example, if you text is the A column, then put these formulas in the following cells: into B1:

=RIGHT(A1,LEN(A1)-FIND("class=""title"">",A1)-13)

进入C1:

=LEFT(B1,FIND("</div",B1)-1)

进入D1:

=RIGHT(A1,LEN(A1)-FIND("class=""description"">",A1)-19)

进入E1:

=LEFT(D1,FIND("</div",D1)-1)

然后复制\填写

这篇关于Excel-从单元格拆分字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！