Is there a way to turn my date range in text form to a two-column number form date range?

Ah, no worries. It's a little longer formula, and you'll have to copy/drag across and down manually, but still doable. Assumes same layout (originals in A2), put this in B2 and copy across/down as needed:

=IF(TRIM(MID(SUBSTITUTE($A2," - ",REPT(" ",LEN($A2))),COLUMN(A1)*LEN($A2)-(LEN($A2)-1),LEN($A2)))="","",--(TRIM(MID(SUBSTITUTE($A2," - ",REPT(" ",LEN($A2))),COLUMN(A1)*LEN($A2)-(LEN($A2)-1),LEN($A2)))&", "&YEAR(TODAY())))


  • If you need to modify that to suit your environment, change the $A2 elements accordingly; leaving the dollar-sign intact -- so the formula stays "looking at" our original info in A as you copy across.
  • Also leave the 2 COLUMN(A1) elements intact as well. Those serve as our "counter". It tells the formula to grab the 1st dash-separated date, then the 2nd one, and even the 3rd, 4th, 5th, etc. if they exist in your original string as you copy across.
/r/excel Thread Parent