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

[Kento]

<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-comfficeffice" /><o></o>
<o> </o>
I have exported some data where the address and phone number is stored in one field like this:
<o></o>
</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></o>
</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></o>
</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></o>
</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></o>
</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></o>
</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></o>
</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></o>
</TD></TR></TBODY></TABLE>
<o> </o>
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> </o>
Any ideas?
<o> </o>
Cheers,
<o> </o>
Sam.

 

[Andrew Poulsom]

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]

Thanks Andrew,

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

Sam.