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]
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

mse330

Active Member
Joined
Oct 18, 2007
Messages
491
Office Version
365
Platform
Windows
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
491
Office Version
365
Platform
Windows
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,192
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"
 

Watch MrExcel Video

Forum statistics

Threads
1,090,160
Messages
5,412,812
Members
403,449
Latest member
Jamil123

This Week's Hot Topics

Top