[Solved] Separate adress-chunk: 3 columns instead of 1

Discuss the spreadsheet application

[Solved] Separate adress-chunk: 3 columns instead of 1

Postby lin » Fri Mar 04, 2011 11:45 pm

hello dear open-Office-community. Hello dear friends form all over the planet

first of all - this is a great great forum. I like this place to share the ideas. It is so great to see such a supportive plate - featuring the great Open-Office (or LibreOffice).

today i have the following thing to discuss:

i want to separate the following data that are stored in one column of a calc-spreadsheet:
See the following data: You see that there are the following categories:_

street,
postal-code,
town

All the data are stored in only one Colum; Well to be honest: i want to separate them into three colums
street,
postal-code,
town

see the data: what can i do?

note - you see that there are commas inbetween the enties: and besides this we see that we have
a postal-code with four digits: that is a good thing. Perhaps we can use this as a marker that helps us to
separate the data?! Perhaps

See a data-sample! Here you can see some exceptions: eg. the town that has two words combinde with a "-" ... or somethims without any signs and characters...

see the following... as an example:

Code: Select all   Expand viewCollapse view
Max-Bader-Platz 1, 5620 Schwarzach im Pongau
Pestalozzistraße 4, 9990 Nussdorf-Debant
Schulstraße 4, 5162 Obertrum am See

But i guess that this means no problem... What do you think about this? I am very very interested to get your opinion!

i look forward to hear from you!

regards
lin ;-)

see a snipped of the dataset - that is stored in one column -Goal: i want to separate the datas into three collumns... :

Code: Select all   Expand viewCollapse view
Schulweg 6, 9871 Seeboden
Khevenhüllerstraße 45, 4861 Schörfling
Franz Xaver Rennstr.18, 6460 Imst
Schulstraße 4, 5162 Obertrum am See
Schulweg 6, 7432 Oberschützen
Pestalozzistraße 4, 9990 Nussdorf-Debant
Niederndorf bei Kufstein 53c, 6342 Niederndorf bei Kufstein
Hauptschulstraße 18, 2183 Neusiedl an der Zaya
Seeweg 14, 5202 Neumarkt am Wallersee
Europaplatz 1, 8820 Neumarkt in Steiermark
Schulstraße 7, 4212 Neumarkt im Mühlkreis
Schulstraße 20, 4720 Neumarkt im Hausruckkreis
Bahnhofstr. 10, 4872 Neukirchen an der Vöckla
Schulstraße 5b, 4780 Schärding
Reitbergstraße 2, 4311 Schwertberg
Europaplatz 1, 2320 Schwechat
Am Schulberg 5, 3931 Schweiggers
Waidach 8, 6130 Schwaz
Waidach 8, 6130 Schwaz
Max-Bader-Platz 1, 5620 Schwarzach im Pongau
Markt 29, 2662 Schwarzau im Gebirge
Hofsteigstraße 68, 6858 Schwarzach
Gmundner Straße 7, 4690 Schwanenstadt
Mühlfeldstraße 1, 4690 Schwanenstadt
Mainsdorferstraße 18, 8541 Schwanberg
Jakob Stemer-Weg 3, 6780 Schruns
Obere Umfahrungsstraße 16, 2432 Schwadorf bei Wien
Battloggstraße 54, 6780 Schruns
Schloss-Straße 19, 5020 Salzburg
Schillerplatz 2, 8280 Fürstenfeld
Erzherzog-Johann-Str. 400, 8970 Schladming
Schulgasse 261, 8811 Scheifling

i look forward to hear from you!!
Last edited by Hagar Delest on Sat Mar 05, 2011 3:03 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 3.3 on OpenSuse Linux 11.3
lin
 
Posts: 32
Joined: Wed Jun 23, 2010 6:33 am

Re: Separate adress-chunk: 3 columns instead of 1

Postby acknak » Sat Mar 05, 2011 1:14 am

You can split them up easily using Data > Text to Columns.

You'll have to use two steps: first, split on the comma, then split the second part using the "Fixed width" option. Since all the postal codes are four digits, you can split all the entries after the code.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Separate adress-chunk: 3 columns instead of 1

Postby lin » Sat Mar 05, 2011 1:19 am

hi there

many many thanks - i will do that.

Again thx to you for the quick answer!

greetings
lin
OpenOffice 3.3 on OpenSuse Linux 11.3
lin
 
Posts: 32
Joined: Wed Jun 23, 2010 6:33 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 26 guests