string;# Before Values in a SharePoint List’s Lookup Column Based on a Calculated Value

I’ve seen lots of posts discussing this, but no workarounds.  Here’s the situation:

• One list (List 1) contains a list of values that are calculated (say ColumnC = ColumnA & ColumnB)
• Another list (List 2) uses ColumnC above as a lookup for one of its columns
• If you go into Datasheet view for List 2, you will get the following error every time you try to change an item (with your column name mentioned instead of mine):

• If you click on the dropdown for the column in List 2 that contains the lookup, every value will be preceded by ‘string;#’.
• If you select the entry that looks exactly like the current value (but with the ‘string;#’ preceding it), you can then save the item.

This seems to be an annoying “feature” of how a calculated lookup column is represented in the Datasheet view.  We’re running SP1 with all current patches at this point, so I don’t hold out hope of a fix anytime soon.

The best workaround that I’ve come up with is to not calculate the lookup column, but to have a simple workflow which does the calculation and updates the column whenever the item changes.  This should work just fine, but since my calculated column is already embedded as a source for many other lists (yes, I inherited a solution, yet again), it’s problematic to make the change.

Anyone seen a better workaround?  This really gets in the way if you need to do some wholesale changes on a long list that contains this problematic type of column.  I’ll certainly post anything I find that gets around this better.