Forum Discussion
Solved
Split column in excel / power query
In a column the data is like this: (R = Row ) R1 = number R2 = number R3 = number R4 = text R5 = number R6 = number R7 = text R8 = text R9= number R10 = number So now i want to split th...
If you can go with Excel formula then can use below one. See the attach file.
=LET(x,A1:A10,y,FILTER(x,NOT(ISNUMBER(x))),z,FILTER(x,ISNUMBER(x)),IFERROR(CHOOSE({1,2},y,z),""))
- i have an office 2019 version. LET formula is not working, sir. it's showing "function isn't valid". i copied correctly.
A possible formula solution. Ctr+Shift+Enter for both to define as arrays.
For text column:
=IFERROR(INDEX($A$1:$A$11,SMALL(IF(ISTEXT($A$2:$A$11),ROW($A$2:$A$11)),ROW(A1))),"")Numbers:
=IFERROR(INDEX($A$1:$A$11,SMALL(IF(ISNUMBER($A$2:$A$11),ROW($A$2:$A$11)),ROW(A1))),"")
