Making column same format

[Don W. Reynolds]

Hi All,

I'm receiving an excel spreadsheet which has a 15 digit number in one of the
columns. Some of the rows contain a number; some of the rows contain a text
field (embedded spaces within the 15 digit number). When I go to import
this into Access, it does not deal well with two kinds of data in the same
row.

I would prefer that all of the data be converted to text. Is there some way
to convert an entire column so the internal representation is text?

Thanks,

Don Reynolds

 

[Jim Rech]

You might select the range and then run this macro:

Sub ToText()
Dim Cell As Range
Selection.NumberFormat = "@"
For Each Cell In Selection
Cell.Value = CStr(Cell.Value)
Next
End Sub


--
Jim Rech
Excel MVP
| Hi All,
|
| I'm receiving an excel spreadsheet which has a 15 digit number in one of
the
| columns. Some of the rows contain a number; some of the rows contain a
text
| field (embedded spaces within the 15 digit number). When I go to import
| this into Access, it does not deal well with two kinds of data in the same
| row.
|
| I would prefer that all of the data be converted to text. Is there some
way
| to convert an entire column so the internal representation is text?
|
| Thanks,
|
| Don Reynolds
|
|
_______________________________________________________________________________
| Posted Via Uncensored-News.Com - Accounts Starting At $6.95 -
http://www.uncensored-news.com
| <><><><><><><> The Worlds Uncensored News Source
<><><><><><><><>
|

 

[Jamie Collins]

...

I'm receiving an excel spreadsheet which has a 15 digit number in one of the
columns. Some of the rows contain a number; some of the rows contain a text
field (embedded spaces within the 15 digit number). When I go to import
this into Access, it does not deal well with two kinds of data in the same
row.

I would prefer that all of the data be converted to text. Is there some way
to convert an entire column so the internal representation is text?

See the following:

http://www.dicks-blog.com/excel/2004/06/external_data_m.html#trackback

It's written with ADO in mind but the issues (and the registry
settings!) are the same.

Jamie.

--