OpenOffice.org Training, Tips, and Ideas

Getting rid of the blanks in a spreadsheet column or row, and more

Note: As Karl pointed out in a comment, this is actually a complex solution. It is a good solution for two or more columns, but if you have one column as shown here, the simplest approach is to just sort the column and the blanks and cells with one space in them will be grouped together. Delete those cells and you get the same result.

I'm going to a big blues dance event this weekend. The people signed up are listed on EventBrite, so since I wanted a list of people coming, I copied the list and pasted it into OpenOffice. But when I pasted the list to a spreadsheet, I got a bunch of blank cells. I want to have a nice singlespaced list of the names.

 

 By the way, Edit > Paste Special, Skip Empty Cells, won't do what I want. It's a badly phrased option and has to do with whether you overwrite when pasting.

Anyway--so to remove the empty cells I selected the column and chose Data > Filter > Standard Filter. I made sure that the Name column was selected, left = selected, and selected Not Empty.

 

I clicked OK and....voila. Voi not, that is. 

That pesky EventBrite site puts in a space, just a single space, in some but not all of the empty lines. Why? Well, why not. I discovered this by clicking in the remaining seemingly empty cells, and yep, there was a blank space.

 

So now I need to screen out not only stuff that's not empty, but stuff that doesn't have a space as the only content. That's a little hard....and I don't want to search and replace to remove spaces, since then I'll end up with TrentPrice instead of Trent Price.

I went back to Data > Filter > Standard Filter and filtered out everything that STARTS with a single space. 

 

And that worked great.

 

Free Microsoft Office templates for Word, for use with OpenOffice Writer

As I posted yesterday, you can use OpenOffice.org with most of the templates out there. Writer/Word, Calc/Excel, and Impress/Powerpoint. Also remember that OpenOffice opens .docx files without any extra help.

So google away, find your Word or Excel template, download it, and follow the steps in yesterday's post to save it in OpenOffice template format in your own template collection. Here's one of many sites. http://www.docstoc.com/

Here's the only difference: it's after you've saved it. Instead of being able to access it the way you do with Impress, you choose File > New > Templates and Documents to get it.

 

Then you'll see a window with all your templates. It might be in a folder of templates like My Templates or it might be at the top of all your folders. You can tell by the info in the title bar. The template will be in the folder you picked when you chose File > Templates > Save. Then just double-click and you're good.

 
 

Quick new presentation templates to use in OpenOffice Impress

As you know, OpenOffice doesn't come with the dazzling depth or array of templates that Microsoft Office users get. However, here's the thing. 

A) Templates aren't software. You can get great free software, without many templates, then go out and get the templates separately, also for free.

B) You can use Microsoft Office format templates to your heart's content in OpenOffice.

So here's what I just did.

1. Googled free Microsoft Office templates.

2. Picked this of the many results. http://www.presentationmagazine.com/free_powerpoint_template.htm

3. Downloaded this one (a dear friend just got married to the man I set her up with -- it seems an appropriate choice. :) )  http://www.presentationmagazine.com/wedding_powerpoint_template.htm

4. From OpenOffice, chose File > Open and opened the downloaded file.

 

5. Now, I can just use it, save it as a presentation, do whatever I want at this point. However, if I want it in that nice little catalog of templates that pops up when I create a new presentation....

I choose File > Templates > Save and name it, then click OK.

 

6. And now (without restarting or anything) when I choose in OpenOffice File > New > Presentation, that wedding template is right there in My Templates.

 

Three nice new features for Writer, in OpenOffice.org 3.1

I'm going to be going over the 3.1 features in the next few blogs. Here are a few that are just nice -- not groundbreaking but very nice, convenient changes that making interacting and collaborating easier. The third just makes slight annoyances go away a little more easily.

Replying to Notes
In 3.0 the notes are new and improved by being in boxes off to the side. Now you can click and hold down on the black arrow, and reply to them, too. The reply shows up immediately below the original in a contrasting color. (You need to be a different user, under Tools > Options > OpenOffice.org > User Data for this option to be available.)

Easier Accept/Reject of Changes
It's also easier now in Writer to accept or reject a change when you're tracking changes. Just right-click (as I like to say, when in doubt, right-click).

Remove Hyperlink
You could always remove a hyperlink by selecting the text and choosing Format > Default Formatting, or right-clicking on the selected link and choosing Default Formatting. However, this makes it more obvious. Right-click without selecting and you get Remove Hyperlink as an option, as well as Copy Hyperlink.

---

---

OpenOffice.org 3.1 is out!

Lots of really great features--check it out!

http://www.openoffice.org/dev_docs/features/3.1/

How to take a spreadsheet that has firstname, lastname, and initial all in the same column, and turn it into two columns

Sometimes your data isn't as broken down, as granular, as you'd like.

Name

Hanson,John B

Herman,Jill

Jenson,Jim J

What you typically want, for the most power over your data, is this.

