Updating formatting without taking each cell to edit mode
 

Hello all!

I have some worksheets that I get from a database and upon exporting them
some items that should be text (Stock numbers with leading zeros) are saved
as Numbers and the leading zeros are removed. Wel... I can easily get the
leading zeros back by formatting to custom"000000000". The problem I am
having however is getting the cells in that column to Update to the new
format. I find that I must dbl-Click in each cell and enter "edit mode" for
each cell to get it to update. Is there any one out there taht knows of the
code t o automate the update as I have thousands of cells to update in this
column each week.


--
Thanks,

Patrick

Updating formatting without taking each cell to edit mode
 

what is the length to the field in the database you are exporting from ?

"PSKelligan" wrote:

Hello all!

I have some worksheets that I get from a database and upon exporting them
some items that should be text (Stock numbers with leading zeros) are saved
as Numbers and the leading zeros are removed. Wel... I can easily get the
leading zeros back by formatting to custom"000000000". The problem I am
having however is getting the cells in that column to Update to the new
format. I find that I must dbl-Click in each cell and enter "edit mode" for
each cell to get it to update. Is there any one out there taht knows of the
code t o automate the update as I have thousands of cells to update in this
column each week.


--
Thanks,

Patrick

Updating formatting without taking each cell to edit mode
 

Before exporting from the database create a query that changes the field by
adding a prefix to it. Say the field name es StockNo then in the query create
a field like:
StNo:="S"&[Stockno]. This will assue you that the data will be exported as
text

"PSKelligan" wrote:

Hello all!

I have some worksheets that I get from a database and upon exporting them
some items that should be text (Stock numbers with leading zeros) are saved
as Numbers and the leading zeros are removed. Wel... I can easily get the
leading zeros back by formatting to custom"000000000". The problem I am
having however is getting the cells in that column to Update to the new
format. I find that I must dbl-Click in each cell and enter "edit mode" for
each cell to get it to update. Is there any one out there taht knows of the
code t o automate the update as I have thousands of cells to update in this
column each week.


--
Thanks,

Patrick

Updating formatting without taking each cell to edit mode
 

I must make the changes after export. I am unable to access the database via
dynamic query. There are pre-defined views that I can use and no flexibility
as this dbate is no my product.

I export through a web interface and I want to clean up the data in excel
befor importing it into an Access database. the length of the field is 9
characters. All numeric and all with 1 or 2 leading zeros.

Any idea on how to update the formatting of the cells?

thanks,

Patrick

Updating formatting without taking each cell to edit mode
 

I am assuming the stock number is in column A and the data begins on line 2
In a new column use the formula
=text(if(len(A1<8,"00"&A1,if(len(A1<9,"0"&A1,A1)))

"PSKelligan" wrote:

I must make the changes after export. I am unable to access the database via
dynamic query. There are pre-defined views that I can use and no flexibility
as this dbate is no my product.

I export through a web interface and I want to clean up the data in excel
befor importing it into an Access database. the length of the field is 9
characters. All numeric and all with 1 or 2 leading zeros.

Any idea on how to update the formatting of the cells?

thanks,

Patrick

Updating formatting without taking each cell to edit mode
 

I typed the formula wrong. It should be as follows
=(IF(LEN(A1)<8,"00"&A1,IF(LEN(A1)<9,"0"&A1,A1))
"PSKelligan" wrote:

I must make the changes after export. I am unable to access the database via
dynamic query. There are pre-defined views that I can use and no flexibility
as this dbate is no my product.

I export through a web interface and I want to clean up the data in excel
befor importing it into an Access database. the length of the field is 9
characters. All numeric and all with 1 or 2 leading zeros.

Any idea on how to update the formatting of the cells?

thanks,

Patrick

Updating formatting without taking each cell to edit mode
 

Thanks for the response,

This seems to work but is not very efficient when working with several
exported sheets and upwards of 10000 records. I am actually looking for a
"VBA" proceedure that will, with a tool bar button click, update formatting
to the entire used range on a sheet.

Thanks,
Patrick

"LWendel" wrote:

I typed the formula wrong. It should be as follows
=(IF(LEN(A1)<8,"00"&A1,IF(LEN(A1)<9,"0"&A1,A1))

Updating formatting without taking each cell to edit mode
 

I came up with a solution that seems to do the trick. Code follows.