Split Text in to adress format

[robinmech]

Hi,

Can some body help me to split the excel value.

I have a list of address's in one excel and I want to split it into the normal address format.

for eg. my current value is something like this,

Abraham , XXX HOUSE, yyyy POST PLACE 689121

<tbody>
</tbody>

I want to split it like thisin same excel cell

[FONT=&quot]Abraham ,
XXX HOUSE,
yyyy POST PLACE 689121[/FONT]

 

[mse330]

How about this formula considering your original example is in cell A1

=SUBSTITUTE(A1,",",CHAR(10))

Also, for the new cell, enable Warp Text

 

[robinmech]

It's working but not coming in the exact format

 

[mse330]

If you are talking about the commas, try the below

=SUBSTITUTE(A1,",",","&CHAR(10))

EDIT : =SUBSTITUTE(A1,", ",","&CHAR(10))

 

[pinarello]

I would use Power Query for that.

 

[alansidman]

Using Power Query, it can place the data in rows as shown below with two steps.

Data Range

	A​
1​	Column1​
2​	Abraham​
3​	XXX HOUSE​
4​	yyyy POST PLACE 689121​

Mcode below

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1", type text}})
in
    #"Changed Type1"