Macro to remove text after date

[polaron]

Hello all,

I have found all kinds of find & replace samples, but nothing to delete text after a certain character count...

My sheet contains a column R with date stamps:

2020-05-04T18:31:53.280Z
2020-05-01T23:10:06.774Z
2020-04-17T23:02:57.912Z

I'm looking for a macro that removes everything after and including the letter T (it's always the 11th character)

2020-05-04
2020-05-01
2020-04-17

Thank you in advance for your help!

[MarvinP]

Hi polaon,

You could use the Left() function in another column and extract those dates. Some thing like:

=Left(A1,10)

If you do this to another column and the copy and paste values only over the formulas they will remain. You might also want to change them to real dates by adding a zero..??

[PCI]

If A1 = 2020-05-04T18:31:53.280Z
and if left(A1,10) does not work then we need an Excel sample of your data ...!

[polaron]

Thank you both, the Left() function did the trick! I was overcomplicating it in my mind.

[Rick Rothstein]

You could also have used the Text To Columns dialog box by selecting Column R and then choosing Delimited in the first dialog page, then in the second dialog page uncheck all the checkboxes, then put a check mark in the "Other" checkbox and put an upper case T in the empty field next to it, then in the third dialog page, select the second column in the table and then click the "Do Not Import" optionbutton, then click the Finish button.