Rearranging text in cell based on special character

[rangequestion]

Hello,

I have multiple date values that look like one of these 2 options:

5/4/2017 12:00:00 AM
20/12/2017 00:00:00

What I'd like to do is only keep the date and convert it in US format. It's in European now. So for the example above I would want:

4/5/2017
12/20/2017

thanks!

 

[Fluff]

Have a look at text to columns > Delimited > Next > uncheck all boxes > Date MDY > Finish

 

[rangequestion]

No, that didn't do anything. Plus I need to have it written in a formula rather than using text to columns

 

[Fluff]

In that case I'm afraid I can't help, formulae are not my strong point

 

[rangequestion]

Can anyone else assist?

 

[jtakw]

Hi,

Are those Real Date/Time values (number)? Or are they Text?

If they're Real Date/Time values:

Book1
	A	B
3	5/4/2017 12:00:00 AM	4/5/2017
4	20/12/2017 00:00:00	12/20/2017
Sheet8

Cell Formulas
Range	Formula
B3	=TEXT(A3,"m/d/yyyy")

 

[rangequestion]

No, that's the problem, its text and custom format. SO Excel is really hating it right now.

 

[jtakw]

No, that's the problem, its text and custom format. SO Excel is really hating it right now.

Hopefully your data are all in the same format as the samples you posted:

Book1
	A	B
1	Raw Data	Formatted as Date
2	5/4/2017 12:00:00 AM	4/5/2017
3	20/12/2017 00:00:00	12/20/2017
Sheet9

Cell Formulas
Range	Formula
B2	=MID(SUBSTITUTE(A2,"/","/"&LEFT(A2,FIND("/",A2)),2),FIND("/",SUBSTITUTE(A2,"/","/"&LEFT(A2,FIND("/",A2)),2))+1,LEN(A2)-LEN(MID(A2,FIND(" ",A2),255)))+0

Formula copied down, results are converted to Real Dates, just format cells as Date.

 

[rangequestion]

success, thanks!

 

[jtakw]

You're welcome, glad it worked for you.