Excel Text Wizardry to Extract First and Last Names
What happens when you need to sort through data in excel to insert into a database? You typically have to clean the data before you insert it.
Today I was handed a spreadsheet that contained people's names. Unfortunately, the database schema requires different columns for first name and last name.
So here we go!
| A | B | C | |
| 1 | Full Name | First Name | Last Name |
| 2 | mark rickert | =PROPER(LEFT(A2,(FIND(" ",A2)-1))) | =PROPER(RIGHT(A2,(LEN(A2) - FIND(" ",A2)))) |
|
Will Become: |
|||
| A | B | C | |
| 1 | Full Name | First Name | Last Name |
| 2 | mark rickert | Mark | Rickert |
This code will take the full name and determine the first and last into their own columns. You can then take those columns, copy them, and do a edit>paste-special>values and it will paste the text in without the accompanying formula (so you can delete the full name column).
That's my quick and dirty Excel tutorial for the day.
Oh yeah, and it will also convert the first and last name to proper case if the original text was all lower case. It doesn't work with middle names or middle initials and will tack those onto the last name field.
Have fun!
QUICKLINK – Linux: Changing distribution remotely
Linux: Changing distribution remotely -- HOWTO
An interesting article that ends in "If the unimaginable happened and your box had booted successfully then consider yourself lucky. You are now running your new distro on a "clean" install."
It amazes me what *NIX OSes can do, but sometimes I think that people so things that were never intended to be done like this.
How To: remote mySQL access, via SSH port forwarding
How To: remote MySQL access, via SSH port forwarding
Step 1: Open Up a Terminal Window
Step 2: Type in this command
sudo ssh -L 7777:127.0.0.1:3306 username@remoteserver.com
Where 7777 is the local port you want to map to.
Using Subversion With Dreamweaver
With multiple developers working on one project version control is a must. The two main choices out there for version control are CVS and Subversion (SVN)...
Here's how to link up Dreamweaver with your SVN server.
Cool, Pop-art inspired Images (Tutorial)
Pop art is an American cultural icon. Starting in the 50's, pop-art dominated advertising and moved to be the predominant style of American comic books.
Now there is a way to create your very own pop-art images from a photograph. This great tutorial (for beginners or intermediate users of Photoshop) shows you every step of the way (with a great starting image I might add).
Photoshop and a few standard filters and some graphic wizardry will help you transform any photo into a cool Lichtenstein-esque image fit for a comic book or a photo album.
Check out the tutorial and maybe even try it yourself.
Recommended Reading
- Me: “OK, let’s start by opening your web browser.” Client: “Web browser? Oh,...
- 14 Famous Man Rooms
- It's Not Macro Beer - It's Imitation Beer!
- Library of Congress rules in favor of jailbreaking
- All the Girls
Nike+ Running Stats
- Total runs: 169
- Total distance: 298.44mi
- Total time: 86hr 53min 47sec
- Total calories: 39,935
- Avg pace per mi: 17'28"
- Avg distance per run: 1.77mi
- Avg calories per run: 236.30
My Records
- Farthest Run: 6.43mi
- Fastest Mile: 8'40"
- Fastest 5K: 32'10"
- Fastest 10K:2hr 16min 12sec
My Recent Runs
- June 1, 2010, 11:39pm
- Distance: 2.20mi
- Duration: 41' 12"
- Calories: 279
- May 27, 2010, 9:59pm
- Distance: 1.16mi
- Duration: 19' 44"
- Calories: 146
- May 10, 2010, 10:56pm
- Distance: 0.67mi
- Duration: 10' 57"
- Calories: 85
My Nike+ Goals
Recent Comments
- Libby Murray on My Mecca
- Ajaxery on Funny Cookies
- Paul on Hacking Is EASY! 80′s-tastic video
- Chufiro on Mohawk vs. Fauxhawk
- Nepol on Hacking Is EASY! 80′s-tastic video