Patent Citation Analysis Project Upcoming

Hello everyone.

Thought I'd drop a quick post about an upcoming project that I'll be pursuing soon for my "real job". (I an the Systems Manager for the University of Nebraska - Lincoln's Office of Technology Devlepment, which is the office of the University responsible for commercializing all intellectual properties - patents, etc. - generated through University research.)

What I'll be trying to accomplish using Excel and VBA, and specifically using the objHTTP object to return web pages and regular expressions to parse the text results of those pages, is a homemade system for performing patent citation analysis.

Now, if any of you are familiar with this type of analysis, you'll know that there are many commercially available packages out there, including services from MicroPatent (www.micropat.com), that do this sort of thing, but understand that our office will be using such solutions, and what I'm trying to do is extend that built in functionality.

This ought to be quite interesting in the end (to me, at least).

I'll keep you posted.

Excel_Geek

Growing International Readership

I wanted to take a moment to send out my appreciation to my growing International audience.

Thank you. Dank u. Merci. Danke. Grazie. Σας ευχαριστούμε. ありがとう. 너를 감사하십시요. Obrigado. Вы. Gracias.

Here's a recent example of the breakdown of my readership (from Google Analytics):



I'm getting more and more international Blog_Mail and Insiders subscribers, and about 20% of all $50 Projects come from Europe.

I've also been getting more and more emails from individuals in the United States and overseas, who'd like to essentially either be a franchisee or reseller of my services. Here's my standard answer:

I do this as a hobby. I make very little money, and in fact, if I were to charge true market rates for this service ($100+ per hour) I'd probably not get enough projects to write about. Plus, I've got a "real job", so I can't spend 50 hours per week on this.

If anyone out there would like to be a franchisee, I'm sorry, but I'm not interested. However, if you'd like to be a contributing programmer/solution provider to keep your skills sharp, email me and I can add you to my growing list of "helpers". How this works is when I get requests for $50 Projects that I am not interested in doing, or I don't have the time for, or I can't figure out myself, I email my list of helpers. If one of them can figure it out, I let them do it, and they email me the solution. I pay them $40, I keep the rest.

If anyone out there would like to be a reseller of Excel_Geek services, again I'm not interested in setting up a formal program. However, if you set up a service whereby you offer to do Excel/VBA projects for, say $100 each, and then turn around and request those projects of me for $50, I can't do anything to stop you. However, know that my standard terms will be in effect.

Later,

Excel_Geek

Work Around for 1024 Character Per Cell Display Problem

Here's a recent $50 Project Request that I got from Mike:

Mike was going to use Excel to log transcripts of videos, but he kept running into Excel quirky little 1,024 character display issue. Everyone's probably aware of the current versions of Excel's 1,024 character per cell display problem, right? If you aren't, here's the jist: In Excel '97, '00, '02 and '03, a cell can contain up to 32,767 characters. However, if a cell contains more than 1,024 characters, the following rules apply:

• Characters after approximately the 1,024th character do not appear in the cell; however, they appear in the formula bar when you edit or select the cell.


• Although characters after approximately the 1,024th character do not appear, you can detect and manipulate them with worksheet functions (for example, the RIGHT and MID functions) and macro commands (for example the Characters property).


• If you copy a cell that contains more than 1,024 characters, and then paste them in another cell, all of the characters are pasted into the new cell. However, characters after approximately the 1,024th character do not appear in the destination cell.


• Characters in a cell after approximately the 1,024th character are not printed and may not appear in print preview.


• You may be able to see more than 1,024 characters by increasing the row height and column width of the worksheet, by modifying the display settings of the system, and/or by inserting strategically place hard character returns (ALT+ENTER).

Here's a nice Microsoft article on the topic, if you're interested.

Now, Excel 12 will not have this issue, but Mike likely didn't want to wait for its release then immediately shell out the bucks to upgrade everything, so I set out to write some code that would patch together a clever work around to this problem.

What I basically did was write a macro that would check each cell in the column containing the transcript texts (which are the ones regularly pusing the limit) to see if any of them contained more than 1,024 characters. If one did, I'd add a new row below that one, truncate the long text at 1,024 characters, and place the remainder in the new cell directly below the first one (and repeat if necessary). Then I'd merge the top and bottom cells in all the other columns and format the cells containing the long text so that they visually seemed to be one cell.

Pretty crude, but works great. An intersting note, the .text property of a cell containing the long text only returns the first 1,024 characters, but the .value property returns the actual contents of the cell.

What did Mike end up doing in the end? Well...unfortunately, he decided to use a different program for logging called LLama Log that interfaces with Final Cut Pro quite well and has added some added features that he couldn't readily get in Excel.

Oh well. If anyone else out there wants the project...it's only $50. But of course Excel_Geek Insiders subscribers get all my projects for only $39.95 per year. Sign up today!

Excel_Geek

UPDATE II on Google API Project

Thought I'd pass along an interesting development with the Google API project requested by S_ _ _ _:

I have been contacted by another party who has alerted me to potential intellectual property issues surrounding this project. I'll not be identifying this party, nor will I make any statements relative to the exact nature of this party's claims, nor my perception of the validity of their claims.

Suffice it to say that I'll not be pursuing this project for hire at this time.

Sorry, S_ _ _ _. $50 isn't worth the risk.

Excel_Geek