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

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())))

NOTES:

  • 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
Previous
Next

Recently removed from /r/excel

Excel Solver either keeps changing the answer or gives the same wrong answer. How do you get rid of the blasted format painter icon that obscures text - Excel/Mac 16.72 - Microsoft 365 What’s the easiest way to share an excel document with Reddit? How do I set a character max and format? Calculate total number of years months and days from already calculated set of individual results displayed as years months and days. Dynamic Scheduling Based on Rotation and Start Day Is there any way to freeze the top row by default or otherwise save the state of frozen cells? Why is my rounding bugging? Vlookup issue with value that exists completing the excel exposure Complicated MOD function that possibly might just be algebra.. Comparing two workbooks for differences Microsoft Money for Excel released today How do I insert a number of blank rows between every row of data, and then copy each row down? Copy data into new sheet with a break after every nth row or copy formula into every cell with breaks every nth row

More Random Comments

I’m anonymously sexting my roommate!! [Windhorst] Players are paying, with several telling ESPN they've spent more than five figures buying supplies of Kobes on the secondary market since last spring and summer. It's No Shave November my dudes Student loan forgiveness memo: Expert says Biden has authority to cancel student debt What's something you really want, but cant afford? Joe Rogan says the vaccine is administered incorrectly all the time because nurses aren't aspirating, and says failure to aspirate is the reason he claimed the video of the president being vaccinated was fake. The Advisory Committee on Immunization Practices (ACIP) says aspiration is "not necessary" Share Your Startup - November 2021 - Upvote This For Maximum Visibility! How do I stop wasting food? Why don't I come back?- an overseas Pakistani perspective fkn doctors Black metal bands that aren't racist. toxic since the beginning, but I(f21) still fell for him(m31) - please help and listen to me. Traffic-calming car barrier to be removed from SF street because activist group claims it is "triggering the trauma of displacement" Report: Donald Trump Should Start Panicking | He just suffered a massive defeat in his quest to keep the January 6 committee in the dark, and people are coming out of the woodwork to testify against him. A no olvidarse.