Active2 years, 6 months ago
The answer is to use Control+Option+Enter, which will create a line break in the cell. As an alternative, Control+Command+Enter can also be used to achieve the same. These combinations work both in Excel for Mac 2011 and the new Excel for Mac 2015. As pointed out by Shameer in this answer, Alt+Enter now seems to work as well in the new Excel for Mac 2015. Adding a Line Break in Plain Text. This one is easy. While you’re editing the text of a cell, you can simply press Alt+Enter (or Command+Option+Enter for Mac) to add a line break as shown below. Excel allows you to wrap text within a cell or within a merged set of cells. But it also allows you to force line-breaks within cells or merged cells. If you want to create the equivalent of a carriage return within an Excel cell, hit ALT+Enter. The cursor will stay within the cell being edited, but will drop to the next line. In Excel, 2010 or any other version probably, if I enter in a cell, a long single-line text that is longer than the width of the cell, Excel sometimes render the text across the next adjacent cells; some other times, it gets cut off at the boundary with the adjacent cell to the right.
How can I get the displayed value of a cell in MS Excel ( for text that was converted to dates)?
Excel File Origin
I have an Excel file that was originally a CSV file. At this time I don't have access to the original CSV file. When the CSV file was originally opened in Excel, many of cells in the
Date
column were automatically converted.Example Values
Here is an example of some of the items from the
Date
column ( spacing represents left and right aligned cells, mostly to show that the second date was not converted):exhibit 1:
And if I choose one of those cells and right click and select formatting I will get different things:
Possible Logic
So looks like excel hasn't converted the 12/01/1882 date - maybe because it is 'too old' or maybe for another reason.
If I select the whole column and change it to 'General' formatting it then gives me this ( text left aligned, numbers right aligned):
What I Want
I'd like to have one column where I have the format set to 'General' or 'Text' and it would have the dates that were displayed from exhibit 1 above.
I saw this question about using a DataFormatter class (looks like Java?) and some similar questions that use Macros and VB scripting - but if at all possible I would really like a way to use an excel function to do this.
What I've tried
I've tried using these things which have not worked:
CONCATENATE
- to get the date field, and prepend it with a spaceINDIRECT(ADDRESS(ROW(),5))
- get the value indirectlyVALUE()
- get the 'value' of the cell - opposite of what I wantTEXT()
- can explicitly specify format but still can't make it workCELL()
- can be used to get cell information such as 'contents' - no luck with this but I did see that I could use it also to get format
which would tell me if the cell is displayed as a number or as 'general' which could then possibly used with an IF
function somehow to convert a number that is displayed as a date back to an actual date... possibly?Copy Paste Method
One method I saw which seems to work is to select the values, copy and paste them into notepad, and then copy and paste them back into excel (it effectively gives the displayed value - which is what I want )
The only problem is that I would like to have a function to do this because the excel sheet has a few hundred thousand rows in it.
Community♦
cwdcwd6,5233838 gold badges9797 silver badges145145 bronze badges
3 Answers
If you have shown us the full range of possible formats, this should work:
If you have date formats other than m/d/yy, add tests for them.
ScottScott17.2k1111 gold badges4646 silver badges9292 bronze badges
A general answer:
Rather than trying to fuss with the results of the CELL('format'...) function, it may be easier to use a user-defined function to return the NumberFormat property of the cell directly.
In that case, the expression '=TEXT(A1,NumberFormat(A1))' would give you the displayed value rather directly.
![Excel Excel](/uploads/1/2/6/2/126207373/235200647.png)
To enable this, you need the following in a module of the worksheet:
NickNaylorNickNaylor
The same answer but with a different function (that has worked for me):
Just use =DisplayText(A1). If you change the cell format this function will return the displayed text
alvarocalvaroc
Not the answer you're looking for? Browse other questions tagged microsoft-excelmicrosoft-excel-2010worksheet-functiondate-time or ask your own question.
You will agree that an Excel sheet can run up to thousands of rows and columns. You will also agree that when you are working on one such worksheet, you might have to navigate into deep rows and columns. This takes you away from the reference row and column i.e cell A1, where you might need to revisit frequently.
Now, using the scrolls isn’t one of the best ways to get back to that cell. Rather, we will see three better and cool ways to do this.
![Excel formula to search for text in a cell Excel formula to search for text in a cell](/uploads/1/2/6/2/126207373/912111679.gif)
So, without further ado, let us jump to A1.
Cool Tip: Also check out our post on various ways to jump from one sheet to another. There are a lot more than what you use.
1. Using Keyboard Shortcuts
To me this is the easiest and the quickest way to get back to A1 from anywhere in the worksheet. All you need to do is press these two keys together: Ctrl + Home
Exception: This method will not take you to A1 if you have freeze panes activated on rows and columns. In such a scenario it will take you to the first row, first column after the freeze panes.Take a look at the image below. Here, rows 1 through 4 and columns A and B are in freeze mode. So pressing Ctrl + Home will take you to C5.
2. Using Go To Dialog Box
This method has an extra step and takes you a bit longer to get to A1. However, unlike method 1, it assures your jump to A1 no matter where you are and no matter what’s in freeze mode.
Step 1: Press either F5 or the combination Ctrl + G. That will launch the Go To dialog box.
Step 2: On the dialog box, under Reference text box, type A1 and hit Enter or click on the OK button.
Quick Tip: This method can be used to navigate to any cell quickly. All you need to know is the reference of the cell you want to jump to.3. Using Name Box
We know that MS Excel has a name box that displays the reference of the cell you are in. You can actually use this box to go to cell A1.
Excel Formula To Separate Text In A Cell
To do that, type A1 in the name box and hit Enter. Immediately, the reference will shift to A1.
There is a second trick here. You can actually define a name for cell A1, say, for example, Home. Then you can click on the name box drop down and select Home to jump to A1.
Conclusion
How often do you navigate away from the A1 reference? And how often are you required to get back? If the answer is, “very often”, you need to remember these tricks. Not all of them though, you can pick the ones you like the most or are comfortable with most.
Know of any other similar trick? Do share.
Also See#Lists #msexcel Did You Know
The world's first ZIP file was invented in 1986 by Philip Katz.