Split Text in to adress format

robinmech

New Member
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

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

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

Also, for the new cell, enable Warp Text
 

mse330

Active Member
If you are talking about the commas, try the below

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

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

alansidman

Well-known Member
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
Code:
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"
 

Some videos you may like

This Week's Hot Topics

Top