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,

[TABLE="width: 670"]
<tbody>[TR]
[TD="class: xl63, width: 670"]Abraham , XXX HOUSE, yyyy POST PLACE 689121[/TD]
[/TR]
</tbody>[/TABLE]

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
[Table="class: grid"][tr][td] [/td][td]

A​

[/td][/tr]
[tr][td]

1​

[/td][td]

Column1​

[/td][/tr]


[tr][td]

2​

[/td][td]

Abraham​

[/td][/tr]


[tr][td]

3​

[/td][td]

XXX HOUSE​

[/td][/tr]


[tr][td]

4​

[/td][td]

yyyy POST PLACE 689121​

[/td][/tr]
[/table]
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"