Lastname	Firstname
Hanson	John B
Herman	Jill
Jenson	Jim J

Here's how to split it automatically, not manually.

First, you need to take out that space between the comma and the first name. Select all the cells in that name column.

Choose Edit > Find and Replace. Type a comma immediately followed by a space in the Search For field and just type a comma in the Replace With field. Click More and select Current Selection Only.

Then click Replace All.

And you'll see the data looks like this.

 

Now you're ready to split it at the comma. First, be sure that you have an empty column to the right of the Name column, to put the first name and initial into. Then click on the column header to select the whole column.

Choose Data > Text to Columns.

Select the comma, but nothing else. You'll see a preview of how it will be split.

Then click OK and you'll see the actual data split.

How to specify fieldname Not Equal To fieldname in an OpenOffice.org Base query

Let's say that you have a whole bunch of data. You're the head of HR and you have thousands of employees. You want to figure out which ones you need to talk to about new benefits because they've changed their health insurance plan from one year to another. You just need to figure out which are the ones who changed. How do you do that?

You create a query that says "find me all the people for whom Plan08 field does not equal Plan09".

OK, good. But what's the syntax?

Design View

<>  is how you say not equal

and then you just put square brackets around the fieldname.

so in the field for Plan08 you just put   <>[Plan09]

Here's some example data. You need to have the fields set up so they have the value for each employee's choice for health plan for 08, and then again for what they're switching to for 09.

 

The data needs to be in or connected to a database file, an ODB file.

Then you open your database .odb file containing that data, and you click the Queries icon at the side.

You have to create the query in design view or in SQL view, you can't use the wizard.

In design view it's as I showed before, and in SQL it's just "Plan08" <> "Plan09".

Here's how it looks in design view.

 

and here's the results when you click the green-checkmark Run Query icon.

Whoohoo! An outline numbering level attribute as part of the paragraph style definition

Just got this email from the new features mailing list. It makes me very happy. Just define your styles, and you don't have to go into Outline Numbering to set which level it's used at. This also begs the question, can we finally use two or more headings at the same level? That would be huge for many reasons.

