Changing data types

[Rick]

I am trying to use ODBC to query an Excel spreadsheet. The ODBC driver
looks at the first 8 rows in each column to determine the datatype. I
have mixed numbers and text, but the driver thinks the column is
numeric. As a result, text entries are not returned.

My solution is to change the format of the cells to Text. This works,
but I have to reenter each numeric value in order for Excel to see
them as Text. Is there a quick shortcut for reentering text in
selected cells? Thank you very much.

Rick Quatro

 

[Pete_UK]

Once you have imported the data, check that the format in these cells
is not set to Text - if it is, highlight the column and change the
format to General (it will not take effect yet). Then select an empty
cell beyond your data (so the format is also set to General) and click
<copy>. Then highlight the column with mixed numbers and text and Edit
| Paste Special | Values (check) | Add (check - in the Operation part)
| OK then <Esc>. This should convert your "numbers" back to numbers.

You might also try Data | Text-to-columns, after first changing the
format to General.

Hope this helps.

Pete

 

[Rick]

Hi Pete,

Actually, I want to convert everything to text. I will give it a try.
Thanks.

Rick

 

[Pete_UK]

Sorry, I'd got it the wrong way round. You could insert a new column
next to the one you want to convert and enter this formula:

=""&A1

and copy this down by double-clicking the fill handle. Then with the
data already highlighted, click <copy> then Edit | Paste Special |
Values (check) | OK then <Enter>. Then you can delete your original
column.

Hope this helps.

Pete