Not much of a change at all, so full credit still goes to Ron, but I figured I'd share it since I just figured it out. ((ROW(OFFSET($R2,LEN($R2)-LEN(SUBSTITUTE($R2,"|",""))+1)))=(ROW($R2))),99))īasically instead of subtracting 1, it subtracts the row number of whatever the row of the cell is that you want to split up.
EXCEL SPLIT CELLS FORMULA CODE
So all of that said, I substituted 2 things in the code so that it'll do it regardless of where you start in the sheet: note that is $R$2 for me, it's just the cell is in a named Table, and corresponds to the same row in the table, but another named column, so I think $R2 could be substituted and still work ",99)), The result is the OFFSET function duplicates the cell by 3(in my example) into separate rows This gives the number of separate text strings that we want split up. Which gives the number of delimiters in the cell being referenced: LEN("Haz|Tank|Doub") - LEN(SUBSTITUTE("Haz|Tank|Doub","|","")) = 2Īdding 1 to this: LEN("Haz|Tank|Doub")-LEN(SUBSTITUTE("Haz|Tank|Doub","|",""))**+1))** Sub SplitText () Dim MyArray () As String, Count As Long, i As Variant For n 2 To 12 MyArray Split (Cells (n, 2), ',') Count 3 For Each i In MyArray Cells (n, Count) i Count Count + 1 Next i Next n End Sub Click on Insert in the menu bar, and click on Module. The OFFSET part of the function is taking the cell with the text that you want to split(my examples):Īnd duplicating it into an array column, with the height specified by taking the total number of characters in the cell LEN("Haz|Tank|Doub") = 13 charactersĪnd subtracting it taking the total number of characters in that cell, when the delimiter is replaced by empty space "" LEN(SUBSTITUTE("Haz|Tank|Doub","|","")) = Regarding Ron Rosenfeld's answer and Austin Wismer's question as to why it wasn't working for him, I spent the good part of an hour breaking it down and trying to figure out what makes it work(I think). Note that the FILTERXML function extracts the values as numbers, whereas the first formula extracts the numbers as text which must be converted to numbers before being used in a numeric formula.
EXCEL SPLIT CELLS FORMULA MAC
With Excel 2010+ for Windows (not MAC versions) with the FILTERXML funcdtion: =FILTERXML("" & SUBSTITUTE(A1,"#","") & "","//s")
The array that is returned is a text array, so the 1* at the beginning is one way to convert them to numbers
0 kommentar(er)
