How do you do this, and why does excel fight it so hard?

Our payroll provider sends us our payroll data in an excel spreadsheet.
Project numbers that have been charged to come back in the spreadsheet with
an apostrophe (') leading the number, forcing it to be recognized as text.
Up to a point. If I edit a project number that was entered wrong, even
though I keep the apostrophe before the number, excel now treats the cell
value differently. In the body of the spreadsheet it now looks like a
number, with no apostrophe, while the edit box at the top of the spreadsheet
still shows the apostrophe in front of the number. When I try to import the
spreadsheet into MS Access using VBA and Transferspreadsheet, the leading
apostrophe is gone and subsequent processing, expecting the apostrophe,
fails! Even formatting the excel column as 'text' fails to keep the
apostrophe when I transfer the data! Any ideas how to keep excel from
trashing my data, how to get it to give me what I want, and not what MS
programmers think I want instead?

Thanks

Fred

are you trying to keep the apostrophe or delete it? are you trying to charge
payroll to the project #. do you want the project # as text or as a number?
several to address this depending on what you want.

"Fredrated" wrote:

How do you do this, and why does excel fight it so hard?

Our payroll provider sends us our payroll data in an excel spreadsheet.
Project numbers that have been charged to come back in the spreadsheet with
an apostrophe (') leading the number, forcing it to be recognized as text.
Up to a point. If I edit a project number that was entered wrong, even
though I keep the apostrophe before the number, excel now treats the cell
value differently. In the body of the spreadsheet it now looks like a
number, with no apostrophe, while the edit box at the top of the spreadsheet
still shows the apostrophe in front of the number. When I try to import the
spreadsheet into MS Access using VBA and Transferspreadsheet, the leading
apostrophe is gone and subsequent processing, expecting the apostrophe,
fails! Even formatting the excel column as 'text' fails to keep the
apostrophe when I transfer the data! Any ideas how to keep excel from
trashing my data, how to get it to give me what I want, and not what MS
programmers think I want instead?

Thanks

Fred