Cell data format problem. Please help 
Author Message
 Cell data format problem. Please help

Hi,

Suppose Column A in my worksheet contains some character
data e.g. "20021218" which represents a date. How can I
use VBA code to convert the data in the whole column in
the format such as "2002/12/18"?
Thanks.

Terence



Sat, 04 Jun 2005 10:20:04 GMT  
 Cell data format problem. Please help
If you don't mind converting them to actual dates:

Dim rng as Range
Dim cell as Range
set rng = Range(Cells(1,1),Cells(rows.count,1).End(xlup)
for each cell in rng
  cell.Value = DateValue( Mid(cell,5,2) & "/" & _
    right(cell,2) & "/" & Left(cell,4))
  cell.Numberformat = "yyyy/mm/dd"
Next

if you want to keep as string

Dim rng as Range
Dim cell as Range
set rng = Range(Cells(1,1),Cells(rows.count,1).End(xlup)
for each cell in rng
  cell.Value = "'" & Left(cell,4) & "/" & _
    Mid(cell,5,2) & "/" & right(cell,2)
Next

Regards,
Tom Ogilvy


Quote:
> Hi,

> Suppose Column A in my worksheet contains some character
> data e.g. "20021218" which represents a date. How can I
> use VBA code to convert the data in the whole column in
> the format such as "2002/12/18"?
> Thanks.

> Terence



Sat, 04 Jun 2005 11:13:04 GMT  
 Cell data format problem. Please help
Dear Tom,

Thanks a lot!

Terence

Quote:
>-----Original Message-----
>If you don't mind converting them to actual dates:

>Dim rng as Range
>Dim cell as Range
>set rng = Range(Cells(1,1),Cells(rows.count,1).End(xlup)
>for each cell in rng
>  cell.Value = DateValue( Mid(cell,5,2) & "/" & _
>    right(cell,2) & "/" & Left(cell,4))
>  cell.Numberformat = "yyyy/mm/dd"
>Next

>if you want to keep as string

>Dim rng as Range
>Dim cell as Range
>set rng = Range(Cells(1,1),Cells(rows.count,1).End(xlup)
>for each cell in rng
>  cell.Value = "'" & Left(cell,4) & "/" & _
>    Mid(cell,5,2) & "/" & right(cell,2)
>Next

>Regards,
>Tom Ogilvy



>> Hi,

>> Suppose Column A in my worksheet contains some character
>> data e.g. "20021218" which represents a date. How can I
>> use VBA code to convert the data in the whole column in
>> the format such as "2002/12/18"?
>> Thanks.

>> Terence

>.



Sat, 04 Jun 2005 11:37:39 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. cell formatting problem .. help please

2. Please help - Cell Formatting problem

3. HELP $$$ HELP - Export Excel data to other File format Problem - HELP $$$ HELP

4. Problem selecting cells...PLEASE,PLEASE HELP

5. help please, help please, help please, help please

6. Cell formatting help please

7. Cell Formatting help please.

8. Unknown Cell Format - please help

9. Help Please: Merging Cells lose formatting

10. cell formatting crashed excel - HELP PLEASE!


 
Powered by phpBB® Forum Software © phpBB Group