Forum Discussion

JD98765's avatar
JD98765
Copper Contributor
Aug 02, 2022
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...
excel
  • Harun24HR's avatar
    Harun24HR
    Aug 02, 2022

    JD98765 

    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),""))

     

     

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Aug 02, 2022

JD98765 

As an Excel 365 user, I would go for the Harun24HR solution.  I go for descriptive, rather than concise, so implemented the formula as

= LET(
     text,    FILTER(data, ISTEXT(data)),
     numbers, FILTER(data, ISNUMBER(data)),
     IFERROR( HSTACK(text, numbers), "")
  )

 

Resources