Friday, August 5, 2011

Excel String Functions and Other Tips

Here are a couple of functions that you can use to manipulate strings in excel. Note that there are many other string functions available in Excel, but the next presented are most common.

1. PROPER(text) - capitalize first letter of each words from a text
For example:
cell A1: "test text"
cell A2: =PROPER(A1) would return "Test Text"

2. UPPER(text) - upper each char from each word from a text
For example:
cell A1: "test text"
cell A2: =UPPER(A1) would return "TEST TEXT"

3. LOWER(text) - lower each char from each word from a text
For example:
cell A1: "test TEXT"
cell A2: =LOWER(A1) would return "test text"

4. LEFT(text, number_of_characters) - return the first number_of_characters characters from text text
For example:
cell A1: "test TEXT"
cell A2: =LEFT(A1, 3) would return "tes"

5. RIGHT(text, number_of_characters) - extracts a substring from a string starting from the right-most character
For example:
cell A1: "test TEXT"
cell A2: =LEFT(A1, 3) would return "EXT"

6. LEN(text) - returns the length of the specified string
For example:
cell A1: "test TEXT"
cell A2: =LEN(A1) would return "9"


Now here are a couple of special combinations of the above functions to achieve different results:

1. Upper first character from a text and let the rest of them unchanged
For example:
cell A1: "test text"
cell A2: =UPPER(LEFT(A1;1))&RIGHT(A1;LEN(A1)-1) would return "Test text"

2. Split a string into multiple columns using a token (string tokenizer)
2.1. Select the column or cell that you want to split
2.2. Make sure that you have enough blank columns in the right
2.3. Go to Excel menu -> Data -> Data Tools -> Text to Column
2.4. From the Wizard that appears select Delimited option and click Next button
2.5. In the next Wizard window select Delimiter -> Other: -> enter your own delimiter
2.6. Click Next to preview the data resulted and then Finish
For example:
cell A1: "test - text"
Follow the above steps and select the "-" delimiter
Would result:
cell A2: "test "
cell A3: " text"

Thursday, June 16, 2011

Setting Up A Virtual Host in Apache

