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

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

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

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

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

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

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))

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

Please Login or Register  to view this content.