Split numbers and text in same column

[xandertio]

Hi all,

I am new to the mr. excel forum. So I hope I am asking the wright things within the good thread...

I have got a question regarding splitting numbers from text that are in the same column. Is there a quick solution to my problem.
Text to columns won't work because the data is not the same.

See below the an example of my data:

<table style="border-collapse: collapse; width: 169pt;" width="225" border="0" cellpadding="0" cellspacing="0"><col style="width: 169pt;" width="225"> <tbody><tr style="height: 11.25pt;" height="15"> <td class="xl63" style="border-style: none solid solid; border-color: -moz-use-text-color white white; border-width: medium 0.5pt 1.5pt; background: rgb(79, 129, 189) none repeat scroll 0% 0%; height: 11.25pt; width: 169pt; font-size: 8pt; color: white; font-weight: 700; text-decoration: none; font-family: Arial; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" width="225" height="15">Address</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl63" style="border-style: none solid solid; border-color: -moz-use-text-color white white; border-width: medium 0.5pt 0.5pt; background: rgb(184, 204, 228) none repeat scroll 0% 0%; height: 11.25pt; font-size: 8pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" height="15">Wilhelminastraat 78</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl63" style="border-style: none solid solid; border-color: -moz-use-text-color white white; border-width: medium 0.5pt 0.5pt; background: rgb(219, 229, 241) none repeat scroll 0% 0%; height: 11.25pt; font-size: 8pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" height="15">Kampstraat 45</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl63" style="border-style: none solid solid; border-color: -moz-use-text-color white white; border-width: medium 0.5pt 0.5pt; background: rgb(184, 204, 228) none repeat scroll 0% 0%; height: 11.25pt; font-size: 8pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" height="15">Kohlbergsgracht 1</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl63" style="border-style: none solid solid; border-color: -moz-use-text-color white white; border-width: medium 0.5pt 0.5pt; background: rgb(219, 229, 241) none repeat scroll 0% 0%; height: 11.25pt; font-size: 8pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" height="15">Jos Klijnenlaan 23</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl63" style="border-style: none solid solid; border-color: -moz-use-text-color white white; border-width: medium 0.5pt 0.5pt; background: rgb(184, 204, 228) none repeat scroll 0% 0%; height: 11.25pt; font-size: 8pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" height="15">Beekstraat 4</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl63" style="border-style: none solid solid; border-color: -moz-use-text-color white white; border-width: medium 0.5pt 0.5pt; background: rgb(219, 229, 241) none repeat scroll 0% 0%; height: 11.25pt; font-size: 8pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" height="15">Akerstraat Noord 74</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl63" style="border-style: none solid solid; border-color: -moz-use-text-color white white; border-width: medium 0.5pt 0.5pt; background: rgb(184, 204, 228) none repeat scroll 0% 0%; height: 11.25pt; font-size: 8pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" height="15">Veeladingstraat 14</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl63" style="border-style: none solid solid; border-color: -moz-use-text-color white white; border-width: medium 0.5pt 0.5pt; background: rgb(219, 229, 241) none repeat scroll 0% 0%; height: 11.25pt; font-size: 8pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" height="15">Becamusstraat 15</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl63" style="border-style: none solid solid; border-color: -moz-use-text-color white white; border-width: medium 0.5pt 0.5pt; background: rgb(184, 204, 228) none repeat scroll 0% 0%; height: 11.25pt; font-size: 8pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" height="15">Wilhelminalaan 1</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl63" style="border-style: none solid solid; border-color: -moz-use-text-color white white; border-width: medium 0.5pt 0.5pt; background: rgb(219, 229, 241) none repeat scroll 0% 0%; height: 11.25pt; font-size: 8pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" height="15">Wijngracht 51</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl63" style="border-style: none solid solid; border-color: -moz-use-text-color white white; border-width: medium 0.5pt 0.5pt; background: rgb(184, 204, 228) none repeat scroll 0% 0%; height: 11.25pt; font-size: 8pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" height="15">Raadhuisstraat 21</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl63" style="border-style: none solid solid; border-color: -moz-use-text-color white white; border-width: medium 0.5pt 0.5pt; background: rgb(219, 229, 241) none repeat scroll 0% 0%; height: 11.25pt; font-size: 8pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" height="15">Henri Weltersstraat 70b</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl63" style="border-style: none solid solid; border-color: -moz-use-text-color white white; border-width: medium 0.5pt 0.5pt; background: rgb(184, 204, 228) none repeat scroll 0% 0%; height: 11.25pt; font-size: 8pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" height="15">Stationsweg 60 A</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl63" style="border-style: none solid solid; border-color: -moz-use-text-color white white; border-width: medium 0.5pt 0.5pt; background: rgb(219, 229, 241) none repeat scroll 0% 0%; height: 11.25pt; font-size: 8pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" height="15">Proost de Beaufortstraat 1</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl63" style="border-style: none solid solid; border-color: -moz-use-text-color white white; border-width: medium 0.5pt 0.5pt; background: rgb(184, 204, 228) none repeat scroll 0% 0%; height: 11.25pt; font-size: 8pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" height="15">Ovidiusstraat 117</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl63" style="border-style: none solid solid; border-color: -moz-use-text-color white white; border-width: medium 0.5pt 0.5pt; background: rgb(219, 229, 241) none repeat scroll 0% 0%; height: 11.25pt; font-size: 8pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" height="15">Ganzeweide 71</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl63" style="border-style: none solid solid; border-color: -moz-use-text-color white white; border-width: medium 0.5pt 0.5pt; background: rgb(184, 204, 228) none repeat scroll 0% 0%; height: 11.25pt; font-size: 8pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" height="15">Kerkstraat 46</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl63" style="border-style: none solid solid; border-color: -moz-use-text-color white white; border-width: medium 0.5pt 0.5pt; background: rgb(219, 229, 241) none repeat scroll 0% 0%; height: 11.25pt; font-size: 8pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" height="15">Kampstraat 45</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl63" style="border-style: none solid solid; border-color: -moz-use-text-color white white; border-width: medium 0.5pt 0.5pt; background: rgb(184, 204, 228) none repeat scroll 0% 0%; height: 11.25pt; font-size: 8pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" height="15">Wilhelminasingel 237</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl63" style="border-style: none solid solid; border-color: -moz-use-text-color white white; border-width: medium 0.5pt 0.5pt; background: rgb(219, 229, 241) none repeat scroll 0% 0%; height: 11.25pt; font-size: 8pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" height="15">Pater van den Elsenplein 90</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl63" style="border-style: none solid solid; border-color: -moz-use-text-color white white; border-width: medium 0.5pt 0.5pt; background: rgb(184, 204, 228) none repeat scroll 0% 0%; height: 11.25pt; font-size: 8pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" height="15">A Ge Water 20</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl63" style="border-style: none solid solid; border-color: -moz-use-text-color white white; border-width: medium 0.5pt 0.5pt; background: rgb(219, 229, 241) none repeat scroll 0% 0%; height: 11.25pt; font-size: 8pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" height="15">Zeswegenlaan 183</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl63" style="border-style: none solid solid; border-color: -moz-use-text-color white white; border-width: medium 0.5pt 0.5pt; background: rgb(184, 204, 228) none repeat scroll 0% 0%; height: 11.25pt; font-size: 8pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" height="15">Dorien Verschureplein 10b</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl63" style="border-style: none solid solid; border-color: -moz-use-text-color white white; border-width: medium 0.5pt 0.5pt; background: rgb(219, 229, 241) none repeat scroll 0% 0%; height: 11.25pt; font-size: 8pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" height="15">Oude raadhuis 10</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl63" style="border-style: none solid solid; border-color: -moz-use-text-color white white; border-width: medium 0.5pt 0.5pt; background: rgb(184, 204, 228) none repeat scroll 0% 0%; height: 11.25pt; font-size: 8pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" height="15">Kruisstraat 39</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl63" style="border-style: none solid solid; border-color: -moz-use-text-color white white; border-width: medium 0.5pt 0.5pt; background: rgb(219, 229, 241) none repeat scroll 0% 0%; height: 11.25pt; font-size: 8pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" height="15">Van Lennepstraat 4</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl63" style="border-style: none solid solid; border-color: -moz-use-text-color white white; border-width: medium 0.5pt 0.5pt; background: rgb(184, 204, 228) none repeat scroll 0% 0%; height: 11.25pt; font-size: 8pt; color: black; font-weight: 400; text-decoration: none; font-family: Arial; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" height="15">Burgemeester Waszinkstraat 2</td> </tr> </tbody></table>
What I am loooking for is:

