Remove middle characters?

[katk]

Hi all --

I have a column that lists months when items are on sale or special sale ("(DP)"). One cell might look like this:

Jun (DP), Aug-Sep, Oct-Dec (DP)

or

Jun-Dec

or any other combination of months. The problem is that the software the data comes from doesn't give partial ranges, so if I only want sales through November, it will still show through December IF the final sale range includes November in it. (In other words, it wouldn't show "Oct, Dec" because it would stop before December. But if Nov is in the range, like "Oct-Dec", it will show "Oct-Dec" and not "Oct-Nov.") The same goes for the beginning month of the range.

I have a code that searches for the desired book end months and cuts/replaces where necessary, but it trips up when there is a (DP) at the end of the cell, as in my first example above. I can't figure out how to remove the middle characters of a string -- it keeps removing the left and right characters instead. Note: The months I need to take out would change with each use, and I set the desired month range with input boxes.

How could I tell excel to take "Jun (DP), Aug-Sep, Oct-Dec (DP)" and cut out the "Dec"? I've been trying MID with LEN, starting at [Length - 7] and going 3 characters but it hasn't been working. Any help would be appreciated.

Thanks.

(Let me know if posting code would be helpful. I figured it was more of a syntax question but I'm happy to post if it would help.)

[Bob Phillips]

[vba]

Dim tmp As String
Dim posHyphen As Long
Dim posBracket As Long

tmp = "Jun (DP), Aug-Sep, Oct-Dec (DP)"
posHyphen = InStrRev(tmp, "-")
posBracket = InStrRev(tmp, " (")
MsgBox Mid$(tmp, posHyphen + 1, posBracket - posHyphen - 1)
[/vba]