Strip address and telephone details stored in one column to separate columns

Kento

New Member
Joined
Jun 16, 2008
Messages
16
<TABLE class=MsoNormalTable style="MARGIN: auto auto auto 4.65pt; WIDTH: 466pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" cellSpacing=0 cellPadding=0 width=621 border=0><TBODY><TR style="HEIGHT: 15pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: #e0dfe3; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e0dfe3; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #e0dfe3; WIDTH: 466pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=621>Hi all,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p> </o:p>
I have exported some data where the address and phone number is stored in one field like this:
<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 1"><TD style="BORDER-RIGHT: #e0dfe3; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e0dfe3; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #e0dfe3; WIDTH: 466pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=621>53 Brompton Street MIDDLESBROUGH Cleveland Tel : 01642 898836<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 2"><TD style="BORDER-RIGHT: #e0dfe3; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e0dfe3; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #e0dfe3; WIDTH: 466pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=621>6 The Crescent Shotley Bridge CONSETT County Durham Tel : 07711 886907<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 3"><TD style="BORDER-RIGHT: #e0dfe3; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e0dfe3; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #e0dfe3; WIDTH: 466pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=621>2 Millfield Lanchester DURHAM Tel : 01207 529133<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 4"><TD style="BORDER-RIGHT: #e0dfe3; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e0dfe3; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #e0dfe3; WIDTH: 466pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=621>39 Sussex Walk STOCKTON-ON-TEES Cleveland Tel : 01642 871704<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 5"><TD style="BORDER-RIGHT: #e0dfe3; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e0dfe3; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #e0dfe3; WIDTH: 466pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=621>3 Pickering Grove HARTLEPOOL Cleveland Tel : 07877 166217<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 6"><TD style="BORDER-RIGHT: #e0dfe3; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e0dfe3; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #e0dfe3; WIDTH: 466pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=621>24 Redmond Road SUNDERLAND Tel : 0191 5482016<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 7; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: #e0dfe3; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e0dfe3; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #e0dfe3; WIDTH: 466pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=621>2 St. Annes Gardens Middleton St. George DARLINGTON County Durham Tel : 01325 333139<o:p></o:p>
</TD></TR></TBODY></TABLE>
<o:p> </o:p>
I want to separate it out into:
1. Property Number/name
2. Street
3. LOCALITY
4. Post town (if present)
5. Phone number (minus the Tel:)
I can use the =LEFT function to extract the property number, but the struggle to think of the way to do the rest.
<o:p> </o:p>
Any ideas?
<o:p> </o:p>
Cheers,
<o:p> </o:p>
Sam.
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Telephone number is straightforward:

=RIGHT(A1,LEN(A1)-(FIND("Tel :",A1)+5))

There is not enough of a pattern to reliably extract the rest.
 

Kento

New Member
Joined
Jun 16, 2008
Messages
16
Thanks Andrew,

I'll give that a shot, I think I've though of a work around for the rest.

Sam.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,216
Messages
5,623,446
Members
415,974
Latest member
ZorroOP

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top