Remove Linefeeds from an Excel Document

I recently received an Excel document that needed to be massaged to suit the requirements of a DCS. One of the changes required was the removal of line feeds from within cells.  There were over 500 rows nearly all with multiple line feeds so I did not want to do this manually.

Some googling revealed an easy way to do this and a cool new tool to try – immediate mode in VBE.

1. On the Excel worksheet select all cells that may contain a line feed to remove
2. Open VBE using alt+F11
3. Enter Immediate mode by pressing ctrl+G
4. In the Immediate box type Selection.Replace Chr(10),”” In Immediate whenever you hit enter your command is executed.
5. Go back to the worksheet and all line feeds are removed

Note that the argument for the Chr function is a char code. Char codes are the same as ASCII codes for the first 32 control code characters so line feed is alt+0010 or Chr(10).

References:
http://www.ozgrid.com/forum/showthread.php?t=20451

OpenVPN on Windows 7

I had some trouble getting OpenVPN to work on my Windows 7 laptop. After a lot of messing around it turns out to be a fairly simple operation, so to save others the messing around part here are the steps to follow.

1. Download the latest OpenVPN windows installer from http://openvpn.net/index.php/open-source/downloads.html I used openvpn-2.1.1-install.exe
2. Completely uninstall any previous versions of OpenVPN from your machine
3. Install OpenVPN
4. On the start menu right click “OpenVPN GUI” and click properties. On the Compatibility tab select “Change settings for all users” then tick the box “Run this program as an administrator”
5. This step is not so obvious. Open the network and sharing centre and click on “Change adapter settings”. There should be a Local Area Connection with the type TAP-Win32 Adapter, right click on this and select properties. Tick the boxes next to “Internet Protocol Version 6 (TCP/IPv6)” and “Internet Protocol Version 4 (TCP/IPv4)”
6. Copy your config and credentials files into the C:\Program Files\OpenVPN\config folder
7. Now click OpenVPN-GUI in the start menu. You’ll have to enter your Administrator password to run it. When it’s running just right click on the OpenVPN icon in the system tray and select connect

And that’s it! I hope it works for you too, leave a comment to let me know…