Excel Find & Replace? or Formula?

%3CLINGO-SUB%20id%3D%22lingo-sub-2824537%22%20slang%3D%22en-US%22%3EExcel%20Find%20%26amp%3B%20Replace%3F%20or%20Formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2824537%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHi.%20I%20am%20using%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22%22%3EMicrosoft%C2%AE%20Excel%C2%AE%20for%20Microsoft%20365%20MSO%20(16.0.14326.20384)%2064-bit%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ECan%20anyone%20tell%20me%20how%20to%20%22Find%22%20all%20the%20cells%20in%20my%20worksheet%20that%20contain%20this%20date%20and%20time%20stamp%20format%3A%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22202%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22202%22%3E2021-09-8T01%3A03%3A02%2B00%3A00%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20then%20replace%20it%20with%20just%20the%20DATE%20in%20this%20format%3A%22mm%2Fd%2Fyy%22.%3C%2FP%3E%3CP%3EWould%20that%20be%20an%20actual%20formula%2C%20or%20can%20I%20use%20the%20Find%20%26amp%3B%20Replace%20tool%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much%20for%20your%20time!%3C%2FP%3E%3CP%3EAngel_777_1%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2824537%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

Hi. I am using Microsoft® Excel® for Microsoft 365 MSO (16.0.14326.20384) 64-bit

Can anyone tell me how to "Find" all the cells in my worksheet that contain this date and time stamp format: 

2021-09-8T01:03:02+00:00

 

And then replace it with just the DATE in this format:"mm/d/yy".

Would that be an actual formula, or can I use the Find & Replace tool?

 

Thank you so much for your time!

Angel_777_1

1 Reply
Hi. You will need a helper column with a formula that converts the text string to a numeric Excel date. When it is numeric, you may format it the way you like.
Assuming date string in column A;
=DATE(MID(T(@$A:$A);1;4);MID(T(@$A:$A);6;2);MID(T(@$A:$A);9;2))
or with ; replace by ,
=DATE(MID(T(@$A:$A),1,4),MID(T(@$A:$A),6,2),MID(T(@$A:$A),9,2))

Your example string seems like missing a zero so the formula is based on your screen shot.