Split Text in to adress format

robinmech

New Member
Joined
Feb 25, 2016
Messages
37
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
Joined
Oct 18, 2007
Messages
449
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
Joined
Oct 18, 2007
Messages
449
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
Joined
Feb 26, 2007
Messages
5,035
Office Version
2019
Platform
Windows
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"
 

Forum statistics

Threads
1,077,684
Messages
5,335,654
Members
399,031
Latest member
Morto

Some videos you may like

This Week's Hot Topics

Top