Easily splitting one column into two or more columns at the separator(s) of your choice, in OpenOffice.org Calc spreadsheets

I've been asked if this feature, available in Excel, exists in Calc. I didn't see it til a reader pointed it out. Here it is!

Let's say that you have some nine-digit zip codes, and you'd like to split them into two columns, the regular zip and the extension. The following illustration shows both. (And this post shows other ways to split them and/or deal with sorting them correctly.)

Select the column and choose Data > Text to Columns.

This is what the window looks like by default.

Now you need to specify at what character you want Calc to go "Hey, time for a new column!" In this case it's the dash. It's not listed so you just deselect Tab, select Other, and type the dash. Then scroll through the data and you'll see how it's going to look if you click OK now.

Click OK, and you get exactly what you want. (Note that you could split a column into two or more, splitting a social security number column would give you three columns because it has two dashes, 333-44-5555.)

If  you have phone numbers, you can use this to split the columns twice, once at ) and then once at -.  (You would use Edit > Find and Replace to get rid of the ( at the beginning of the area code.)

Phone number data:

Data > Text to Columns brings up the regular window where, the first time, you specify ) as the separator.

Click OK and you get this.

 

Then just do it again on the column containing the phone number with the - and split that at the - the same way we split the zip codes earlier in this post.

Comments

[hawkse]

For the phone number example, you can split at once without having to do find & replace. Just enter () as your separator and you're done.

[hawkse]

Even simpler (if you want to split at the - too). Just enter ()-.

[Solveig]

Excellent! Thanks, hawkse. Will update.

[Jerry Montoliang]

Excellent, it's make easy to all my work. Thanks