Cell Time Format > Backspace before first character with no spaces present, changes cell data appearance

[Chopsaki]

Ok, so have imported data from another source and copied into excel. I have a column that contains a list of time stamps. The format for the cells is "Custom > h:mm". With this format, the data appears as follows:

• 2:54:29 PM

Upon inspection, I found that the cells contained Leading Spaces, so I applied the following formula example to remove them:

• =TRIM(CLEAN(SUBSTITUTE(K35,CHAR(160),)))[/CODE]

The net result was that any leading spaces were removed, however the data presentation in the cell remained unchanged. I then selected a cell, placing my cursor to the left of the first character and found no leading space. However, by pressing the "Backspace" key, the data represented changes and appears as follows:

• 2:54

This above is the desired result. I'm not interested in selecting each cell and executing the "Backspace" key for reasons unexplained.

Any ideas as to what I have missed?

 

[Z51]

"h:mm" format on 2:54 PM should show 14:54.

Also, you may be able to just use this formula, and not have to do any backspaces. (Format the result cell as h:mm or whatever you wish, of course.)

=SUBSTITUTE(K35,CHAR(160),"")+0

 

[Chopsaki]

"h:mm" format on 2:54 PM should show 14:54.

Also, you may be able to just use this formula, and not have to do any backspaces. (Format the result cell as h:mm or whatever you wish, of course.)

=SUBSTITUTE(K35,CHAR(160),"")+0[/C
[QUOTE="Z51, post: 5935800, member: 86898"]
"h:mm" format on 2:54 PM should show 14:54.

Also, you may be able to just use this formula, and not have to do any backspaces.  (Format the result cell as h:mm or whatever you wish, of course.)
[CODE=xls]=SUBSTITUTE(K35,CHAR(160),"")+0

Thanks for your response, however this has not resolved the issue.
[/QUOTE]

 

[Chopsaki]

Further to my problem, I can provide the following analysis: