Let me start with a simplifying assumption and show how to deal with this rock-bottom simple case, then we'll work our way up to the more complicated ones. Part A of the simplifying assumption is that there are only 2 commas in the text field, the one that precedes the date and the one that's IN the date. Part B is that there's only one hyphen (for a hyphen, not an en dash or an em dash, is what appears in your example), namely the one immediately after the date.

What you want to do is use the Middle function to find the text string "Nov 10, 2006". The syntax of "Middle" is:

   Middle ( text ; start ; numberOfCharacters )

Let's look at those parameters one at a time.

 • "text" is the name of the text field you will be examining. For this example, I'll assume it's "blather", and I'll further assume that the record we're looking at has a value of "blather" equal to:

   "The UFO landed, Nov 10, 2006 - and nothing was ever the same."

 • "start" is the beginning position of the date, counting the number of characters from the beginning of "blather". For instance, the "T" in "The" is at Position 1, the "F" in "UFO" is at Position 6, and so on. Ideally, we'd like to find the position occupied by the "N" in "Nov", which is Position 17. (As we will see shortly, it'll actually be easier to look for the position immediately after the comma, Position 16, which is occupied by a space in the example.)

 • "numberOfCharacters" is the length of the character string we want to extract from "blather", which would be 12 for "Nov 10, 2006". The trick, of course, is that it won't always be 12. For example, if someone put in a period ("Nov. 10, 2006"), it would be 13. If someone used "Sept." instead of "Sep" or "July" instead of "Jul", same deal. So what we need here is not a fixed number but rather a calculation of the distance between the comma and the hyphen.

So here's the "Middle" formula rewritten to take the above into account:

   Middle ( blather; [position right after comma]; [position of hyphen minus position of comma] )

which translates to:

   Middle ( blather; 16; 30 - 16 ) = Middle ( blather; 16; 14 )

So which 14 characters will we be getting? They will be these:

"But," you may be thinking, "I don't want those spaces surrounding the date." Can't blame you for that, but there's no way to guarantee in advance that those spaces will be there all the time. What if your original text had read:

In this case, your calculation of hyphen position minus comma position would have yielded 12, and you'd get exactly what you wanted, the date without surrounding spaces.

So the way we deal with extraneous spaces is to use the Trim function, outside the Middle function, thus:

That way, if the Middle function yields " Nov 10, 2006 ", the Trim function will cut it down to just "Nov 10, 2006".

OK, now let's look a little more deeply at those formulas for finding the position. The syntax of "Position" is:

   Position ( text ; searchString ; start ; occurrence )

Again, let's look at the parameters one at a time:

 • "text" is our old friend "blather"

 • "searchString" is what you're looking for, inside quotation marks. The very first thing we'll be looking for is a comma, so for that purpose, "searchString" = ",".

 • "start" is where within "text" you want to start searching. It's almost always 1 (the very 1st position, the "T" in "The").

 • "occurrence" is which occurrence of the search string you want to discover. Under our simplifying assumption, you'll be looking for the very 1st comma, so this too will be 1.

So here's the "Position" formula rewritten to take the above into account:

Now, that finds you the position of the comma, but what you really want is the position of the character immediately after the comma, so for that you'd specify:

I leave it to you to figure out the formula that you should plug in for "[position of hyphen minus position of comma]".

Try this first to see what you get. If it turns out to work for some of your records but not all of them, we'll probably end up breaking your set of records up into batches and dealing with each batch separately, probably depending on the number of commas that precede the one immediately before the date. But maybe we'll get lucky, and it really will work for 100% of them the 1st time out of the gate.