Text to column

[Mariya]

Hi guys,

I have the following question. Do you have an idea how to convert the text from the columns C and D to separate columns? Like names (on some rows they are 2) to be in separate columns and the address to be separate to city, street and post code. I have tried but it did not work.

Thank you! Mariya

 

[Marc L]

Hi !

For column C, see the Excel inner Convert assistant feature …​

 

[AlanSidman]

Using Power Query, you can split column C and the zip code from column D using the following Mcode.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Tax Yr", Int64.Type}, {"Account Number", type text}, {"Owner Name", type text}, {"Property Address", type text}, {"Cert Status", type text}, {"Deed Status", type text}, {"Standard Flags", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Owner Name", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Owner Name.1", "Owner Name.2", "Owner Name.3", "Owner Name.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Owner Name.1", type text}, {"Owner Name.2", type text}, {"Owner Name.3", type text}, {"Owner Name.4", type text}}),
    #"Split Column by Position" = Table.SplitColumn(#"Changed Type1", "Property Address", Splitter.SplitTextByPositions({0, 5}, true), {"Property Address.1", "Property Address.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position",{{"Property Address.1", type text}, {"Property Address.2", type text}})
in
    #"Changed Type2"