MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Formatting - 2 Questions !


Posted by Pete Wilshaw on April 25, 2001 3:24 AM

Hi,
Just have a couple of questions, firstly, I have a column which has a numbers entered into each cell which look like - 18042001. How do I change this 8 figure number into a date, ie. 18/04/2001 ?

Also, I have a row which contains text in each cell. Is there an easy way of converting each cell to purely UPPER CASE or lower case ??

Many Thanks,
Pete.


Posted by Dave Hawley on April 25, 2001 3:53 AM


Hi Pete

For your numbers to dates use:

=DATEVALUE(LEFT(A1,2)&"/" &MID(A1,3,2)&"/"&RIGHT(A1,4))

And for your UPPER CASE use:

=UPPER(A1)

Dave

OzGrid Business Applications

Posted by IML on April 25, 2001 9:10 AM

another way with the date

To convert the date, you can highlight the column and go to Data - text to columns
Hit next, next again, and click date and DMY.

Posted by Dave Hawley on April 25, 2001 2:15 PM

Re: another way with the date

Yes, a much cleaner way!

Dave
OzGrid Business Applications