<table style="border-collapse: collapse; width: 129pt;" width="172" border="0" cellpadding="0" cellspacing="0"><col style="width: 81pt;" width="108"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 11.25pt;" height="15"> <td class="xl65" style="height: 11.25pt; width: 81pt;" width="108" height="15">Adress</td> <td class="xl65" style="width: 48pt;" width="64">Number</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl66" style="height: 11.25pt;" height="15">Wilhelminastraat </td> <td class="xl65" align="right">78</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl67" style="border-top: medium none; height: 11.25pt;" height="15">Kampstraat</td> <td class="xl65" align="right">45</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl66" style="border-top: medium none; height: 11.25pt;" height="15">Kohlbergsgracht</td> <td class="xl65" align="right">1</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl67" style="border-top: medium none; height: 11.25pt;" height="15">Jos Klijnenlaan </td> <td class="xl65" align="right">23</td> </tr> </tbody></table>
Thank you in advance!
Xander

 

[Peter_SSs]

See if this does what you want.

Excel Workbook
	A	B	C	D
1	Address
2	Wilhelminastraat 78	Wilhelminastraat	78
3	Kampstraat 45	Kampstraat	45
4	Kohlbergsgracht 1	Kohlbergsgracht	1
5	Jos Klijnenlaan 23	Jos Klijnenlaan	23
6	Beekstraat 4	Beekstraat	4
7	Akerstraat Noord 74	Akerstraat Noord	74
8	Veeladingstraat 14	Veeladingstraat	14
9	Becamusstraat 15	Becamusstraat	15
10	Wilhelminalaan 1	Wilhelminalaan	1
11	Wijngracht 51	Wijngracht	51
12	Raadhuisstraat 21	Raadhuisstraat	21
13	Henri Weltersstraat 70b	Henri Weltersstraat	70	b
14	Stationsweg 60 A	Stationsweg	60	A
15	Proost de Beaufortstraat 1	Proost de Beaufortstraat	1
Separate Text

 

[xandertio]

Hi Peter,

Thank you for your help. I have managed to create a file that can be imported. This is saving me a lot of time

Thanks again,
Xander