How to Delete Returns in Excel

The pesky carraige return and new line characters, while providing a way to present text for human viewing, are not always desirable when working in a spreadsheet program like Microsoft Excel or OpenOffice.org.

In Excel, a bit of code provided by Ivan Moala on mrexcel.com, works wonders. Here is the code:


'Created by Chip Pearson
'Cleans up data by removing tabs and carriage returns in worksheet cells.
Sub CleanUp()
Dim TheCell As Range
For Each TheCell In ActiveSheet.UsedRange
With TheCell
If .HasFormula = False Then
.Value = Application.WorksheetFunction.Clean(.Value)
End If
End With
Next TheCell
End Sub

Here’s how to use it:

  • In Excel click Tools > Macro > Visual Basic Editor (or just hit Alt-F11)
  • In the top-left pane under the Project heading, double-click the name of the sheet you want to strip all the newlines and carriage returns from.
  • In the window that pops up, paste the code above into the empty box under where you see General and Declarations.
  • Run the macro by clicking the “play” button above, or hit F5

Warning: make a backup copy of your Excel workbook before performing the above procedure.

Posted under Excel, Microsoft, Uncategorized

Alternate Time Servers for Automatic Clock Set

In modern Microsoft Windows operating systems and all Unix / Linux systems the system clock time can be set automatically. The computer will connect to a special server on the Internet called a Network Time Protocol server, and get the current universal time, and then adjust it according to the local time zone on the computer. It keeps the computer clock very accurate and corrects drift before it can become much of a problem.

We recommend using a reliable server: pool.ntp.org

A wonderful group of generous folks provide this extremely reliable cluster of computers for us all to use, free of charge. Thank you NTP Pool Project and all the participants!

Posted under Freeware, Linux, WebDev

How to Show MySQL Warnings at CLI

When logged in to the MySQL server using the command line interface (CLI) the  generated errors on the previously run command.

mysql> show warnings;

Thanks to Trevor Nichols and www.issociate.de

Posted under Freeware, MySQL

This post was written by Content Curator on November 16, 2009

Tags: , , , , , , , , , , , ,

How to Highlight Duplicates in Microsoft Excel

Using conditional formatting one may easily show duplicate values in any chosen range of data. Find all duplicate values, and highlight any values that appear more than once.

=COUNTIF(A:A,A2)>1

This is the meat, and here is how:

  • Highlight the cell to work on.
  • Click Format > Conditional Formatting
  • Set Formula Is
  • Enter the formula above, replacing the ranges as follows:
    • (A:A means that it will look inside the entire A range. Replace this with your own range, e.g. B3:B5 or CC:CC
    • ,A2) means to count how many times the value in A2 appears in the designated range. This needs to match the cell you are currently working on.
  • Then set the format you want to see when the count of identical values existing in the range is greater than one. This value can be altered to a higher number in order to highlight cells that have more than two duplicates, e.g. change it to be >5 to highlight the cell when at least 5 cells have the same value as the current cell, including this cell itself.
  • Copy the formula only to other cells in the range. To do this, copy the current cell, then Paste Special and choose Formulas and paste into the other cells in the range.

An excellent tutorial is at MREXCEL.COM, and thanks to that site for this knowledge and info.

Posted under Excel, Microsoft, Office

This post was written by Content Curator on November 16, 2009

Tags: , , , , , , , , , , , , , , , , ,

How to Select First Last or Range of Records in MySQL Table

When using MySQL, and wanting to retrieve the first n records from a table, one can use the LIMIT function. It can also be used to grab a range of records, not just a series starting with the first record. Use as follows:

SELECT * FROM yourtable LIMIT yourlowlimit,yourhighlimit;

This will return all rows between the number yourlowlimit and yourhighlimit inclusive.

e.g. SELECT FROM users LIMIT 20,50
will return the rows 20, 21, 22,…, 49, 50

Thanks to dmxzone.com

Posted under Freeware, MySQL

This post was written by Content Curator on November 16, 2009

Tags: , , , , , , , , , ,

How to Delete Records in PHP using MySQL

The code in the HTML/PHP markup would look something like:

<?php mysql_query("DELETE FROM yourtable WHERE yourfield='yourvalue' ")
or die(mysql_error()); ?>

This code deletes any record from the table named “yourtable” when that record’s field named “yourfield” is identically equal to “yourvalue”. The names of these objects will, of course, be replaced by the unique names that your set up uses.

Source: tizag.com – excellent tutorials on all kinds of coding, programming, and other technical stuff.

Posted under Freeware, MySQL, WebDev

This post was written by Content Curator on November 16, 2009

Tags: , , , , , , , , , , , , , ,

Adobe Download: Adobe Reader 9.1

