Converting TEXT to DATe

[GFH]

Evening

I have two simalar issues.

1) I have a column of text that represents the date but is entered as
text. The entry has a leading space (entered as "<sp>12 DEC 06") that
does not permit the entry to be displayed or treated as a DATE cell.

Question how can I strip the leading space from a cellso I can format
and display the cell as a DATE?


and 2nd

Another column has a time entry with the same leading zero as well as
one after the full colon "<sp>9:<sp>34" without quotes of course.

Same question is there an easy way to get rid of the leading space s
well as the <sp> after the colon?

Thank you

G

 

[Allen]

Hi,
Have you tried trim function to remove the leading space
Supposse your data in cell A1 then lets type the formula in Cell B1 as
=trim(A1)
The other option is Select complet column and use find and replace
option from edit menu
under find lets his space bar onece and under replace dont type
anything and click on replace all

Thanks

 

[Ivyleaf]

Hi G,

In this instance, =DATEVALUE() and =TIMEVALUE() should be exactly what
you are after. Not only will they remove the spaces, but they will
convert the data to Excel date and time values which you can then
format the cells as date / time respectively.

Cheers,
Ivan.