Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting US Date Format | Excel Worksheet Functions | |||
Copy from a Cell to a text box. | Excel Worksheet Functions | |||
Mileage Claim Formula | New Users to Excel | |||
Concatening a text and a date | Excel Worksheet Functions | |||
Formulas dealing with text data | Excel Worksheet Functions |