Number Stored As Text Converted To Number When Save To CSV

[goss]

Hi all

Using Excel 2010

I have a field of Invoices in Excel
I have all items as Text as some invoices are a mix of alpha and numeric
When I am done, I would like to export the data to csv and import to Access

When I copy the data from the Excel file to the csv file, the format changes
Now I have:

• 
  400509 <<-- Number
  5.51538E+11 <<-- Number
  JM20110301 <<--Text

When I tried to import to Access, many of the invoice numbers error'd out due to this mismatch

How do I make sure all invoice numbers are text and not numbers in the csv fil before I import to Access?

 

[CWatts]

When entering them, you can put an apostrophe (') in front of them, or a blank space by holding down ALT and typing 255 on the numeric keypad for a blank space.

 

[goss]

Thanks

All of the invoices appear as text in Excel
I did this by adding a helper column to Col N and dropping in formula =Upper(H1) where H1 is the invoice number

This coerced all numbers to text and had the added value of ensuring all alpha Char's were upper case

Inspecting these results I see a green triangle in the upper left of each of the resultant cells

When I hover with the mouse I receive a yellow diamond with an exclamation. When I hover on the icon I see this message: " The number in this cell is formatted as text or is preceded by an apostrophe."

Looking at the formula bar, I see no apostrophe.

I added an apostrophe to the left most position in the cell
I now see the apostrophe in the formula bar

I ran the code to save to a csv file
Again, all items that are numbers stored as text in Excel are converted to numbers

How can I coerce so that all are saved as text literals instead of numbers

I cannot change the original invoice number
I need to compare to another table output from RPG Database that treats the invoice number as text

 

[CWatts]

Maybe this then?

http://www.mrexcel.com/forum/showthread.php?t=57535

 

[goss]

Thanks

I tried the solutions posed by Andrew Poulsom and Mark W
When I opened the sample proposed by Andrew I do see the leading zero (I used Notepad++)

I them imported this file into Access
Access immediately interpreted as a number

So it does not appear that those will work

Then I tried

C.Offset(0,6).Value = CStr(C.Value)

Looks OK in Excel except items noted in col H for Number stored as text.
If they are very long numbers (Len = 12) Now appear in Col N as Text, but as 6.279612E+11

Also, though now appears naturally left aligned and no green triangle, a quick =ISTEXT(N2) in a helper column returns FALSE

So, still not where I need to be