The formula above returns the text in cell A1 split into multiple rows in one column. What’s that second XPath argument about? I’ll get to that in a second. The function has this syntax:Ī3: = FILTERXML( “” & SUBSTITUTE( $A$1, “:”, “” ) & “”, To filter that XML, we wrap a FILTERXML function around the formula. So, rather than “p” and “c” I could have used “parent” and “child”…or even “dog” and “cat”. And unlike HTML, we can use any tag values we want. But XML describes data and HTML describes how data is displayed. Or, in a structured format, the return value would look like this: …where SUBSTITUTE’s second argument contains the delimiter and where “p” stands for parent and “c” stands for child. This is easy to do with this formula…Ī3: =”” & SUBSTITUTE($A$1, “:”, “” ) & “”
The first step is to convert the text into XML. You could do this easily by using FILTERXML. Suppose that the text A:B:C:D is in cell A1 and you want to split it into four cells of a dynamic array that begins in cell A3. If you have that function, here’s how to do it… Splitting Text with FILTERXML The other way works only with versions of Excel that have the FILTERXML function.
It’s kind of complex and difficult to explain, but I’ll explain it in detail soon.
One of them has been floating around the web for a long time. …where text is the text to split, delimiter is the delimiter that separates each chunk of text, and index is the optional number that returns the specified chunk of text.īut in the meantime, there are at least two other ways to split text. The function would have a syntax like this… For the longest time I’ve wished that Microsoft would add a SPLIT function to Excel.
0 kommentar(er)
