| View previous topic :: View next topic |
| Author |
Message |
linlin
General User
Joined: 19 Sep 2010
Posts: 13
|
 Posted: Tue Mar 08, 2011 2:40 am Post subject: postal-code and town in one column - how to divide into two |
 |
|
Hello dear community
how can i separate the combination of postal code and town - they are stored in one column
| Quote: | PLZ und Stadt
2700 Wiener Neustadt
4822 Bad Goisern am Hallstättersee
5310 Mondsee
1190 Wien
6712 Thüringen
1070 Wien
8786 Rottenmann
4910 Ried im Innkreis
4120 Neufelden
4663 Laakirchen
3390 Melk
3204 Kirchberg an der Pielach
3580 Horn
5611 Großarl
6952 Hittisau
5142 Eggelsberg
2700 Wiener Neustadt
4160 Schlägl
4222 Sankt Georgen an der Gusen
4973 Sankt Martin im Innkreis
4060 Leonding
4982 Obernberg am Inn
4360 Grein
8271 Bad Waltersdorf
8350 Fehring
4924 Waldzell
4273 Unterweißenbach
2424 Zurndorf
3370 Ybbs an der Donau
3150 Wilhelmsburg
4580 Windischgarsten
2811 Wiesmath
8250 Vorau
8141 Unterpremstätten
5350 Strobl
3160 Traisen
8345 Straden
2320 Schwechat
8323 Sankt Marein bei Graz
8970 Schladming
7071 Rust
2070 Retz
2651 Reichenau an der Rax
3380 Pöchlarn
3385 Prinzersdorf
3002 Purkersdorf
2170 Poysdorf
7423 Pinkafeld
2763 Pernitz
5121 Ostermiething
3390 Melk
8480 Mureck
8630 Mariazell
3382 Loosdorf
6652 Elbigenalp
3180 Lilienfeld
3550 Langenlois
2851 Krumbach
3400 Klosterneuburg
3470 Kirchberg/Wagram
2860 Kirchschlag in der Buckligen Welt
8324 Kirchberg an der Raab
8380 Jennersdorf
|
look forward to tipps.  |
|
| Back to top |
|
 |
jrkrideau
Super User
Joined: 08 Aug 2005
Posts: 6733
Location: Kingston ON Canada
|
| Posted: Tue Mar 08, 2011 3:02 am Post subject: Re: postal-code and town in one column - how to divide into |
|
|
| linlin wrote: | Hello dear community
how can i separate the combination of postal code and town - they are stored in one column
| Quote: | PLZ und Stadt
2700 Wiener Neustadt
4822 Bad Goisern am Hallstättersee
5310 Mondsee
1190 Wien
6712 Thüringen
1070 Wien
8786 Rottenmann
4910 Ried im Innkreis
4120 Neufelden
4663 Laakirchen
3390 Melk
3204 Kirchberg an der Pielach
3580 Horn
5611 Großarl
6952 Hittisau
5142 Eggelsberg
2700 Wiener Neustadt
4160 Schlägl
4222 Sankt Georgen an der Gusen
4973 Sankt Martin im Innkreis
4060 Leonding
4982 Obernberg am Inn
4360 Grein
8271 Bad Waltersdorf
8350 Fehring
4924 Waldzell
4273 Unterweißenbach
2424 Zurndorf
3370 Ybbs an der Donau
3150 Wilhelmsburg
4580 Windischgarsten
2811 Wiesmath
8250 Vorau
8141 Unterpremstätten
5350 Strobl
3160 Traisen
8345 Straden
2320 Schwechat
8323 Sankt Marein bei Graz
8970 Schladming
7071 Rust
2070 Retz
2651 Reichenau an der Rax
3380 Pöchlarn
3385 Prinzersdorf
3002 Purkersdorf
2170 Poysdorf
7423 Pinkafeld
2763 Pernitz
5121 Ostermiething
3390 Melk
8480 Mureck
8630 Mariazell
3382 Loosdorf
6652 Elbigenalp
3180 Lilienfeld
3550 Langenlois
2851 Krumbach
3400 Klosterneuburg
3470 Kirchberg/Wagram
2860 Kirchschlag in der Buckligen Welt
8324 Kirchberg an der Raab
8380 Jennersdorf
|
look forward to tipps. |
Use three text functions Assuming data starts in A1
Since the postal code is always 4 digits then =LEFT(A1;4)
Calculate the length of the rest of the cell =LEN(A1)
To get address
=RIGHT(A1;E1-5)
the length funtion was in E1 and I subtracted 5 to avoid the postal code and the space after it.
I hope this helps
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2 |
|
| Back to top |
|
|
ftack
Moderator
Joined: 27 Jan 2003
Posts: 3102
Location: Belgium
|
| Posted: Tue Mar 08, 2011 3:49 am Post subject: |
|
|
This could also be done with Data - Text to columns.
_________________
--
GNU/Linux (Ubuntu) |
|
| Back to top |
|
|
linlin
General User
Joined: 19 Sep 2010
Posts: 13
|
| Posted: Tue Mar 08, 2011 5:54 am Post subject: |
|
|
Hello
| ftack wrote: | | This could also be done with Data - Text to columns. |
hello many thanks
can this be done with the Data-> Text to Columns those ones here:
| Quote: | 2320 Schwechat
8970 Schladming
7071 Rust
2070 Retz
|
as well as with those ones here: (note - they look a bit different!!!!
| Quote: |
2651 Reichenau an der Rax
8323 Sankt Marein bei Graz
2860 Kirchschlag in der Buckligen Welt
8324 Kirchberg an der Raab |
Look forward to hear from you
regards
linlin[/i] |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Tue Mar 08, 2011 6:02 am Post subject: |
|
|
FIND(" ";A1) returns a number which is the position of the space counting from position #1 (or #VALUE when there is no space).
LEFT(A1;FIND(" ";A1)-1) gets the left part before that position.
MID(A1;FIND(" ";A1)+1);LEN(A1)) gets all the rest behind that position.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
ftack
Moderator
Joined: 27 Jan 2003
Posts: 3102
Location: Belgium
|
| Posted: Tue Mar 08, 2011 12:00 pm Post subject: |
|
|
| linlin wrote: |
Look forward to hear from you
|
It sounds as if you did not yet try for yourself. What are you waiting for? The use of formulas as Villeroy pointed out is another option.
_________________
--
GNU/Linux (Ubuntu) |
|
| Back to top |
|
|
linlin
General User
Joined: 19 Sep 2010
Posts: 13
|
| Posted: Wed Mar 09, 2011 3:15 am Post subject: |
|
|
hi there
many thanks _ i am not at home at the moment. i do try out all.
i come back and report all the findings
many thanks |
|
| Back to top |
|
|
|
