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

Maybe when you edit the numbers, enter them like so:

="'10000"

It's hard to see, but there is an apostrophe right after the first quote.



"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

One other thing. You could then get rid of the = sign and quotes by clicking
copy, then Edit/PasteSpecial/Values.

"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