(Now all we need is to have a Restart Numbering attribute in the paragraph or numbering style definition windows (it's in the formatting window but not the equivalent style definition window) and I will be perfectly happy. Self-actualization will be just around the corner.)

 Product: Word Processing
   Type: new
   Title: Introduction of an outline level attribute for paragraphs and paragraph styles
  Posted by: [email protected]
  Affected: sw, xmloff
   TaskId: i70748
<http://www.openoffice.org/issues/show_bug.cgi?id=70748>
Effective from: cws outlinelevel
   CWS:
<http://eis.services.openoffice.org/EIS2/cws.ShowCWS?Path=DEV300/outlinelevel>
 CWS status: ready for QA


*Flags*
-------
API/ BASIC [x]
Configuration [ ]
File format change [ ]
Help/ Guide [x]
Performance test [ ]
Translation [x]
UI relevant [x]


*Description*
-------------
Introduce a new paragraph and paragraph style attribute, named
“outline level”, to transform a normal paragraph directly and
independently from any certain list style or paragraph style into a
heading.

*Specification URL*
-------------------
http://specs.openoffice.org/writer/numbering/OutlineLevel.odt


Send feedback to [email protected]

How to generate an email address from firstname and lastname

Let's say that you work at one of those companies that has a nice leftover-from-the-8-character-file-name-limit-days email format. Solveig Haugland becomes shauglan, Bob Nelson becomes bnelson, and so on.

And let's also say, as long as we're dreaming up situations, that you've got a spreadsheet full of first and last names but you don't have their emails.

You could type in all the email addresses but even an intern who's paid well by the hour is going to balk at that.

The faster way is to use CONCATENATE and LEFT.

You've got this.

Firstname	Lastname	Email
Shelly	Nelson
Bill	Mizrahi
Steve	Santos

You want this.

Firstname	Lastname	Email
Shelly	Nelson	[email protected]
Bill	Mizrahi	[email protected]
Steve	Santos	[email protected]

Here's how to get it.

Syntax

=CONCATENATE(LEFT(cell_with_firstname;1);LEFT(cell_with_lastname;7);"@yourdomain")

Example

Simple labels in OpenOffice Writer 2.0 or 3.0: Just typing or pasting the content

Here's how to just print out a few labels very simply.

I follow this procedure when I ship out books. Let's say I've got five people who've ordered books directly from me through PayPal or Amazon. I've got five emails in my inbox, with five addresses. So I need to make five labels.

I describe the simple but more labor-intensive way in the following steps. Then at the end I show what I actually do that adds a little bit of one-time extra setup and makes the whole process much simpler.

1. Choose File > New > Labels.* (See note at end.)

In this window, look at the two lists in the lower right corner. Pick the right layout, usually Avery Letter, and the kind of labels you're using (the number is on the box or envelope) like 8160.

2. Click the Options tab and make sure that the Synchronize checkbox is NOT marked.

3. Click New Document.

4. You'll see the new empty label document.

5. Now just type the content you want in each box. OR copy and paste. In my example, I copy the address from my email and paste it into the box.

If you've already used up some of the labels on the physical sheet you're going to print onto, then just start pasting or typing in the next available box.

If you have trouble clicking in a box to type, click somewhere in the blank margin around the area where the boxes are, then click in the middle of the box. You'll then see a normal blinking cursor and you'll be able to type.

6. To format the text differently, you could do a few different things. Pick any of the following; I recommend b or c.

a - Just format every text box the same, manually.

b - Format the first text box how you want it, and select some of the formatted text. Then DOUBLE-CLICK the Paintbrush icon.

Select every additional text box with text to format the same way.

Then CLICK the Paintbrush icon again and you're done.

c - Format the first text box how you want it. With some formatted text selected, choose Format > Styles and Formatting. Click and hold down on the far right icon and choose Update Style. All the other boxes will be formatted the same way.

7.  Now just stick the label sheet in your printer and print. If you want more than one sheet of labels, then just print out these, then type the new addresses over the old addresses and print a second sheet. (Or save this sheet, then choose File > Save As and save the sheet under a different name, and type the second set of addresses over the old addresses.)

* NOTE: What I actually do instead of steps 1-4 and 6 is open the label template that I created. I went through this entire process, then formatted the text the way I wanted it. I formatted the text in the first label box the way I wanted it. Then I choose Format > Styles and Formatting, I clicked and held down the mouse on the far right icon, and chose Update Style, as in step 6-c above.  After that, I chose File > Templates > Save. I named the template Labels and clicked OK.

That's what I did to create the template the way I wanted it. Then instead of step 1, I choose File > New > Templates and Documents, and pick Labels from my list of templates.

---

---

Notes in OpenOffice.org 3.0 Writer

Notes are easy to use, easy to format, and really, really easy to read, in 3.0. Printing is the same, so not that great but not less than what we had.

You start out the same way. Choose Insert > Note.

 

You get a box, and you just type. Note two things: first, just the note box there which is just what you'd expect, but also check out the circled icons. Those icons, the enabled ones such as font and bold/italic/underline, are available to use when formatting the note.

 

  

Then you finish formatting, and you could have something plain, or something as silly as this.

 

  

If you click on the little black triangle in the lower right corner you get a few options.

  

  

And you get those options plus formatting options if you just right-click somewhere in the text of the note.

 

 

To print notes, just File > Print, and click the Options button. You can print the notes where you want, at the end or after each page. You can also print only notes.

 

Using the zoom to view 2+ pages in the editable view, in OpenOffice.org Writer 3.0

Here's another nice new 3.0 feature. You can view pages side-by-side in editable mode, up to three.

Here's a normal page.

 

Here are the controls, in the lower right corner, for viewing side by side and zooming. The icons are Single, Double, and Double-with-first-page-on-right-side (more on that later).

You can drag the little slider bubble in the middle and drag it from right to left and back to control magnification. The vertical lines indicate how far you have to be for one, two, and three pages at time.

And the 100% zoom is the regular approach to zoom; right-click on it or double-click it to get different magnification choices.

 

Here's what you get. Please click each image to see it bigger. When you have the icons set this way, with the single icon showing, the little slider dot to the right of the second line, and magnification at 100%, you only have room to see one page at a time, and even if it were smaller, it would only show one at a time.

 

I click the Double Page icon and I drag the little slider dot to the left.  It gets smaller but that's all. That's because the slider dot is still to the right of the first vertical line.

 

Now I drag the slider dot to the left of the first vertical line. The Double Page icon is also still marked, and I get two pages side by side. This is in the low 60% for zoom.

 

Now see what I get when I drag the slider dot to the left of the second vertical line. There are three pages displayed across.

 

Now when I click the Double-Pages-But-With-The-First-Page-On-The-Right, the right-hand of the three icons, and drag the slider back a little, we get this. This view is great for book production, when the first page, an odd page, is always on the right side. (Take a look at any printed book and you'll see the odd pages are on the right.)

 

OpenOffice.org 3.0 is out!

Reviews
http://www.pcmag.com/article2/0,2817,2332502,00.asp

Download
http://www.openoffice.org/

Updated the 10th: An Impress PDF presentation on OpenOffice.org 3.0 features

I created this presentation  for a client, about the 3.0 features in OpenOffice.org. I'm going to do a more detailed article but I thought I would post this since I've done it. Here's the solver.ods spreadsheet you can use to fiddle with.

I got pretty excited about some of them, especially the 3-up layout with lines already in there, and the far easier Impress handout printing.

And for those who like a good cross-reference, you don't need to create them first; you can just point to a heading in a list and select it, to make the cross-reference.

Also very exciting is the PDF editing, which does some very Adobe Acrobat type things. It's in an extension you can get here.

In the words of Douglas Adams, share and enjoy!