strange date format

[axelaxelaxel]

I got a strange format that I need to apply a format to, so Excel interprets it as a date.

It looks like this: 

1/30/2018 3:58:07 PM(UTC-5)

1/30/2018 11:33:06 AM(UTC-5)

All these dates are currently sitting in a text column in my sheet.

I am unsuccessful in converting this column to date using text to columns and apply a format, I've tried mm:dd:yyyy:hh:mm:ss AM/PM

Anyone?

[#2]

@axelaxelaxel 

Power Query is suitable for such transformations, if you consider such option.

Select the range -> Replace (UTC on space -> Replace ) on nothing -> Change Type Using locale -> Type DateTimeTimezone, locale English (US) -> return result to Excel sheet