Yesturday I installed an web application that required a valid domain name, something like (http://localhost/my_site was not allowed).
The question was how can we provide a valid domain name to this application, which is installed on the local machine, assuming that we don't have bought an domain name?
The answer for this question is that we set up a virtual host which points to which is the localhost.
If we presume that we run Apache server on Windows machine, here are the short steps of how to do this virtual host:

1. Configuring Apache
Locate and edit Apache httpd.conf file by adding the next lines at the end of the file (e.g. apache_home_folder/conf/httpd.conf):

<virtualhost 1="">
  DocumentRoot "C:\My Sites\Site1"

Note 1: If the above path for DocumentRoot does not have any spaces in it, do not quote the path.

Note 2: Starting with Apache 2.2, the above modifications should be made in httpd-vhosts.conf file, which will be located in the extra folder (e.g. apache_home_folder/conf/extra/httpd-vhosts.conf). Also make sure that the line:

Include conf/extra/httpd-vhosts.conf

is uncommented in httpd.conf file.

It is also needed to add the next lines to httpd-vhosts.conf file:

<directory my="">
  Order Deny,Allow
  Allow from all

then add the NameVirtualHost ... lines.

2. Resolving the DNS issue
Locate the file




At the end of this file add the following line:

and save the file.

If you cannot save the file, run the editor with administrator permissions (right click on the editor and select Run as Administrator option) and then open the file in the editor.

3. Restart Apache server
All you have to do now is to restart the Apache server and test the virtual host in your browser:


Saturday, May 21, 2011

How to replace a key on Acer Notebook Keyboard

No meter how careful you are with your laptop, sooner or later you will find your keyboard filled with dust, lint or even small pieces of food. Sometimes, all you have to do to clean your keyboard is to turn your laptop on one side and blow. But sometimes, dirt is sticking beneath a key and no meter how hard you will blow, the dirt will not easily go away. The annoying part is that the key with dirt will not work properly anymore until is cleaned.
In this post I will show you step by step how to remove, clean and put back a key from an Acer Notebook keyboard (Aspire 7736Z).

1. First, search for a small screwdriver. A tweezers is optional but can help.
I will exemplify on "J" key.

2. This step is the most important one and it will require you to handle the screwdriver very carefully.
The key have tow main pieces: the plastic part with letter on it (the part that you can see) and beneath an "X" mechanism. Gently insert the screwdriver beneath the hey, between the part with letter on it and "X" mechanism.

3. Now gently rotate the screwdriver until the part with letter on it detach from "X" mechanism. The part with letter on it have to detach from tow points.

Here are those tow connection points between part with letter on it and "X" mechanism.

4. The hardest step was to remove the part with letter on it. After that, you may also need to remove the "X" mechanism. The "X" mechanism is attached to the keyboard in three contact points. With the same small screwdriver detach the "X" mechanism from tow of the three contact points.

5. Now you can start the cleaning.

6. After you finished the cleaning, follow the steps back to the begining.
First connect the "X" mechanism to the lower connection point from the keyboard.

Then connect the "X" mechanism to the other tow connection points from the keyboard.

Now you are done with the "X" mechanism.

7. And the last part: attach the part with letter on it. First put the part with letter on it over the "X" mechanism and slided into the lower tow connection points of the keyboard.

When the part with letter on it is in the right place, press on the upper left side, then the upper right side until it clicks into the initial position.

This is it. Now you have a clean keyboard.

Friday, May 20, 2011

Opera - Remember choice and do not show dialog again - problem

I usually use Opera to surf on Internet. Opera is the fastest browser I've ever try, and it have a unique set of features that cannot be found on the other browsers. A couple days ago I was surfing some documents in Moodle. The documents type ware .doc, .docx, .zip, .txt... you get the picture. First, all those documents should be downloaded in order to be viewed. So when I press on a document, the "Downloas file" dialog appears. From this dialog you can select either Save or Open button. I usually use Open to directly open the document with the appropriate application. If the type of the document is .doc it will be opened by MS Word, if the type is .txt it will be opened by Notepad.
In addition, this dialog have an interesting option, "Remember choice and do not show dialog again", which I ignored for a very long time, until tow days ago, when by mistake I select the option's checkbox.

From that moment the Opera changes his opening behavior: all files are now opened with MS Word, even .pdfs or .txts. So the question is how can we undo the checkbox selection?

1. What happened when I selected the checkbox?
When I selected the chekbox from "Downloas file" dialog, in Opera was added a new MIME type called "application/x-forcedownload". And because I selected the chekbox when I was opened a .doc file, by default the "application/x-forcedownload" type is opened by MS Word. This was happened because in Moodle all download-able documents have the type "application/x-forcedownload". So when I try to open a .zip file in Moodle, the web server says to Opera "this file have the type application/x-forcedownload" and try to open the file with default application, MS Word.

However, when you will try download / open a zip file from other website, most probably that you will not have any problems, because the website tells to Opera "this file that you want to download have the type application/zip", so Opera will open the .zip file with the correct application.

2. How can you undo this behavior?
To undo the changes made when you click by mistake the checkbox in "Downloas file" dialog, all you have to do is to remove the MIME type called "application/x-forcedownload".
To do this follow the steps below:
- Open Menu Settings > Preferences > Advanced > Downloads
- Uncheck "Hide file types opened by Opera"
- Enter "doc" in Quick Find
- Select "application/x-forcedownload"
- Click "Delete" button

This is it. Now is back to normal.


Saturday, January 29, 2011

How to install Ubuntu after Windows installation

This situation is recommended most of the time because installing Ubuntu after Windows will not make Windows disappear in the way Windows does it when it is installed after Ubuntu. But even that it is suppose to be the easiest case, you still can encounter problems. The next steps will help you to complete this task for the most commune situation:

1. Windows installed (in my case Windows 7).
2. One empty partition where we want to install Ubuntu
3. Ubuntu LiveCD (it can be on a CD or USB stick). I test it for Ubuntu 10.10

Installation steps:
1. First you will need to split the empty partition in two (sometimes are recommended three partitions). One will be used for Ubuntu OS and one will be the swap. The recommended size for swap partition is at least the computer internal memory size (1, 2, 4 GB).
1.1. Boot into LiveCD and open GParted. It can be found under System > Administration > Partition Editor. Resizing and creating ext4 partition for Ubuntu OS and swap partition with GParted is covered in the tutorial How to install Windows 7 and Ubuntu side by side.
1.2. If you have problems launching GParted application from Ubuntu LiveCD, you can use EASEUS Partition Master from Windows. I would not recommend Disk Management from windows (Start > Run > compmgmt.msc).
1.3. From EASEUS Partition Master you can easily resize one partition in two. You can even set the File System ext3 and swap for them.

2. Install Ubuntu
2.1. When you reach the step "Prepare disk space" select "Specify partitions manually".
2.2. Highlight the ext3 partition and assign the mount point "/". Check "Format the partition" checkbox and select ext4 as File System.
2.3. Highlight the ext4 partition and click next.
2.4. While the Ubuntu is installing on your computer, you will be asked for location, username, password and so on.

3. Dual boot
3.1. After you reboot the system following Ubuntu installation, you will now see the GRUB menu from where you can select to boot Ubuntu or Windows.

How to install Windows 7 and Ubuntu side by side
EASEUS Partition Master

How to Install / Reinstall Windows after Ubuntu

I have a laptop with two OS installed on it: Windows 7 and Ubuntu. After some drivers problems I had to reinstall Windows 7. Before that I, made some research over the net of how to reinstall Windows after Ubuntu. I find a couple of sites with very useful informations, but as you can imagine, they did not worked smoothly. So here there are the combined steps which will help you reinstall Windows after Ubuntu installation:

1. (very important step) Back-up your important data for both Windows and Ubuntu files, setting, bookmarks...

2. Create an NTFS partition for windows (using fdisk, GPartEd or whatever tool you are familiar with). This step is already done because we have Windows installed on an NTFS partition.

3. Backup the MBR
Normally when Windows is installed after Ubuntu the "Master Boot Record", MBR, will be overwritten. You can save the MBR before installing Windows.
3.1. Boot from Ubuntu and run command: sudo dd if=/dev/sda of=/mbr.bin bs=446 count=1

4. Install Windows

5. Restore the MBR
5.1. Boot into a LiveCD
5.2. Mount Ubuntu root partition in the LiveCD (mbr.bin file was saved on Ubuntu partition). The simplest way to do this is by accessing Nautilus and click on the Ubuntu drive. Ubuntu will give a name for the Ubuntu partition something like 96be32...
5.3. After you mount Ubuntu partition, you have to restore MBR:
e.g. sudo dd if=/media/96be32.../mbr.bin of=/dev/sda bs=446 count=1

6. Restart and dual boot will reappear. Now you can boot Ubuntu.