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.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,012
Messages
5,834,923
Members
430,326
Latest member
tomwax46

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