Format appears stuck..how to break formatting
 

Hi:

I have an old Excel File of clients that I am converting to Access and
I have a problem.

The phone number field is, quite naturally, formatted in Excel as a
number in phone number format. However after being moved into Access,
while it retains that format for some functions, it either looses it
for others or turns up formatted like a weird zip code.

I have talked to Access people and done enough research to be certain
the problem is in the initial Excel spreadsheet. What I need to do is
remove ALL formatting from the field, convert it to text (this is what
Access uses for phone numbers) and revise the import process for
access.

My question is: HOW DO I REMOVE ALL FORMATTING ON AN EXCEL FIELD SO
THAT IT APPEARS AND ACTS AS IF IT WAS A VIRGIN UNSULLIED UNFORMATTED
FIELD. I have tried all the usual tricks of using the format function
to remove the phone number setting, and have set it to text. However
when I move it into access it still is considered numeric and has this
residual formatting like a zip code,

This spreadsheet has been used for years, and no doubt someone in the
past ,messed around with the formatting, but I am now faced with the
potential of having to REKEY all phone number -- a task I do not
relish!

Any help much welcomed.

John Baker

Format appears stuck..how to break formatting
 

On Jun 24, 8:13*am, JHB wrote:
Hi:

I have an old Excel File of clients that I am converting to Access and
I have a problem.

The phone number field is, quite naturally, formatted in Excel as a
number in phone number format. However after being moved into Access,
while it retains that format for some functions, it either looses it
for others or turns up formatted like a weird zip code.

I have talked to Access people and done enough research to be certain
the problem is in the initial Excel spreadsheet. What I need to do is
remove ALL formatting from the field, convert it to text (this is what
Access uses for phone numbers) and revise the import process for
access.

My question is: HOW DO I REMOVE ALL FORMATTING ON AN EXCEL FIELD SO
THAT *IT *APPEARS AND ACTS AS IF IT WAS A VIRGIN UNSULLIED UNFORMATTED
FIELD. I have tried all the usual tricks of using the format function
to remove the phone number setting, and have set it to text. However
when I move it into access it still is considered numeric and has this
residual formatting like a zip code,

This spreadsheet has been used for years, and no doubt someone in the
past ,messed around with the formatting, but I am now faced with the
potential of having to REKEY all phone number -- a task I do not
relish!

Any help much welcomed.

John Baker

OOPS Sorry..forgot to say I am working with Office 2000!

Format appears stuck..how to break formatting
 

JHB wrote :
On Jun 24, 8:13*am, JHB wrote:
Hi:

I have an old Excel File of clients that I am converting to Access and
I have a problem.

The phone number field is, quite naturally, formatted in Excel as a
number in phone number format. However after being moved into Access,
while it retains that format for some functions, it either looses it
for others or turns up formatted like a weird zip code.

I have talked to Access people and done enough research to be certain
the problem is in the initial Excel spreadsheet. What I need to do is
remove ALL formatting from the field, convert it to text (this is what
Access uses for phone numbers) and revise the import process for
access.

My question is: HOW DO I REMOVE ALL FORMATTING ON AN EXCEL FIELD SO
THAT *IT *APPEARS AND ACTS AS IF IT WAS A VIRGIN UNSULLIED UNFORMATTED
FIELD. I have tried all the usual tricks of using the format function
to remove the phone number setting, and have set it to text. However
when I move it into access it still is considered numeric and has this
residual formatting like a zip code,

This spreadsheet has been used for years, and no doubt someone in the
past ,messed around with the formatting, but I am now faced with the
potential of having to REKEY all phone number -- a task I do not
relish!

Any help much welcomed.

John Baker

OOPS Sorry..forgot to say I am working with Office 2000!

If you apply 'Custom' format (ie: "(000) 000-0000") it will result as
text and so would satisfy Access' need for it to be 'text'!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion