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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Telephone number is straightforward:

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

There is not enough of a pattern to reliably extract the rest.
 
Upvote 0
Thanks Andrew,

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

Sam.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top