I am often faced with a decision to make: Should I modify the spreadsheet to separate out merged fields or should I leave the spreadsheet as-is and write the code to do the separation?
Here’s an example: The Need Date is already separated; now I need to separate the number in parentheses from the full name of the client. *Names have been changed to protect the innocent.
If I use the Text to Columns tool of Excel, look what happens to Chuck. His name spills into the next column!
And Chuck isn’t the only person with right parentheses in his name. The numbers in the first column will all have to be stripped of the left paren, and the names that got split will have to be re-merged. With over 500 names in the file, this isn’t really something I want to do every time I get a new copy of the file.
Instead I use the file as-is and write code to separate the number and name. This data will be input to a program that matches names from a custom report of another database with names in this file.
This is easy using the string module of Python. I separate the number by finding the index of the first right paren then slice the field starting at the index of the first digit (1) to the index of the first right paren. What remains is the name which is sliced from the index of the first right paren plus 2 (skipping the blank) all the way to the end of the string. The name is converted to all lowercase and stored, and the match indicator is set to ‘no’
And here’s what I end up with. Note that I convert the names to all lower-case, and will do the same with the names in the custom report so a match won’t fail because of mis-matched case.
If you’re still struggling with excel tools to manage your data, contact me to discuss the time-saving advantages of using custom software instead.