excel-formula convert - Excel Date to String conversion
number format (9)
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
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 -> 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
In a cell in Excel sheet I have a Date value like:
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?
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 (&), etc.)
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
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)&"/"&MONTH(A2)&"/"&YEAR(A2)& " "&HOUR(B2)&":"&MINUTE(B2)&":"&SECOND(B2)
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.
In some contexts using a ' character beforehand will work, but if you save to CSV and load again this is impossible.
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
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.
Well so far this is the best I could come up with.
It uses the
ISBLANK function to check if the cell is truly empty within an
If there is anything in the cell,
A1 in this example, even a SPACE character,
then the cell is not
EMPTY and the calculation will result.
This will keep the calculation errors from showing until you have numbers to work with.
If the cell is
EMPTY then the calculation cell will not display the errors from the calculation.If the cell is
NOT EMPTY then the calculation results will be displayed.
This will throw an error if your data is bad, the dreaded
Change the cell references and formula as you need to.