Text to columns time format gets AM/PM wrong

[bchez]

Column A	Column B
08/07/2019 12:00 AM	01/00/1900 7:03 AM

<tbody>
</tbody>

I am doing a text to columns where I have a date and time in Column A - and I want it to have the date in column A and the time in column B
Here is what the original looks like
08/07/2019 7:03 PM
The table above is what text to columns gives. When I format Col A to Date, short date that column is fine. When I format Col B to Custom h:mm AM/PM, it gets the AM or PM incorrect, randomly it seems. For example, in the table above, it should have been 7:03 PM instead of AM
Is there a way to fix this without VBA code? TY

 

[AlphaFrog]

What settings do you use with Text-to-Columns?

I suggest using Fixed width instead of a "space" delimiter.

 

[bchez]

Thanks I tried that as well, and am getting the same result. Thank you for the suggestion.

 

[jasonb75]

Did you remove the break line between 7:03 and PM with fixed width?
If PM is removed from the time then it will be read as 07:03 (24 hour format).

 

[sg2209]

Would you mind sharing a sample file or link to that.

 

[bchez]

I will try that. Thanks for the suggestion

 

[bchez]

I would love to share a file but I don't know how. As for links, I work in a hospital so everything is locked down tight.

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F
1		Text to Columns
2	03/03/2019 7:03 PM	03-03-19	7:03 PM	03-Mar-19	19:03	Column A is text
3	01/01/2019 7:03 AM	01-01-19	7:03 AM	01-Jan-19	7:03	Column A is text
4	03-03-19 19:03			03-Mar-19	19:03	Column A is number
5	01-01-19 7:03			01-Jan-19	7:03	Column A is number
6
1b

Cell Formulas
Range	Formula
D2	=LEFT(A2,10)+0
D4	=INT(A4)
E2	=RIGHT(A2,7)+0
E4	=MOD(A4,1)

Data Text to Columns works see B2:C3

choices
- Fixed Width
- delete break line between time and PM
- Date type selected as appropriate
- Destination as appropriate

 

[bchez]

Many thanks to you all. As several of you suggested, removing the break line between the time and PM did the trick - using Fixed width.