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 this column into two where one is of Text and one is of numbers...how can i do that? 

excel
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Aug 02, 2022

    JD98765 

     

    With Power Query:

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
    TableFromColums = Table.FromColumns(
        {
            List.Select(Source[Values], each Type.Is(Value.Type(_), type text)),
            List.Select(Source[Values], each Type.Is(Value.Type(_), type number))
        },
        type table [Text=text, Number=number]
    )
in
    TableFromColums
  • 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), "")
  )

     

    • JD98765's avatar
      JD98765
      Copper Contributor
      Aug 02, 2022
      i have an office 2019 version. LET formula is not working, sir. it's showing "function isn't valid". i copied correctly.
      • Patrick2788's avatar
        Patrick2788
        Silver Contributor
        Aug 02, 2022

        JD98765 

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

         

         

         

Resources