Cell Format

[iamnu]

I want to enter <shift>: in a cell (this would be Shift Key and the
colon character to enter a date).

And then automatically format this cell so that the date is on one
line, and the value "Critical Date" on the second line.

So the cell value would look like this:

05/25/2010
Critical Date

Can someone explain how to do this? If it can be done with code, I
would be interested in that also...

 

[Dave Peterson]

First, ctrl-: (control-colon) will enter the time, not the date.

You want ctrl-; (control-semicolon) to enter the date.

Second, you can use a custom number format to get what you want.

Format the cell (either before or after you enter the value).
Format|cells|Number tab|Custom category:
Type:
mm/dd/yyyy(ctrl-j)"Critical Date"

Don't type (ctrl-j). Hit and hold the ctrl key while you hit the j key.

Then on the alignment tab:
Check Shrink to fit
Check Wrap text
It's important you do the shrink to fit option first.

Then close up that dialog.

You'll have to adjust the rowheight and column width manually -- and remember to
do this each time you autofit the row/column that contains that cell.

 

[steve]

Hi, you could try using a macro but that has to be a ctrl key + letter
combination, the macro would then contain
Sub SplitDate()
Selection.Value = Date & Chr(10) & "Critical Date"
End Sub

Hope this helps

 

[iamnu]

First, ctrl-: (control-colon) will enter the time, not the date.

You want ctrl-; (control-semicolon) to enter the date.

Second, you can use a custom number format to get what you want.

Format the cell (either before or after you enter the value).
Format|cells|Number tab|Custom category:
Type:
mm/dd/yyyy(ctrl-j)"Critical Date"

Don't type (ctrl-j).  Hit and hold the ctrl key while you hit the j key..

Then on the alignment tab:
Check Shrink to fit
Check Wrap text
It's important you do the shrink to fit option first.

Then close up that dialog.

You'll have to adjust the rowheight and column width manually -- and remember to
do this each time you autofit the row/column that contains that cell.

Excellent! Thanks for your help.

The key here was the "j" character. How did you know this? Is there
somewhere I can go to find other such characters that represent
keyboard controls?

 

[Gord Dibben]

Dave's ctrl+j is the equivalent of a line feed in a cell.

Could also have used Alt + 0010 for a line feed.

Not too many shortcuts like ctrl + j for entering characters.

But there are many using Alt + xxxx

Open Insert>Symbol to see the character codes in ASCII(decimal) to use with
the Alt key.

Or open Windows CharMap


Gord Dibben MS Excel MVP