Excel text manipulation rocks Jun28 '05

You know Lloyd... Just when I think you couldn’t go and do anything dumber, you go and do this... And totally redeem yourself!

Jeff Daniels, Dumb and Dumber

Every time I find myself cursing a Microsoft product, for one reason or another – I always remember the usefulness of Excel, which puts powerful text manipulation at your fingertips.

In my mind, Microsoft always "redeems itself," with the sheer power of Excel.

Got a big wad of text that you need to sort through, organize, find/replace?

Chances are there is some function or technique, within Excel, that can solve all of your text problems, and within minutes, not hours.

I believe there is hardly a scenario that can’ t be solved with Excel. With a little VB knowledge, anything can be done.

PROPER text case: First and Last names

Among the many "text case" functions in Excel, PROPER seems to come in handy the most.

Let’s say you have a giant list of first and last names, all in lower case.

For the project you’re working on, you need each name in the proper case, with the first letter capitalized in the first and last name.

For these examples, we’ll just use a few names, to keep things simple. But, these techniques are much more effective with hundreds, or thousands of names.

Rather than manually going into each cell, and deleting the first letters, and replacing them with their capital counterpart – you can simply use PROPER to do all the work for you:

=PROPER(A1)

Once applied to all of the cells, we have our result:

No deleting or re–typing necessary. Just one text function takes care of it all.

Combine two cells into one

Let’s say the last name and first name are in separate cells:

We want to combine the two names, flip them (so the first name comes first), and make it proper case:

Lauren Holly

With a list of hundreds of names, this could take forever to get straight, right?

How can Excel help in this case?

Easy. A string formula to the rescue:

Notice the formula:

= PROPER( B1 & " " & A1 )

Here we specify the name in cell B1 to appear first, then a space follows, then the name in cell A1 is listed next. We wrap the entire string formula inside the PROPER function, which makes our first and last names begin with a capital letter.

Drag the formula down to all the cells with names in them, and you’re done.

SQL in Excel

Typically, the formulas and functions above are "child’s play." Those is just the tip of the iceberg, in regards to what Excel can do with text.

I try to use Excel as much as possible when I need to manipulate SQL queries.

For example, let’s say I have a giant list of prices, each of which has the corresponding product id next to it. I need to insert these prices into a database table that already exists.

Since I want to insert them into a database table that already exists, I need to use UPDATE:

UPDATE product_prices_table SET current_price = 34.00 WHERE product_id = 344;

But, with a giant list of hundreds of prices, it could take forever to write an UPDATE statement for every price.

Solution? Write a simple string formula, and you’re done!

Notice the formula:

= "UPDATE product_prices_table SET current_price = " & TEXT( B2, "0.00" ) & " WHERE product_id = " & A2 & ";"

We write out the redundant part, and then insert the cell locations where appropriate.

By applying this formula to every cell in question, we instantly have our result:

UPDATE product_prices_table SET current_price = 34.00 WHERE product_id = 344;

Of course, the price and id number change, according to the cell in reference.

Now we have our SQL queries organized within Excel. All we have to do is copy the queries (all at once, by dragging down the whole column), and paste them into our SQL editor, for them to be processed.

Done!

Categories: Software , SQL , Tutorials

Add Feedback (view all)