Help - Data Format Problem

[Delta Star]

I've been supplied with a vast amount of data which I'm trying to sort in time order. Unfortunatly the times are in a strange format. The column below shows a small amount of the data

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 height=17 width=64 align=right x:num="0.10625">02:33</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 height=17 align=right x:num="0.15069444444444444">03:37</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 height=17 align=right x:num="0.90277777777777779">21:40</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 height=17 align=right x:num="0.90972222222222221">21:50</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 height=17>02:30 3</TD></TR></TBODY></TABLE>

As you can see the last entry has a space followd by a three, or any other number. If I sort on that column the 02:30 time keeps ending up at the bottom. I've added an extra column and tried using =left(A5,5) but that does not work either.

Can any help?

 

[Richard Schollar]

Hi

Have you tried selecting the column and going Data>TextToColumns>Delimited and choose a space Delimiter>Next and on the third screen, select the second column and choose to Skip import>Finish?

Hopefully this will strip all these spurious numbers out leaving the valid times.

 

[Delta Star]

I've been using the suggestion supplied a couple of days ago and thought everything was fine.

However a strange thing appears to be happening. The example being if cell A1 has a time of 15:00:00, then cell A1 becomes 03:00:00 and cell B1 shows PM. I need the original time of 15:00:00 to be retained.

The things I've checked are the format of the column and individual cells where the apparant discrepancy arise. I've checked the values incase something is different but everything seems OK.

I get data from various people and this seems a random event, but is causing major problems.

I've come up with tempory solution by adding an If statement so that if cell B1 shows PM then .5 is added to A1.

Does anyone have any solutions to this?