Thread: How to keep data as text from external file

We asre using MS Excel 2003 and the SAS Add-in for Excel. When we attempt to pull data created by SAS into Excel, columns that are supposed to be text are coming in as numeric. I know there's something in Excel that if it sees something like "000123456" in a column, it'll think that is numeric, although it should be text.

Is there something I can do in Excel to keep a character column as character data, even if the data looks like a number?

Thnaks in advance!

Anybody?? Please ?

Hello Greaseman,

Select the entire column you want to display only text, and Right Click. Select Format Cells... from the pop up menu. Click the Number tab. Under the headingCategory, click Text then clik OK.

Sincerely,
Leith Ross

Leith,

Thanks for replying..... however, when the text data comes over and Excel thinks it is numeric data, leading zeros are suppressed, which we want to keep. So, even highlighting the column and formatting manually as text won't help us. The leading zeros are already gone at this point. We want to keep text records such as "000123456" in Excel as "000123456".

Is there some setting in Excel 2003 that will tell Excel to keep text as text in these cases?

Thanks again!

If they should all be the same length, say 9 digits, and the numbers coming across are all 6 digits, or if you need to add the same # of 0s to every number regardless of length, you can have a helper column that is =000&A1 and drag down. If the number of digits that is coming across varies, but the end numbers should all have the same digits, =Rept(0,9-Len(A1))&A1. Then you can copy and paste values.

Thank you all for your replies and suggestions.... I'll keep plugging away untile I get it worked out, and will most likely try to incorporate your suggestions into a macro to apply against the workbook sheet.

Thanks again! I appreciate it!