SOLVED

Split column in excel / power query

Copper 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 numbers...how can i do that? 

5 Replies
best response confirmed by JD98765 (Copper Contributor)
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),""))

 

Harun24HR_0-1659420084215.png

 

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

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

 

 

Patrick2788_0-1659465025850.png

 

@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 

 

With Power Query:

_Screenshot.png

 

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
1 best response

Accepted Solutions
best response confirmed by JD98765 (Copper Contributor)
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),""))

 

Harun24HR_0-1659420084215.png

 

View solution in original post