Excel Date to String conversion

Question

In a cell in Excel sheet I have a Date value like   01 01 2010 14 30 00   I want to convert that Date to Text and also want the Text to look exactly like Date  So a Date value of 01 01 2010 14 30 00 should look like 01 01 2010 14 30 00 but internally it should be Text   How can I do that in Excel

User · Answer

In some contexts using a   character beforehand will work  but if you save to CSV and load again this is impossible    01 01 2010 14 30 00

User · Answer

Here is a VBA approach   Sub change       toText Sheets 1  Range  A1 F20   End Sub  Sub toText target As Range  Dim cell As Range     For Each cell In target         cell Value   cell Text         cell NumberFormat           Next cell End Sub   If you are looking for a solution without programming  the Question should be moved to SuperUser

User · Answer

TEXT A1  DD MM YYYY hh mm ss      24 hour time    TEXT A1  DD MM YYYY hh mm ss AM PM      standard time

User · Answer

Couldnt get the TEXT   formula to work  Easiest solution was to copy paste into Notepad and back into Excel with the column set to Text before pasting back  Or you can do the same with a formula like this   DAY A2  amp     amp MONTH A2  amp     amp YEAR A2  amp      amp HOUR B2  amp     amp MINUTE B2  amp     amp SECOND B2

User · Answer

I have no idea about the year of publication of the question  it might be old now  So  I expect my answer to be more of a reference for future similar questions after my post  I don t know if anybody out there has already given an answer similar to the one I am about to give  which might result -I think- being the simplest  most direct and most effective  If someone has already given it  I apologize  but I haven t seen it  Here  my answer using CStr instead of TEXT  Asuming cell A1 contains a date  and using VBA code   Dim strDate As String   Convert to string the value contained in A1  a date  strDate   CStr  A1  Value    You can  thereafter  manipulate it as any ordinary string using string functions  MID  LEFT  RIGHT  LEN  CONCATENATE   amp    etc

User · Answer

Here s another option   Use Excel s built in  Text to Columns  wizard   It s found under the Data tab in Excel 2007   If you have one column selected  the defaults for file type and delimiters should work  then it prompts you to change the data format of the column   Choosing text forces it to text format  to make sure that it s not stored as a date

User · Answer

In Excel 2010  marg s answer only worked for some of the data I had in my spreadsheet  it was imported   The following solution worked on all data   Sub change       toText Selection End Sub  Sub toText target As range  Dim cell As range Dim txt As String     For Each cell In target         txt   cell text         cell NumberFormat               cell Value2   txt     Next cell End Sub

User · Answer

If you are not using programming then do the following  1  select the column  2  right click and select Format Cells  3  Select  Custom   4  Just Under  Type   type  dd mm yyyy hh mm ss

User · Answer

The selected answer did not work for me as Excel was still not converting the text to date  Here is my solution   Say that in the first column  A  you have data of the type 2016 03 25 21 20 00 but is stored as text  Then in column B write  DATEVALUE A1  and in column C write  TIMEVALUE A1     Then in column D do  B1 C1 to add the numerical formats of the date and time   Finally  copy the values from D into column E by right clicking in column E and select Paste Special - gt  Paste as Values   Highlight the numerical values in column E and change the data type to date - I prefer using a custom date of the form YYYY-MM-DD HH MM SS

[excel] Excel Date to String conversion

Examples related to excel

Examples related to excel-formula