Converting TEXT to DATE
 

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 "<sp12 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 "<sp9:<sp34" 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

Converting TEXT to DATE
 

Hi

Assuming data is in column A, use a separate column as a helper column
and enter
=--TRIM(A1)
and copy down.
Copy this new column of values and Paste SpecialValues back over
original data.
Mark the range of this newly pasted data and
FormatCellsNumberCustomdd mmm yy
Delete helper column


For the Time problem use
=--SUBSTITUTE(A1," ","")
and repeat steps above but format as h:mm

--
Regards

Roger Govier


"GFH" wrote in message
...
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 "<sp12 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 "<sp9:<sp34" 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

Converting TEXT to DATE
 

ASAP Utilities, a free Add-in available at www.asap-utilities.com has a
feature that will allow the deletion of any selected characters..........

Vaya con Dios,
Chuck, CABGx3



"GFH" wrote:

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 "<sp12 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 "<sp9:<sp34" 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

Converting TEXT to DATE
 

Try editreplace find: <sp and leave replace box blank.

To remove just leading or trailing spaces use datatext to columns:
step 1. fixed width then click next
step 2. remove vertical lines then click finish

GFH wrote:
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 "<sp12 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 "<sp9:<sp34" 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