Forcing Text Format

[F. Michael Miller]

I've got a spreadsheet that's being read as a datasource by an SQL statement
in a vb.net program. I have a field that contains data that can be numeric,
text, or mixed but is to be considered as text.

The problem is that a pure numeric value is being considered as numeric even
when the cell is formatted as text. If I edit the cell and put a single
quote (') at the beginning of the cell, the ' is not visible, the cell
displayes as text and imports correctly.

I need a way to do this in a macro. I've tried using ="'"&A2, but that
results in the ' being visible, and part of the data rather than just
forcing to text.

Any ideas?

 

[Kalle]

.....

when the cell is formatted as text. If I edit the cell and put a single
quote (') at the beginning of the cell, the ' is not visible, the cell
displayes as text and imports correctly.

this is intendet behaviour and the correct way.

Cellentries starting with a single quote are Text - formatted.

 

[Tom Ogilvy]

for each cell in selection
cell.numberformat = "@"
cell.Value = "'" & cell.Text
Next

 

[F. Michael Miller]

That did it, thanks!:

Sub AllCellsInSelectionToText()

Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

Dim CurCell As Object
For Each CurCell In Selection
CurCell.NumberFormat = "@"
CurCell.Value = "'" & CurCell.Text
Next

End Sub

 

[onedaywhen]

Here a suggested SQL solution:

I've found if you use CREATE TABLE DLL specifying a 'text ' datatype
e.g. VARCHAR then the column will be recognised as text even with pure
numeric values. You may have to persist the data (e.g. in memory) and
issue a DROP TABLE before re-creating the worksheet/Range with a
CREATE TABLE statement..

See this recent discussion:

http://groups.google.com/groups?hl=...d0b7.0403040342.690a17e9%40posting.google.com

--