formatting a text column

[Don W. Reynolds]

Hi All,

I'm receiving a spreadsheet which has a column in it with a 15 digit
number. I import this spreadsheet into an Access database. Sometimes
the 15 digit number is a number (internally), sometimes it has
embedded spaces within it and is text (internally).

When importing to Access, it assumes that the format for the column is
consistant. When internal formats change, Access can't import that
particular cell.

I've tried selecting a range and using format cells, but that does not
change the internal format, just the visible display. (But it does
create an unwanted exponential display).

Is there a way to select a row, change the internal formats to text so
that all 15 digits are displayed as if 1. the cells had been specified
as text, and 2. a 15 digit number (with or without spaces) was entered
into the cells?

Thanks,

Don

 

[Dave Peterson]

You could use a helper column and do something like:

=b3&""

It won't hurt the text cells, but it'll convert the number cells to text.
If you want the cells formatted, you could use:
=text(b3,"0.00")
(apply the formatting that you like.)

Or you could run a macro.
Select a cell in that column and run this:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range

With ActiveSheet
Set myRng = Intersect(ActiveCell.EntireColumn, .UsedRange)
For Each myCell In myRng.Cells
With myCell
.NumberFormat = "@"
.Value = .Text
End With
Next myCell
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Jamie Collins]

Hi All,

I'm receiving a spreadsheet which has a column in it with a 15 digit
number. I import this spreadsheet into an Access database. Sometimes
the 15 digit number is a number (internally), sometimes it has
embedded spaces within it and is text (internally).

When importing to Access, it assumes that the format for the column is
consistant. When internal formats change, Access can't import that
particular cell.

I've tried selecting a range and using format cells, but that does not
change the internal format, just the visible display. (But it does
create an unwanted exponential display).

Is there a way to select a row, change the internal formats to text so
that all 15 digits are displayed as if 1. the cells had been specified
as text, and 2. a 15 digit number (with or without spaces) was entered
into the cells?

Thanks,

Don

See

http://groups.google.com/[email protected]

Jamie.

--