The redistributable package of Adobe Reader 9.1, without Adobe AIR or Download Manager bundled. This is the simple Reader-only package installer, no Internet connection needed once it is downloaded.

Find all current special offers on Adobe products.

Adobe Download: Adobe Reader 9.1

If there is ever a problem when a PDF document is viewed, such as an error generated by Adobe or a failure to print the PDF file, then reinstalling the program is the best option. To do this, simply:

  1. Uninstall any currently installed “Adobe Acrobat Reader” programs in the Control Panel’s Add/Remove programs (in Windows Vista and Windows 7 it is called “Programs and Features”).
  2. If there are problems uninstalling Acrobat, such as an error such as “THE PATCH PACKAGE COULD NOT BE OPENED. Verify the patch package exists and you can access it, or contact the application vendor.” then do the following sub-routine:
    1. Download Microsoft’s Clean Up Utility.
    2. Run it to install the Clean Up program.
    3. Now run the Clean Up Utility program by clicking Start > All Programs > Windows Install Clean Up
    4. In the list of program that the utility offers fins Acrobat Reader, click once on it to select its line, then click the Remove button.
    5. Download and install Acrobat Reader from the link provided above.
    6. Once it is installed, now uninstall it in the Control Panel’s Add/Remove programs (in Windows Vista and Windows 7 it is called “Programs and Features”).
  3. Install the Acrobat program after downloading it from the link above. The program has been “reinstalled” now. This reinstallation takes care of most problems that can occur in Reader, and it’s plugin / add-on for Mozilla Firefox and Internet Explorer.

There have been some mentions of an Adobe Cleanup Tool or Adobe Uninstall Tool made to uninstall Reader manually, but these are still unavailable from the Adobe website to the public. Perhaps their technical support staff can provide the utility if a customer contacts them, as one blogger stated.

Posted under Browsers, Downloads, Freeware, Microsoft

Windows XP Service Pack 3 Command Line Options

When running the SP3 install from the command line, choose the following options:

---------------------------
Service Pack 3 Setup
---------------------------
AVAILABLE SWITCHES:
[/help] [/quiet] [/passive] [/norestart] [/forcerestart] [/warnrestart] [/promptrestart] [/overwriteoem] [/nobackup] [/forceappsclose] [/integrate:] [/d:] [/log:]

/help Displays this message

SETUP MODES

/quiet Quiet mode (no user interaction or display)
/passive Unattended mode (progress bar only)

RESTART OPTIONS

/norestart Do not restart when installation is complete
/forcerestart Restart after installation
/warnrestart[:] Warn and restart automatically if required (default timeout 30 seconds)
/promptrestart Prompt if restart is required

SPECIAL OPTIONS

/overwriteoem Overwrite OEM files without prompting
/nobackup Do not backup files needed for uninstall
/forceappsclose Force other programs to close when the computer shuts down
/integrate: Integrate this software update into
/d: Back up files into
/log: Create log file at

****
My Install
Command line:
D:\WindowsXP-KB936929-SP3-x86-ENU /nobackup /quiet /forcerestart /forceappsclose

Posted under Microsoft

How To Copy Directories and Subdirectories Recursively With FTP (scp)

When transferring file directory structures between linux / Unix hosts, usinf FTP was what came to mind.  FTP has been used for many transfers in the past, but when forced (read:allowed) to use the command line to transfer files, the MGET and other FTP related commands were useless. So google to the rescue, and up pops this great simple writeup about how to copy host-to-host using the SCP command. Sweetness defined.

In essence:

scp -vr -P 2222 ./* REMOTEUSERNAME@REMOTEHOSTNAME.TLD:/FULL/UNIX/PATH/TO/DESTINATION/FOLDER/(OR/FILENAMES.ABC)

This command will Verbosely and Recirsively do it’s thang. It will contact the remote host on port 2222 instead of the default port 22 used for SSH. The remote username is the unix username, and the remote hostname is the full DNS name or IP address of the remote unix box. The destination path is reltive to the root of the system, NOT relative to the user’s home dirtectory.

Have fun, and leave FTP for transferring single files or batches of files inside a single directory container only.

Posted under Freeware, Linux, Network

How To Tell Windows XP To Not Look For New Wireless Networks

In order to make Windows XP ignore new wireless networks, there must be some sort of registry tweak or something, right? There must be some way to force the manual setup of new wireless network connections in Windows, right? Let’s find out… After a bit of Googling came up with these:

http://antivirus.about.com/od/securitytips/ht/wirelessconn.htm
This one may be true, but on the system I checked the checkbox mentioned was not ticked.  Not to mention, this does not keep the wireless adapter from finding, or scanning for, new wireless networks and reporting that they are in view.

Posted under Microsoft, Network, Wireless