Hello Fellow Excellers.
When I enter a date in a cell and give it a long format. e.g. dddd dd mmmm
yyyy, so that it would read Monday 1 January 2006, then the wrap text
facility does not work.
Is there any way to put in an 'alt-enter' type character so that I could
have the Monday on the first line and the rest underneath.
--
Your help is and always has been very much appreciated.
Thanking you in anticipation.
--- --- ---
Big Rick
Not exactly what you're after, but perhaps worth a try
(Hang around for views from others ..)
Assuming dates entered in A1 down
Put in B1:
=TEXT(A1,"dddd")&CHAR(10)&TEXT(A1,"dd mmmm yyyy")
Format B1 to wrap text & copy B1 down
Col B will return the desired display
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Big Rick" <[email protected]> wrote in message
news:[email protected]...
> Hello Fellow Excellers.
> When I enter a date in a cell and give it a long format. e.g. dddd dd mmmm
> yyyy, so that it would read Monday 1 January 2006, then the wrap text
> facility does not work.
> Is there any way to put in an 'alt-enter' type character so that I could
> have the Monday on the first line and the rest underneath.
> --
> Your help is and always has been very much appreciated.
> Thanking you in anticipation.
> --- --- ---
> Big Rick
Many thanks.
This is a lot further than I would of got.
I would still like a solution if possible for the way described, as I would
like to use it in other items, but your formula does work on this occasion.
I am forever grateful and in your debt.
--
Big Rick
"Max" wrote:
> Not exactly what you're after, but perhaps worth a try
> (Hang around for views from others ..)
>
> Assuming dates entered in A1 down
>
> Put in B1:
> =TEXT(A1,"dddd")&CHAR(10)&TEXT(A1,"dd mmmm yyyy")
> Format B1 to wrap text & copy B1 down
> Col B will return the desired display
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> "Big Rick" <[email protected]> wrote in message
> news:[email protected]...
> > Hello Fellow Excellers.
> > When I enter a date in a cell and give it a long format. e.g. dddd dd mmmm
> > yyyy, so that it would read Monday 1 January 2006, then the wrap text
> > facility does not work.
> > Is there any way to put in an 'alt-enter' type character so that I could
> > have the Monday on the first line and the rest underneath.
> > --
> > Your help is and always has been very much appreciated.
> > Thanking you in anticipation.
> > --- --- ---
> > Big Rick
>
>
>
You're welcome !
As mentioned in the response,
do hang around awhile for views from others.
There just might be a way to do it directly (perhaps via vba ?)
that others may step-in to offer you.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Big Rick" <[email protected]> wrote in message
news:[email protected]...
> Many thanks.
> This is a lot further than I would of got.
> I would still like a solution if possible for the way described, as I
would
> like to use it in other items, but your formula does work on this
occasion.
>
> I am forever grateful and in your debt.
> --
> Big Rick
One way
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If IsDate(.Value) Then
If .NumberFormat = "dddd dd mmmm yyyy" Then
.Value = Format(.Value, "dddd " & vbLf & "dd mmmm yyyy")
End If
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
"Max" <[email protected]> wrote in message
news:[email protected]...
> You're welcome !
>
> As mentioned in the response,
> do hang around awhile for views from others.
>
> There just might be a way to do it directly (perhaps via vba ?)
> that others may step-in to offer you.
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> "Big Rick" <[email protected]> wrote in message
> news:[email protected]...
> > Many thanks.
> > This is a lot further than I would of got.
> > I would still like a solution if possible for the way described, as I
> would
> > like to use it in other items, but your formula does work on this
> occasion.
> >
> > I am forever grateful and in your debt.
> > --
> > Big Rick
>
>
Dear Bob
I'm afraid that I have never got to grips with any form of VBA before. I
have followed the on screen insructions and pasted the code into the sheet1
tab, but where do I go from there.
What I have done is simply pasted the code in, closed the screen, and
entered a date in H1 but the wrap is not working. I have still formatted the
cell as wrap text but this did not help. I know that I am going wrong
somewhere, but I know that you are the one to guide me through.
Best Regards
--
--
Big Rick
"Bob Phillips" wrote:
> One way
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Const WS_RANGE As String = "H1:H10"
>
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
> With Target
> If IsDate(.Value) Then
> If .NumberFormat = "dddd dd mmmm yyyy" Then
> .Value = Format(.Value, "dddd " & vbLf & "dd mmmm yyyy")
> End If
> End If
> End With
> End If
>
> ws_exit:
> Application.EnableEvents = True
> End Sub
>
> 'This is worksheet event code, which means that it needs to be
> 'placed in the appropriate worksheet code module, not a standard
> 'code module. To do this, right-click on the sheet tab, select
> 'the View Code option from the menu, and paste the code in.
>
>
>
>
> --
> HTH
>
> Bob Phillips
>
You can use format|cells|number tab
Give it a custom format of:
dddd(alt-0010)dd mmmm yyyy
Hit and hold the alt key while typing 0010 on the numeric keypad--not above the
QWERTY keys.
And turn on wrap text
format|cells|alignment tab
But be aware that you'll have to adjust the rowheight yourself (autofitting
won't work).
And if you make the cell too narrow for the whole string (not after wrapping
text), you'll see ###'s.
====
Another format to consider:
dddd* dd mmmm yyyy
This works pretty neat when you widen the column.
Big Rick wrote:
>
> Hello Fellow Excellers.
> When I enter a date in a cell and give it a long format. e.g. dddd dd mmmm
> yyyy, so that it would read Monday 1 January 2006, then the wrap text
> facility does not work.
> Is there any way to put in an 'alt-enter' type character so that I could
> have the Monday on the first line and the rest underneath.
> --
> Your help is and always has been very much appreciated.
> Thanking you in anticipation.
> --- --- ---
> Big Rick
--
Dave Peterson
Hi Rick
Copy the following, then right click on Worksheet Tab, Select View code
and Paste into the white pane.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Target.Row <2 Then Exit Sub
If Target.Column > 1 Then Exit Sub
If Not Intersect(Me.Range("A:A"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Cells(.Row, "A")
.Value = Format(.Value, "dddd" & vbLf & "d mmmm yyyy")
End With
Application.EnableEvents = True
End If
End With
End Sub
This assumes that you want all entries in Column A to be adjusted.
If you want a different column, change "A"'s to the column Letter
required, change Target.Column number to <> and the column number for
the entry.
Target.row is set to ignore row 1, assuming this is your header, change
as appropriate.
--
Regards
Roger Govier
"Max" <[email protected]> wrote in message
news:[email protected]...
> You're welcome !
>
> As mentioned in the response,
> do hang around awhile for views from others.
>
> There just might be a way to do it directly (perhaps via vba ?)
> that others may step-in to offer you.
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> "Big Rick" <[email protected]> wrote in message
> news:[email protected]...
>> Many thanks.
>> This is a lot further than I would of got.
>> I would still like a solution if possible for the way described, as I
> would
>> like to use it in other items, but your formula does work on this
> occasion.
>>
>> I am forever grateful and in your debt.
>> --
>> Big Rick
>
>
If you copied the code to the sheet module as described, you should have got
the two lines as required. It worked fine in my tests.
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
"Big Rick" <[email protected]> wrote in message
news:[email protected]...
> Dear Bob
> I'm afraid that I have never got to grips with any form of VBA before. I
> have followed the on screen insructions and pasted the code into the
sheet1
> tab, but where do I go from there.
> What I have done is simply pasted the code in, closed the screen, and
> entered a date in H1 but the wrap is not working. I have still formatted
the
> cell as wrap text but this did not help. I know that I am going wrong
> somewhere, but I know that you are the one to guide me through.
>
> Best Regards
> --
> --
> Big Rick
>
>
> "Bob Phillips" wrote:
>
> > One way
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Const WS_RANGE As String = "H1:H10"
> >
> > On Error GoTo ws_exit:
> > Application.EnableEvents = False
> > If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
> > With Target
> > If IsDate(.Value) Then
> > If .NumberFormat = "dddd dd mmmm yyyy" Then
> > .Value = Format(.Value, "dddd " & vbLf & "dd mmmm
yyyy")
> > End If
> > End If
> > End With
> > End If
> >
> > ws_exit:
> > Application.EnableEvents = True
> > End Sub
> >
> > 'This is worksheet event code, which means that it needs to be
> > 'placed in the appropriate worksheet code module, not a standard
> > 'code module. To do this, right-click on the sheet tab, select
> > 'the View Code option from the menu, and paste the code in.
> >
> >
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
Hey, that's quite sneaky Dave.
I would never have thought about entering control characters into the
number format.
Very effective.
--
Regards
Roger Govier
"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> You can use format|cells|number tab
>
> Give it a custom format of:
> dddd(alt-0010)dd mmmm yyyy
>
> Hit and hold the alt key while typing 0010 on the numeric keypad--not
> above the
> QWERTY keys.
>
> And turn on wrap text
> format|cells|alignment tab
>
> But be aware that you'll have to adjust the rowheight yourself
> (autofitting
> won't work).
>
> And if you make the cell too narrow for the whole string (not after
> wrapping
> text), you'll see ###'s.
>
> ====
> Another format to consider:
> dddd* dd mmmm yyyy
> This works pretty neat when you widen the column.
>
>
>
> Big Rick wrote:
>>
>> Hello Fellow Excellers.
>> When I enter a date in a cell and give it a long format. e.g. dddd dd
>> mmmm
>> yyyy, so that it would read Monday 1 January 2006, then the wrap text
>> facility does not work.
>> Is there any way to put in an 'alt-enter' type character so that I
>> could
>> have the Monday on the first line and the rest underneath.
>> --
>> Your help is and always has been very much appreciated.
>> Thanking you in anticipation.
>> --- --- ---
>> Big Rick
>
> --
>
> Dave Peterson
Dear Bob, Max, Dave & Roger
I am forever grateful for all your time, effort and patience for someone who
has to have something drummed in before it sticks in my mind.
I did get Rogers code to work, I couldn't with Bobs', although I sure that
it is because a complete amateur is trying.
If anyone of you are ever in England, then the drinks are on me !
Regards
--
Big Rick
"Dave Peterson" wrote:
> You can use format|cells|number tab
>
> Give it a custom format of:
> dddd(alt-0010)dd mmmm yyyy
>
> Hit and hold the alt key while typing 0010 on the numeric keypad--not above the
> QWERTY keys.
>
> And turn on wrap text
> format|cells|alignment tab
>
> But be aware that you'll have to adjust the rowheight yourself (autofitting
> won't work).
>
> And if you make the cell too narrow for the whole string (not after wrapping
> text), you'll see ###'s.
>
> ====
> Another format to consider:
> dddd* dd mmmm yyyy
> This works pretty neat when you widen the column.
>
>
>
> Big Rick wrote:
> >
> > Hello Fellow Excellers.
> > When I enter a date in a cell and give it a long format. e.g. dddd dd mmmm
> > yyyy, so that it would read Monday 1 January 2006, then the wrap text
> > facility does not work.
> > Is there any way to put in an 'alt-enter' type character so that I could
> > have the Monday on the first line and the rest underneath.
> > --
> > Your help is and always has been very much appreciated.
> > Thanking you in anticipation.
> > --- --- ---
> > Big Rick
>
> --
>
> Dave Peterson
>
Hi Big Rick
> If anyone of you are ever in England, then the drinks are on me !
Be careful there,
Bob is in Dorset, I am in Wales (but often across the border)
Don't know about Bob, but I can drink quite a lot of beer!! <vbg>
--
Regards
Roger Govier
"Big Rick" <[email protected]> wrote in message
news:[email protected]...
> Dear Bob, Max, Dave & Roger
> I am forever grateful for all your time, effort and patience for
> someone who
> has to have something drummed in before it sticks in my mind.
> I did get Rogers code to work, I couldn't with Bobs', although I sure
> that
> it is because a complete amateur is trying.
> If anyone of you are ever in England, then the drinks are on me !
>
> Regards
> --
> Big Rick
>
>
> "Dave Peterson" wrote:
>
>> You can use format|cells|number tab
>>
>> Give it a custom format of:
>> dddd(alt-0010)dd mmmm yyyy
>>
>> Hit and hold the alt key while typing 0010 on the numeric keypad--not
>> above the
>> QWERTY keys.
>>
>> And turn on wrap text
>> format|cells|alignment tab
>>
>> But be aware that you'll have to adjust the rowheight yourself
>> (autofitting
>> won't work).
>>
>> And if you make the cell too narrow for the whole string (not after
>> wrapping
>> text), you'll see ###'s.
>>
>> ====
>> Another format to consider:
>> dddd* dd mmmm yyyy
>> This works pretty neat when you widen the column.
>>
>>
>>
>> Big Rick wrote:
>> >
>> > Hello Fellow Excellers.
>> > When I enter a date in a cell and give it a long format. e.g. dddd
>> > dd mmmm
>> > yyyy, so that it would read Monday 1 January 2006, then the wrap
>> > text
>> > facility does not work.
>> > Is there any way to put in an 'alt-enter' type character so that I
>> > could
>> > have the Monday on the first line and the rest underneath.
>> > --
>> > Your help is and always has been very much appreciated.
>> > Thanking you in anticipation.
>> > --- --- ---
>> > Big Rick
>>
>> --
>>
>> Dave Peterson
>>
I work at a swimming pool in Blackpool.
There are 300,000 gallons of 'drink' to go at !!
--
Big Rick
"Roger Govier" wrote:
> Hi Big Rick
>
> > If anyone of you are ever in England, then the drinks are on me !
> Be careful there,
> Bob is in Dorset, I am in Wales (but often across the border)
> Don't know about Bob, but I can drink quite a lot of beer!! <vbg>
>
> --
> Regards
>
> Roger Govier
>
>
> "Big Rick" <[email protected]> wrote in message
> news:[email protected]...
> > Dear Bob, Max, Dave & Roger
> > I am forever grateful for all your time, effort and patience for
> > someone who
> > has to have something drummed in before it sticks in my mind.
> > I did get Rogers code to work, I couldn't with Bobs', although I sure
> > that
> > it is because a complete amateur is trying.
> > If anyone of you are ever in England, then the drinks are on me !
> >
> > Regards
> > --
> > Big Rick
> >
> >
> > "Dave Peterson" wrote:
> >
> >> You can use format|cells|number tab
> >>
> >> Give it a custom format of:
> >> dddd(alt-0010)dd mmmm yyyy
> >>
> >> Hit and hold the alt key while typing 0010 on the numeric keypad--not
> >> above the
> >> QWERTY keys.
> >>
> >> And turn on wrap text
> >> format|cells|alignment tab
> >>
> >> But be aware that you'll have to adjust the rowheight yourself
> >> (autofitting
> >> won't work).
> >>
> >> And if you make the cell too narrow for the whole string (not after
> >> wrapping
> >> text), you'll see ###'s.
> >>
> >> ====
> >> Another format to consider:
> >> dddd* dd mmmm yyyy
> >> This works pretty neat when you widen the column.
> >>
> >>
> >>
> >> Big Rick wrote:
> >> >
> >> > Hello Fellow Excellers.
> >> > When I enter a date in a cell and give it a long format. e.g. dddd
> >> > dd mmmm
> >> > yyyy, so that it would read Monday 1 January 2006, then the wrap
> >> > text
> >> > facility does not work.
> >> > Is there any way to put in an 'alt-enter' type character so that I
> >> > could
> >> > have the Monday on the first line and the rest underneath.
> >> > --
> >> > Your help is and always has been very much appreciated.
> >> > Thanking you in anticipation.
> >> > --- --- ---
> >> > Big Rick
> >>
> >> --
> >>
> >> Dave Peterson
> >>
>
>
>
One of the nicer things about using format is that the value stays a date, too.
So it makes doing date arithmetic a little easier if you need it later.
Roger Govier wrote:
>
> Hey, that's quite sneaky Dave.
> I would never have thought about entering control characters into the
> number format.
> Very effective.
>
> --
> Regards
>
> Roger Govier
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > You can use format|cells|number tab
> >
> > Give it a custom format of:
> > dddd(alt-0010)dd mmmm yyyy
> >
> > Hit and hold the alt key while typing 0010 on the numeric keypad--not
> > above the
> > QWERTY keys.
> >
> > And turn on wrap text
> > format|cells|alignment tab
> >
> > But be aware that you'll have to adjust the rowheight yourself
> > (autofitting
> > won't work).
> >
> > And if you make the cell too narrow for the whole string (not after
> > wrapping
> > text), you'll see ###'s.
> >
> > ====
> > Another format to consider:
> > dddd* dd mmmm yyyy
> > This works pretty neat when you widen the column.
> >
> >
> >
> > Big Rick wrote:
> >>
> >> Hello Fellow Excellers.
> >> When I enter a date in a cell and give it a long format. e.g. dddd dd
> >> mmmm
> >> yyyy, so that it would read Monday 1 January 2006, then the wrap text
> >> facility does not work.
> >> Is there any way to put in an 'alt-enter' type character so that I
> >> could
> >> have the Monday on the first line and the rest underneath.
> >> --
> >> Your help is and always has been very much appreciated.
> >> Thanking you in anticipation.
> >> --- --- ---
> >> Big Rick
> >
> > --
> >
> > Dave Peterson
--
Dave Peterson
Hi Big Rick
I won't beat a trail up the M6 too quickly then<g>
--
Regards
Roger Govier
"Big Rick" <[email protected]> wrote in message
news:[email protected]...
>I work at a swimming pool in Blackpool.
> There are 300,000 gallons of 'drink' to go at !!
> --
> Big Rick
>
>
> "Roger Govier" wrote:
>
>> Hi Big Rick
>>
>> > If anyone of you are ever in England, then the drinks are on me !
>> Be careful there,
>> Bob is in Dorset, I am in Wales (but often across the border)
>> Don't know about Bob, but I can drink quite a lot of beer!! <vbg>
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "Big Rick" <[email protected]> wrote in message
>> news:[email protected]...
>> > Dear Bob, Max, Dave & Roger
>> > I am forever grateful for all your time, effort and patience for
>> > someone who
>> > has to have something drummed in before it sticks in my mind.
>> > I did get Rogers code to work, I couldn't with Bobs', although I
>> > sure
>> > that
>> > it is because a complete amateur is trying.
>> > If anyone of you are ever in England, then the drinks are on me !
>> >
>> > Regards
>> > --
>> > Big Rick
>> >
>> >
>> > "Dave Peterson" wrote:
>> >
>> >> You can use format|cells|number tab
>> >>
>> >> Give it a custom format of:
>> >> dddd(alt-0010)dd mmmm yyyy
>> >>
>> >> Hit and hold the alt key while typing 0010 on the numeric
>> >> keypad--not
>> >> above the
>> >> QWERTY keys.
>> >>
>> >> And turn on wrap text
>> >> format|cells|alignment tab
>> >>
>> >> But be aware that you'll have to adjust the rowheight yourself
>> >> (autofitting
>> >> won't work).
>> >>
>> >> And if you make the cell too narrow for the whole string (not
>> >> after
>> >> wrapping
>> >> text), you'll see ###'s.
>> >>
>> >> ====
>> >> Another format to consider:
>> >> dddd* dd mmmm yyyy
>> >> This works pretty neat when you widen the column.
>> >>
>> >>
>> >>
>> >> Big Rick wrote:
>> >> >
>> >> > Hello Fellow Excellers.
>> >> > When I enter a date in a cell and give it a long format. e.g.
>> >> > dddd
>> >> > dd mmmm
>> >> > yyyy, so that it would read Monday 1 January 2006, then the wrap
>> >> > text
>> >> > facility does not work.
>> >> > Is there any way to put in an 'alt-enter' type character so that
>> >> > I
>> >> > could
>> >> > have the Monday on the first line and the rest underneath.
>> >> > --
>> >> > Your help is and always has been very much appreciated.
>> >> > Thanking you in anticipation.
>> >> > --- --- ---
>> >> > Big Rick
>> >>
>> >> --
>> >>
>> >> Dave Peterson
>> >>
>>
>>
>>
Roger,
You know that all Brits can drink! The world is astounded at our capacity.
Bob
"Roger Govier" <[email protected]> wrote in message
news:[email protected]...
> Hi Big Rick
>
> > If anyone of you are ever in England, then the drinks are on me !
> Be careful there,
> Bob is in Dorset, I am in Wales (but often across the border)
> Don't know about Bob, but I can drink quite a lot of beer!! <vbg>
>
> --
> Regards
>
> Roger Govier
>
>
> "Big Rick" <[email protected]> wrote in message
> news:[email protected]...
> > Dear Bob, Max, Dave & Roger
> > I am forever grateful for all your time, effort and patience for
> > someone who
> > has to have something drummed in before it sticks in my mind.
> > I did get Rogers code to work, I couldn't with Bobs', although I sure
> > that
> > it is because a complete amateur is trying.
> > If anyone of you are ever in England, then the drinks are on me !
> >
> > Regards
> > --
> > Big Rick
> >
> >
> > "Dave Peterson" wrote:
> >
> >> You can use format|cells|number tab
> >>
> >> Give it a custom format of:
> >> dddd(alt-0010)dd mmmm yyyy
> >>
> >> Hit and hold the alt key while typing 0010 on the numeric keypad--not
> >> above the
> >> QWERTY keys.
> >>
> >> And turn on wrap text
> >> format|cells|alignment tab
> >>
> >> But be aware that you'll have to adjust the rowheight yourself
> >> (autofitting
> >> won't work).
> >>
> >> And if you make the cell too narrow for the whole string (not after
> >> wrapping
> >> text), you'll see ###'s.
> >>
> >> ====
> >> Another format to consider:
> >> dddd* dd mmmm yyyy
> >> This works pretty neat when you widen the column.
> >>
> >>
> >>
> >> Big Rick wrote:
> >> >
> >> > Hello Fellow Excellers.
> >> > When I enter a date in a cell and give it a long format. e.g. dddd
> >> > dd mmmm
> >> > yyyy, so that it would read Monday 1 January 2006, then the wrap
> >> > text
> >> > facility does not work.
> >> > Is there any way to put in an 'alt-enter' type character so that I
> >> > could
> >> > have the Monday on the first line and the rest underneath.
> >> > --
> >> > Your help is and always has been very much appreciated.
> >> > Thanking you in anticipation.
> >> > --- --- ---
> >> > Big Rick
> >>
> >> --
> >>
> >> Dave Peterson
> >>
>
>
Hi Bob
Then before too long, I must travel down to your neck of the woods and
enjoy a pint of Badger with you.
--
Regards
Roger Govier
"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Roger,
>
> You know that all Brits can drink! The world is astounded at our
> capacity.
>
> Bob
>
>
> "Roger Govier" <[email protected]> wrote in message
> news:[email protected]...
>> Hi Big Rick
>>
>> > If anyone of you are ever in England, then the drinks are on me !
>> Be careful there,
>> Bob is in Dorset, I am in Wales (but often across the border)
>> Don't know about Bob, but I can drink quite a lot of beer!! <vbg>
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "Big Rick" <[email protected]> wrote in message
>> news:[email protected]...
>> > Dear Bob, Max, Dave & Roger
>> > I am forever grateful for all your time, effort and patience for
>> > someone who
>> > has to have something drummed in before it sticks in my mind.
>> > I did get Rogers code to work, I couldn't with Bobs', although I
>> > sure
>> > that
>> > it is because a complete amateur is trying.
>> > If anyone of you are ever in England, then the drinks are on me !
>> >
>> > Regards
>> > --
>> > Big Rick
>> >
>> >
>> > "Dave Peterson" wrote:
>> >
>> >> You can use format|cells|number tab
>> >>
>> >> Give it a custom format of:
>> >> dddd(alt-0010)dd mmmm yyyy
>> >>
>> >> Hit and hold the alt key while typing 0010 on the numeric
>> >> keypad--not
>> >> above the
>> >> QWERTY keys.
>> >>
>> >> And turn on wrap text
>> >> format|cells|alignment tab
>> >>
>> >> But be aware that you'll have to adjust the rowheight yourself
>> >> (autofitting
>> >> won't work).
>> >>
>> >> And if you make the cell too narrow for the whole string (not
>> >> after
>> >> wrapping
>> >> text), you'll see ###'s.
>> >>
>> >> ====
>> >> Another format to consider:
>> >> dddd* dd mmmm yyyy
>> >> This works pretty neat when you widen the column.
>> >>
>> >>
>> >>
>> >> Big Rick wrote:
>> >> >
>> >> > Hello Fellow Excellers.
>> >> > When I enter a date in a cell and give it a long format. e.g.
>> >> > dddd
>> >> > dd mmmm
>> >> > yyyy, so that it would read Monday 1 January 2006, then the wrap
>> >> > text
>> >> > facility does not work.
>> >> > Is there any way to put in an 'alt-enter' type character so that
>> >> > I
>> >> > could
>> >> > have the Monday on the first line and the rest underneath.
>> >> > --
>> >> > Your help is and always has been very much appreciated.
>> >> > Thanking you in anticipation.
>> >> > --- --- ---
>> >> > Big Rick
>> >>
>> >> --
>> >>
>> >> Dave Peterson
>> >>
>>
>>
>
>
It is good that it keeps the original value, but the fact that you have to
allow cell width to cater for the full text string nullifies the usefulness
of this technique IMO.
Bob
"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> One of the nicer things about using format is that the value stays a date,
too.
> So it makes doing date arithmetic a little easier if you need it later.
>
>
>
> Roger Govier wrote:
> >
> > Hey, that's quite sneaky Dave.
> > I would never have thought about entering control characters into the
> > number format.
> > Very effective.
> >
> > --
> > Regards
> >
> > Roger Govier
> >
> > "Dave Peterson" <[email protected]> wrote in message
> > news:[email protected]...
> > > You can use format|cells|number tab
> > >
> > > Give it a custom format of:
> > > dddd(alt-0010)dd mmmm yyyy
> > >
> > > Hit and hold the alt key while typing 0010 on the numeric keypad--not
> > > above the
> > > QWERTY keys.
> > >
> > > And turn on wrap text
> > > format|cells|alignment tab
> > >
> > > But be aware that you'll have to adjust the rowheight yourself
> > > (autofitting
> > > won't work).
> > >
> > > And if you make the cell too narrow for the whole string (not after
> > > wrapping
> > > text), you'll see ###'s.
> > >
> > > ====
> > > Another format to consider:
> > > dddd* dd mmmm yyyy
> > > This works pretty neat when you widen the column.
> > >
> > >
> > >
> > > Big Rick wrote:
> > >>
> > >> Hello Fellow Excellers.
> > >> When I enter a date in a cell and give it a long format. e.g. dddd dd
> > >> mmmm
> > >> yyyy, so that it would read Monday 1 January 2006, then the wrap text
> > >> facility does not work.
> > >> Is there any way to put in an 'alt-enter' type character so that I
> > >> could
> > >> have the Monday on the first line and the rest underneath.
> > >> --
> > >> Your help is and always has been very much appreciated.
> > >> Thanking you in anticipation.
> > >> --- --- ---
> > >> Big Rick
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson
I'm not sure I'd say nullify. I (still) think that it depends on the
circumstance. If this were a header for a column full of wide descriptions,
then I wouldn't care at all.
Bob Phillips wrote:
>
> It is good that it keeps the original value, but the fact that you have to
> allow cell width to cater for the full text string nullifies the usefulness
> of this technique IMO.
>
> Bob
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > One of the nicer things about using format is that the value stays a date,
> too.
> > So it makes doing date arithmetic a little easier if you need it later.
> >
> >
> >
> > Roger Govier wrote:
> > >
> > > Hey, that's quite sneaky Dave.
> > > I would never have thought about entering control characters into the
> > > number format.
> > > Very effective.
> > >
> > > --
> > > Regards
> > >
> > > Roger Govier
> > >
> > > "Dave Peterson" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > You can use format|cells|number tab
> > > >
> > > > Give it a custom format of:
> > > > dddd(alt-0010)dd mmmm yyyy
> > > >
> > > > Hit and hold the alt key while typing 0010 on the numeric keypad--not
> > > > above the
> > > > QWERTY keys.
> > > >
> > > > And turn on wrap text
> > > > format|cells|alignment tab
> > > >
> > > > But be aware that you'll have to adjust the rowheight yourself
> > > > (autofitting
> > > > won't work).
> > > >
> > > > And if you make the cell too narrow for the whole string (not after
> > > > wrapping
> > > > text), you'll see ###'s.
> > > >
> > > > ====
> > > > Another format to consider:
> > > > dddd* dd mmmm yyyy
> > > > This works pretty neat when you widen the column.
> > > >
> > > >
> > > >
> > > > Big Rick wrote:
> > > >>
> > > >> Hello Fellow Excellers.
> > > >> When I enter a date in a cell and give it a long format. e.g. dddd dd
> > > >> mmmm
> > > >> yyyy, so that it would read Monday 1 January 2006, then the wrap text
> > > >> facility does not work.
> > > >> Is there any way to put in an 'alt-enter' type character so that I
> > > >> could
> > > >> have the Monday on the first line and the rest underneath.
> > > >> --
> > > >> Your help is and always has been very much appreciated.
> > > >> Thanking you in anticipation.
> > > >> --- --- ---
> > > >> Big Rick
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> >
> > --
> >
> > Dave Peterson
--
Dave Peterson
fantastic indeed!!!
-via135
Originally Posted by Roger Govier
> .. It worked fine in my tests.
And it worked fine for me over here, too <g>
Big Rick:
With Bob's sub implemented properly,
Select and *pre-format* the designated input range: H1:H10
as Custom: dddd dd mmmm yyyy
Then test by inputting some dates within H1:H10
When you press ENTER to commit each input,
Bob's code will return exactly what you're after
(My guess is you either didn't pre-format H1:H10 beforehand, or you might
have tried out the inputs elsewhere .. in A1, A2?? [I dunno]. But don't ask
me why Bob prefers "H1:H10" instead of "A1:A10" as the assumed source input
range <g>)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
A little "OT", sorry ..
Am trying out Dave's suggestion, but think I've forgotten
how to activate/deactivate the numeric keypad on a laptop
Thanks ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
> .. I did get Rogers code to work, I couldn't with Bobs',
Big Rick (as posted in the other branch),
With Bob's sub implemented properly,
Select and *pre-format* the designated input range: H1:H10
as Custom: dddd dd mmmm yyyy
Then test by inputting some dates within H1:H10
When you press ENTER to commit each input,
Bob's code will return exactly what you're after
(My guess is you either didn't pre-format H1:H10 beforehand, or you might
have tried out the inputs elsewhere .. in A1, A2?? [I dunno]. But don't ask
me why Bob prefers "H1:H10" instead of "A1:A10" as the assumed source input
range <g>)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Hi Max
You should have a button marked Fn and the word Numlock in coloured type
at the base of a key somewhere.
Press both, and it should activate M as 0, JKL as 123, UIO as 456 with
the number keys 789 being 789.
--
Regards
Roger Govier
"Max" <[email protected]> wrote in message
news:[email protected]...
>A little "OT", sorry ..
>
> Am trying out Dave's suggestion, but think I've forgotten
> how to activate/deactivate the numeric keypad on a laptop
>
> Thanks ..
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
>
>
My Dell has small colored numbers on the letter keys, matching Roger's
scenario, where all that's necessary is to hold the Fn key (right of the
left Ctrl key) and the <Alt> key, and then use those colored numbers.
--
Regards,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Roger Govier" <[email protected]> wrote in message
news:%[email protected]...
> Hi Max
>
> You should have a button marked Fn and the word Numlock in coloured type
> at the base of a key somewhere.
> Press both, and it should activate M as 0, JKL as 123, UIO as 456 with
> the number keys 789 being 789.
>
> --
> Regards
>
> Roger Govier
>
>
> "Max" <[email protected]> wrote in message
> news:[email protected]...
> >A little "OT", sorry ..
> >
> > Am trying out Dave's suggestion, but think I've forgotten
> > how to activate/deactivate the numeric keypad on a laptop
> >
> > Thanks ..
> > --
> > Rgds
> > Max
> > xl 97
> > ---
> > Singapore, GMT+8
> > xdemechanik
> > http://savefile.com/projects/236895
> > --
> >
> >
>
>
Thanks, Roger & RD ! With your help, think I got it <g>.
For my m/c (IBM), I had to press Shift + ScrLk to toggle
(the tiny "Numlk" appears above "ScrLk" in white)
And the numeric* keypad numbers appear in white too (no color),
albeit in "subscript" form.
*as explained in Roger's response:
> .. M as 0, JKL as 123, UIO as 456 with
> > the number keys 789 being 789.
Cheers
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Max" <[email protected]> wrote in message
news:[email protected]...
>
> (My guess is you either didn't pre-format H1:H10 beforehand, or you might
> have tried out the inputs elsewhere .. in A1, A2?? [I dunno]. But don't
ask
> me why Bob prefers "H1:H10" instead of "A1:A10" as the assumed source
input
> range <g>)
A1:A10 is boring <vbg>
In that case, why would you want the wrap-around?
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> I'm not sure I'd say nullify. I (still) think that it depends on the
> circumstance. If this were a header for a column full of wide
descriptions,
> then I wouldn't care at all.
>
>
> Bob Phillips wrote:
> >
> > It is good that it keeps the original value, but the fact that you have
to
> > allow cell width to cater for the full text string nullifies the
usefulness
> > of this technique IMO.
> >
> > Bob
> >
> > "Dave Peterson" <[email protected]> wrote in message
> > news:[email protected]...
> > > One of the nicer things about using format is that the value stays a
date,
> > too.
> > > So it makes doing date arithmetic a little easier if you need it
later.
> > >
> > >
> > >
> > > Roger Govier wrote:
> > > >
> > > > Hey, that's quite sneaky Dave.
> > > > I would never have thought about entering control characters into
the
> > > > number format.
> > > > Very effective.
> > > >
> > > > --
> > > > Regards
> > > >
> > > > Roger Govier
> > > >
> > > > "Dave Peterson" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > You can use format|cells|number tab
> > > > >
> > > > > Give it a custom format of:
> > > > > dddd(alt-0010)dd mmmm yyyy
> > > > >
> > > > > Hit and hold the alt key while typing 0010 on the numeric
keypad--not
> > > > > above the
> > > > > QWERTY keys.
> > > > >
> > > > > And turn on wrap text
> > > > > format|cells|alignment tab
> > > > >
> > > > > But be aware that you'll have to adjust the rowheight yourself
> > > > > (autofitting
> > > > > won't work).
> > > > >
> > > > > And if you make the cell too narrow for the whole string (not
after
> > > > > wrapping
> > > > > text), you'll see ###'s.
> > > > >
> > > > > ====
> > > > > Another format to consider:
> > > > > dddd* dd mmmm yyyy
> > > > > This works pretty neat when you widen the column.
> > > > >
> > > > >
> > > > >
> > > > > Big Rick wrote:
> > > > >>
> > > > >> Hello Fellow Excellers.
> > > > >> When I enter a date in a cell and give it a long format. e.g.
dddd dd
> > > > >> mmmm
> > > > >> yyyy, so that it would read Monday 1 January 2006, then the wrap
text
> > > > >> facility does not work.
> > > > >> Is there any way to put in an 'alt-enter' type character so that
I
> > > > >> could
> > > > >> have the Monday on the first line and the rest underneath.
> > > > >> --
> > > > >> Your help is and always has been very much appreciated.
> > > > >> Thanking you in anticipation.
> > > > >> --- --- ---
> > > > >> Big Rick
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson
Hi Roger,
I look forward to that.
Tanglefoot, Wadworths 6X, topped off with a decent pint of Director's if we
can find it. We also have a local Ringwood micro-brewery which does a nice
ale.
Bob
"Roger Govier" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob
>
> Then before too long, I must travel down to your neck of the woods and
> enjoy a pint of Badger with you.
>
> --
> Regards
>
> Roger Govier
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > Roger,
> >
> > You know that all Brits can drink! The world is astounded at our
> > capacity.
> >
> > Bob
> >
> >
> > "Roger Govier" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi Big Rick
> >>
> >> > If anyone of you are ever in England, then the drinks are on me !
> >> Be careful there,
> >> Bob is in Dorset, I am in Wales (but often across the border)
> >> Don't know about Bob, but I can drink quite a lot of beer!! <vbg>
> >>
> >> --
> >> Regards
> >>
> >> Roger Govier
> >>
> >>
> >> "Big Rick" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Dear Bob, Max, Dave & Roger
> >> > I am forever grateful for all your time, effort and patience for
> >> > someone who
> >> > has to have something drummed in before it sticks in my mind.
> >> > I did get Rogers code to work, I couldn't with Bobs', although I
> >> > sure
> >> > that
> >> > it is because a complete amateur is trying.
> >> > If anyone of you are ever in England, then the drinks are on me !
> >> >
> >> > Regards
> >> > --
> >> > Big Rick
> >> >
> >> >
> >> > "Dave Peterson" wrote:
> >> >
> >> >> You can use format|cells|number tab
> >> >>
> >> >> Give it a custom format of:
> >> >> dddd(alt-0010)dd mmmm yyyy
> >> >>
> >> >> Hit and hold the alt key while typing 0010 on the numeric
> >> >> keypad--not
> >> >> above the
> >> >> QWERTY keys.
> >> >>
> >> >> And turn on wrap text
> >> >> format|cells|alignment tab
> >> >>
> >> >> But be aware that you'll have to adjust the rowheight yourself
> >> >> (autofitting
> >> >> won't work).
> >> >>
> >> >> And if you make the cell too narrow for the whole string (not
> >> >> after
> >> >> wrapping
> >> >> text), you'll see ###'s.
> >> >>
> >> >> ====
> >> >> Another format to consider:
> >> >> dddd* dd mmmm yyyy
> >> >> This works pretty neat when you widen the column.
> >> >>
> >> >>
> >> >>
> >> >> Big Rick wrote:
> >> >> >
> >> >> > Hello Fellow Excellers.
> >> >> > When I enter a date in a cell and give it a long format. e.g.
> >> >> > dddd
> >> >> > dd mmmm
> >> >> > yyyy, so that it would read Monday 1 January 2006, then the wrap
> >> >> > text
> >> >> > facility does not work.
> >> >> > Is there any way to put in an 'alt-enter' type character so that
> >> >> > I
> >> >> > could
> >> >> > have the Monday on the first line and the rest underneath.
> >> >> > --
> >> >> > Your help is and always has been very much appreciated.
> >> >> > Thanking you in anticipation.
> >> >> > --- --- ---
> >> >> > Big Rick
> >> >>
> >> >> --
> >> >>
> >> >> Dave Peterson
> >> >>
> >>
> >>
> >
> >
>
>
Hmmm....
I can see the absolute merit of Dave's keeping the date in a format for
use in further calculations (how are we going to do all those SP
formulae otherwise Bob<vbg>).
I can also see the OP's desire for having a nice formatting.
Me, Id' just have a good old compromise and do both. Have a hidden
column B and write the date value to that cell from my code,
unformatted, and then use that column if I needed to do any
calculations.
--
Regards
Roger Govier
"Bob Phillips" <[email protected]> wrote in message
news:%[email protected]...
> In that case, why would you want the wrap-around?
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
>> I'm not sure I'd say nullify. I (still) think that it depends on the
>> circumstance. If this were a header for a column full of wide
> descriptions,
>> then I wouldn't care at all.
>>
>>
>> Bob Phillips wrote:
>> >
>> > It is good that it keeps the original value, but the fact that you
>> > have
> to
>> > allow cell width to cater for the full text string nullifies the
> usefulness
>> > of this technique IMO.
>> >
>> > Bob
>> >
>> > "Dave Peterson" <[email protected]> wrote in message
>> > news:[email protected]...
>> > > One of the nicer things about using format is that the value
>> > > stays a
> date,
>> > too.
>> > > So it makes doing date arithmetic a little easier if you need it
> later.
>> > >
>> > >
>> > >
>> > > Roger Govier wrote:
>> > > >
>> > > > Hey, that's quite sneaky Dave.
>> > > > I would never have thought about entering control characters
>> > > > into
> the
>> > > > number format.
>> > > > Very effective.
>> > > >
>> > > > --
>> > > > Regards
>> > > >
>> > > > Roger Govier
>> > > >
>> > > > "Dave Peterson" <[email protected]> wrote in message
>> > > > news:[email protected]...
>> > > > > You can use format|cells|number tab
>> > > > >
>> > > > > Give it a custom format of:
>> > > > > dddd(alt-0010)dd mmmm yyyy
>> > > > >
>> > > > > Hit and hold the alt key while typing 0010 on the numeric
> keypad--not
>> > > > > above the
>> > > > > QWERTY keys.
>> > > > >
>> > > > > And turn on wrap text
>> > > > > format|cells|alignment tab
>> > > > >
>> > > > > But be aware that you'll have to adjust the rowheight
>> > > > > yourself
>> > > > > (autofitting
>> > > > > won't work).
>> > > > >
>> > > > > And if you make the cell too narrow for the whole string (not
> after
>> > > > > wrapping
>> > > > > text), you'll see ###'s.
>> > > > >
>> > > > > ====
>> > > > > Another format to consider:
>> > > > > dddd* dd mmmm yyyy
>> > > > > This works pretty neat when you widen the column.
>> > > > >
>> > > > >
>> > > > >
>> > > > > Big Rick wrote:
>> > > > >>
>> > > > >> Hello Fellow Excellers.
>> > > > >> When I enter a date in a cell and give it a long format.
>> > > > >> e.g.
> dddd dd
>> > > > >> mmmm
>> > > > >> yyyy, so that it would read Monday 1 January 2006, then the
>> > > > >> wrap
> text
>> > > > >> facility does not work.
>> > > > >> Is there any way to put in an 'alt-enter' type character so
>> > > > >> that
> I
>> > > > >> could
>> > > > >> have the Monday on the first line and the rest underneath.
>> > > > >> --
>> > > > >> Your help is and always has been very much appreciated.
>> > > > >> Thanking you in anticipation.
>> > > > >> --- --- ---
>> > > > >> Big Rick
>> > > > >
>> > > > > --
>> > > > >
>> > > > > Dave Peterson
>> > >
>> > > --
>> > >
>> > > Dave Peterson
>>
>> --
>>
>> Dave Peterson
>
>
I agree with the merit of saving the original data, but my point was that if
it doesn't allow the column width to be reduced, what is the point of having
it wrap to multiple lines, why not just use a simple form of 'dddd dd mmmm
yyyy' which still preserves the data. That is why I never use that
technique, I see absolutely no merit in it. A typical example of a good idea
badly implemented in Excel IMO (... don't get me started on charts and pivot
tables <vbg>).
Regards
Bob
(remove nothere from email address if mailing direct)
"Roger Govier" <[email protected]> wrote in message
news:e2B%[email protected]...
> Hmmm....
>
> I can see the absolute merit of Dave's keeping the date in a format for
> use in further calculations (how are we going to do all those SP
> formulae otherwise Bob<vbg>).
> I can also see the OP's desire for having a nice formatting.
>
> Me, Id' just have a good old compromise and do both. Have a hidden
> column B and write the date value to that cell from my code,
> unformatted, and then use that column if I needed to do any
> calculations.
>
> --
> Regards
>
> Roger Govier
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:%[email protected]...
> > In that case, why would you want the wrap-around?
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Dave Peterson" <[email protected]> wrote in message
> > news:[email protected]...
> >> I'm not sure I'd say nullify. I (still) think that it depends on the
> >> circumstance. If this were a header for a column full of wide
> > descriptions,
> >> then I wouldn't care at all.
> >>
> >>
> >> Bob Phillips wrote:
> >> >
> >> > It is good that it keeps the original value, but the fact that you
> >> > have
> > to
> >> > allow cell width to cater for the full text string nullifies the
> > usefulness
> >> > of this technique IMO.
> >> >
> >> > Bob
> >> >
> >> > "Dave Peterson" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> > > One of the nicer things about using format is that the value
> >> > > stays a
> > date,
> >> > too.
> >> > > So it makes doing date arithmetic a little easier if you need it
> > later.
> >> > >
> >> > >
> >> > >
> >> > > Roger Govier wrote:
> >> > > >
> >> > > > Hey, that's quite sneaky Dave.
> >> > > > I would never have thought about entering control characters
> >> > > > into
> > the
> >> > > > number format.
> >> > > > Very effective.
> >> > > >
> >> > > > --
> >> > > > Regards
> >> > > >
> >> > > > Roger Govier
> >> > > >
> >> > > > "Dave Peterson" <[email protected]> wrote in message
> >> > > > news:[email protected]...
> >> > > > > You can use format|cells|number tab
> >> > > > >
> >> > > > > Give it a custom format of:
> >> > > > > dddd(alt-0010)dd mmmm yyyy
> >> > > > >
> >> > > > > Hit and hold the alt key while typing 0010 on the numeric
> > keypad--not
> >> > > > > above the
> >> > > > > QWERTY keys.
> >> > > > >
> >> > > > > And turn on wrap text
> >> > > > > format|cells|alignment tab
> >> > > > >
> >> > > > > But be aware that you'll have to adjust the rowheight
> >> > > > > yourself
> >> > > > > (autofitting
> >> > > > > won't work).
> >> > > > >
> >> > > > > And if you make the cell too narrow for the whole string (not
> > after
> >> > > > > wrapping
> >> > > > > text), you'll see ###'s.
> >> > > > >
> >> > > > > ====
> >> > > > > Another format to consider:
> >> > > > > dddd* dd mmmm yyyy
> >> > > > > This works pretty neat when you widen the column.
> >> > > > >
> >> > > > >
> >> > > > >
> >> > > > > Big Rick wrote:
> >> > > > >>
> >> > > > >> Hello Fellow Excellers.
> >> > > > >> When I enter a date in a cell and give it a long format.
> >> > > > >> e.g.
> > dddd dd
> >> > > > >> mmmm
> >> > > > >> yyyy, so that it would read Monday 1 January 2006, then the
> >> > > > >> wrap
> > text
> >> > > > >> facility does not work.
> >> > > > >> Is there any way to put in an 'alt-enter' type character so
> >> > > > >> that
> > I
> >> > > > >> could
> >> > > > >> have the Monday on the first line and the rest underneath.
> >> > > > >> --
> >> > > > >> Your help is and always has been very much appreciated.
> >> > > > >> Thanking you in anticipation.
> >> > > > >> --- --- ---
> >> > > > >> Big Rick
> >> > > > >
> >> > > > > --
> >> > > > >
> >> > > > > Dave Peterson
> >> > >
> >> > > --
> >> > >
> >> > > Dave Peterson
> >>
> >> --
> >>
> >> Dave Peterson
> >
> >
>
>
No other reason than "just because".
Actually, I like this format much better:
dddd* mm/dd/yyyy
But that's just my opinion.
Bob Phillips wrote:
>
> In that case, why would you want the wrap-around?
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > I'm not sure I'd say nullify. I (still) think that it depends on the
> > circumstance. If this were a header for a column full of wide
> descriptions,
> > then I wouldn't care at all.
> >
> >
> > Bob Phillips wrote:
> > >
> > > It is good that it keeps the original value, but the fact that you have
> to
> > > allow cell width to cater for the full text string nullifies the
> usefulness
> > > of this technique IMO.
> > >
> > > Bob
> > >
> > > "Dave Peterson" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > One of the nicer things about using format is that the value stays a
> date,
> > > too.
> > > > So it makes doing date arithmetic a little easier if you need it
> later.
> > > >
> > > >
> > > >
> > > > Roger Govier wrote:
> > > > >
> > > > > Hey, that's quite sneaky Dave.
> > > > > I would never have thought about entering control characters into
> the
> > > > > number format.
> > > > > Very effective.
> > > > >
> > > > > --
> > > > > Regards
> > > > >
> > > > > Roger Govier
> > > > >
> > > > > "Dave Peterson" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > You can use format|cells|number tab
> > > > > >
> > > > > > Give it a custom format of:
> > > > > > dddd(alt-0010)dd mmmm yyyy
> > > > > >
> > > > > > Hit and hold the alt key while typing 0010 on the numeric
> keypad--not
> > > > > > above the
> > > > > > QWERTY keys.
> > > > > >
> > > > > > And turn on wrap text
> > > > > > format|cells|alignment tab
> > > > > >
> > > > > > But be aware that you'll have to adjust the rowheight yourself
> > > > > > (autofitting
> > > > > > won't work).
> > > > > >
> > > > > > And if you make the cell too narrow for the whole string (not
> after
> > > > > > wrapping
> > > > > > text), you'll see ###'s.
> > > > > >
> > > > > > ====
> > > > > > Another format to consider:
> > > > > > dddd* dd mmmm yyyy
> > > > > > This works pretty neat when you widen the column.
> > > > > >
> > > > > >
> > > > > >
> > > > > > Big Rick wrote:
> > > > > >>
> > > > > >> Hello Fellow Excellers.
> > > > > >> When I enter a date in a cell and give it a long format. e.g.
> dddd dd
> > > > > >> mmmm
> > > > > >> yyyy, so that it would read Monday 1 January 2006, then the wrap
> text
> > > > > >> facility does not work.
> > > > > >> Is there any way to put in an 'alt-enter' type character so that
> I
> > > > > >> could
> > > > > >> have the Monday on the first line and the rest underneath.
> > > > > >> --
> > > > > >> Your help is and always has been very much appreciated.
> > > > > >> Thanking you in anticipation.
> > > > > >> --- --- ---
> > > > > >> Big Rick
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> >
> > --
> >
> > Dave Peterson
--
Dave Peterson
"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> Actually, I like this format much better:
> dddd* mm/dd/yyyy
So do I.
I did finally get Bob' code to work by pre formatting the cells. As you can
see from the previous post, I was trying to input the date into cell H1.
Thanks for the suggestions as to why it didnt work. Many thanks to Bob also.
Many Regards
--
Big Rick
"Max" wrote:
> > .. I did get Rogers code to work, I couldn't with Bobs',
>
> Big Rick (as posted in the other branch),
>
> With Bob's sub implemented properly,
>
> Select and *pre-format* the designated input range: H1:H10
> as Custom: dddd dd mmmm yyyy
>
> Then test by inputting some dates within H1:H10
>
> When you press ENTER to commit each input,
> Bob's code will return exactly what you're after
>
> (My guess is you either didn't pre-format H1:H10 beforehand, or you might
> have tried out the inputs elsewhere .. in A1, A2?? [I dunno]. But don't ask
> me why Bob prefers "H1:H10" instead of "A1:A10" as the assumed source input
> range <g>)
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
>
>
>
Glad to hear you got it to work!
And thanks for posting back
> As you can see from the previous post,
> I was trying to input the date into cell H1 ...
Yes, you did. Overlooked this earlier, apologies
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Big Rick" <[email protected]> wrote in message
news:[email protected]...
> I did finally get Bob' code to work by pre formatting the cells. As you
can
> see from the previous post, I was trying to input the date into cell H1.
> Thanks for the suggestions as to why it didnt work. Many thanks to Bob
also.
>
> Many Regards
> --
> Big Rick
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks