SOLVED

New Contributor

# 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?

5 Replies
best response confirmed by JD98765 (New Contributor)
Solution

# Re: Split column in excel / power query

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

# Re: Split column in excel / power query

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

# Re: Split column in excel / power query

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

# Re: Split column in excel / power query

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

# Re: Split column in excel / power query

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``````