Tim Mahoney

I stumbled across a great utility for creating true CSV files. A client had a need for a quote-enclosed CSV file, and Excel for some reason does not do this. Instead, it only encloses those fields that actually have commas in them, so that it doesn’t delimit by that comma. Works fine, but some systems require ALL fields to be quote-enclosed.

Check out the linkage goodness below.. He’s got a few other things on the site that I didn’t check out, but his “Text Write Program” is excellent – just make sure you enable macros. Has a little set up screen and everything!

Smokey Lake Excel Help

I’ve been meaning to come up with a way to create an Excel library for tasks I do very often; I suppose this will be a good place to start it and update it.

The TRIM function removes spaces from the right and left sides of whatever text you’re processing, like so:

Formula:

=TRIM(A1)

” Hello ” will process to: “Hello”

However, if you want to remove spaces from within a text cell, leaving all other characters / numbers / etc., you need to use the SUBSTITUTE function for simple cases.

Formula

=SUBSTITUTE(A1," ","")

” Hello Thar ” will process to “HelloThar”

First in what will probably be a lot of posts about Excel stuff. I use it WAY too often for text processing.

I had to update a whole lot of rows, and instead of sitting down and doing some ridiculous export and re-update, I figured I’d just write an SQL query in an Excel Concatenate statement and use the ids in the spreadsheet. WAH LA

First 2 columns are A : the id and B : the value I want to replace.

In column C, I put:

=CONCATENATE("UPDATE tablename SET fieldname='",B1,"' WHERE idfield=",A1,";")

Then, just copy and paste that cell all the way down, copy the queries, and paste them into a phpMyAdmin SQL text box and run.