OpenOffice.org Training, Tips, and Ideas

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

Really good gift ideas

It's that time of year again: the time when I promote my, if I do say so myself, pretty good gift ideas guide. It's a book for men who are buying gifts for women. (Or for anyone who needs to buy gifts for women and wants some help coming up with ideas.)

If you want some ideas for a really great gift idea for your wife or girlfriend, or you'd just like help figuring out what book or music your sister would like, then this will be a huge help for you.

Plus, it's just $3 for the PDF download!

It starts off with basics. Music and books and videos are good ideas, but how do you pick which ones? If you need a gift for someone who always goes out and buys the latest Lyle Lovett and Buffy the Vampire Slayer items the minute they're released, this can be a challenge. The book goes over how to solve this problem.

There's also the eternal issue of how a man can be successful buying clothes or jewelry for a woman. Style? Size??? You'll get guidance from this book on both.

If you're looking to be socially responsible through your gift-giving, the book provides lots of links to artisans who make stuff your honey would actually like to receive.

And if you and your honey both have enough Stuff, but you still want to give a gift, what do you do? The book provides all sorts of non-Stuff gift ideas, starting with but going way beyond the classic weekend getaway in the mountains.

Using the "Format > Default Formatting" feature in presentations

The best approach to formatting in OpenOffice.org presentations is to let all the formatting flow from the template.

If you do manual formatting on some words in the presentation, then that will stick when you switch or modify the template. Then you'll be stuck with a few red headings, or other formatting that doesn't look good in the new or modified template.

Here's what you do to remove all that manual formatting and let the template formatting flow through to every part of every slide.

1. Click the Outline tab at the top of the work area.
2. Click in the text area of the Outline tab and Select All.
3. Choose Format > Default Formatting.

And there you are. All the manual formatting is gone, and your presentation adapts to whatever template you're on.