Split column in excel / power query

New Contributor


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 can i do that? 

5 Replies
best response confirmed by JD98765 (New Contributor)


If you can go with Excel formula then can use below one. See the attach file.





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:













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




With Power Query:



    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]