Split column in excel / power query

[JD98765]

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? 

[#2 · Accepted solution]

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

[#3]

i have an office 2019 version. LET formula is not working, sir. it's showing "function isn't valid". i copied correctly.

[#4]

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

[#5]

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

[#6]

@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