How do I set Excel cell format to "Text" from VB.NET?

[John Brock]

I am creating an Excel workbook using VB.NET, and have run into a
problem. Excel at times insists on reformatting data that I enter
into cells, e.g., converting "01234" to "1234", and this screws me
up when I need to read the data back. When I run into this problem
using Excel interactively I simply change the cell Number format
from "General" to "Text", but I haven't been able to figure out
how to do this using VB.NET. Here is a code sample:

Dim wb as Microsoft.Office.Interop.Excel.Workbook

[...workbook is created...]

Dim style as Microsoft.Office.Interop.Excel.Style

style = wb.Styles.Add("Style1")
style.Font.Name = "Arial"
style.Font.Bold = True
style.Font.Size = 12
style.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid
style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlighLeft

I use more than one style, and once I've created a style with the
features I want I apply it to the ranges where I am entering data.
Styles also have a "NumberFormat" property, and I would think that
this somehow could be used to set cell format to "Text". But if,
for example, I try

style.NumberFormat = "Text"

all that happens is that I end up with a weird custom format.
Nothing else I try seems to work either. Can anyone tell me how
to do what I am trying to do?

 

[Jim Thomlinson]

The text number format is the at symbol...

..NumberFormat = "@"

 

[Randall Arnold]

Here are examples from my own code:

wsStdDev = objXL.Worksheets.Add
With wsStdDev
.Cells(seriesName.GetUpperBound(0) + 7, 3).numberformat = "#.00"
.Cells(seriesName.GetUpperBound(0) + 7, 5).numberformat = "0"
End With

Don't worry about my cell identifiers; what's important here is
numberformat. In the first case, it truncates all fractional parts of the
numbers to 2 places with optional whole numbers to the left. If you want to
more tightly control the whole numbers, you would use combinations of 0 and
#, depending on if you want leading zeros or not. In the second case, all
numbers (regardless of number of digits) lose their fractional portions.
Apply this to any type of range object.

Look up info on numberformat in Excel's VBA help for more detail.

Randall Arnold

 

[George Nicholson]

Try:
style.NumberFormat = "@"

HTH,

 

[Gareth]

A simple way to force Excel to accept anything as text is to prepend an
apostrophe to the text.

e.g. to write an integer 123 as 00000123 just write:
ActiveCell = "'" & Format(123, "00000000")

Note that this cell is now definitively text - this precludes the use of
formulae such as SUM on these cells, or further number formatting. If
this is an issue consider setting the numberformat as described in other
posts.

HTH,
Gareth

 

[Randall Arnold]

My goof, I should have limited my response to styles. My example is
generally relevant but not specific to your question. Sorry.

Randall Arnold

Here are examples from my own code:

wsStdDev = objXL.Worksheets.Add
With wsStdDev
.Cells(seriesName.GetUpperBound(0) + 7, 3).numberformat = "#.00"
.Cells(seriesName.GetUpperBound(0) + 7, 5).numberformat = "0"
End With

Don't worry about my cell identifiers; what's important here is
numberformat. In the first case, it truncates all fractional parts of the
numbers to 2 places with optional whole numbers to the left. If you want to
more tightly control the whole numbers, you would use combinations of 0 and
#, depending on if you want leading zeros or not. In the second case, all
numbers (regardless of number of digits) lose their fractional portions.
Apply this to any type of range object.

Look up info on numberformat in Excel's VBA help for more detail.

Randall Arnold

I am creating an Excel workbook using VB.NET, and have run into a
problem. Excel at times insists on reformatting data that I enter
into cells, e.g., converting "01234" to "1234", and this screws me
up when I need to read the data back. When I run into this problem
using Excel interactively I simply change the cell Number format
from "General" to "Text", but I haven't been able to figure out
how to do this using VB.NET. Here is a code sample:

Dim wb as Microsoft.Office.Interop.Excel.Workbook

[...workbook is created...]

Dim style as Microsoft.Office.Interop.Excel.Style

style = wb.Styles.Add("Style1")
style.Font.Name = "Arial"
style.Font.Bold = True
style.Font.Size = 12
style.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid
style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlighLeft

I use more than one style, and once I've created a style with the
features I want I apply it to the ranges where I am entering data.
Styles also have a "NumberFormat" property, and I would think that
this somehow could be used to set cell format to "Text". But if,
for example, I try

style.NumberFormat = "Text"

all that happens is that I end up with a weird custom format.
Nothing else I try seems to work either. Can anyone tell me how
to do what I am trying to do?

 

[John Brock]

Thank you, that does the trick.

Try:
style.NumberFormat = "@"

HTH,
--
George Nicholson

Remove 'Junk' from return address.

I am creating an Excel workbook using VB.NET, and have run into a
problem. Excel at times insists on reformatting data that I enter
into cells, e.g., converting "01234" to "1234", and this screws me
up when I need to read the data back. When I run into this problem
using Excel interactively I simply change the cell Number format
from "General" to "Text", but I haven't been able to figure out
how to do this using VB.NET. Here is a code sample:

Dim wb as Microsoft.Office.Interop.Excel.Workbook

[...workbook is created...]

Dim style as Microsoft.Office.Interop.Excel.Style

style = wb.Styles.Add("Style1")
style.Font.Name = "Arial"
style.Font.Bold = True
style.Font.Size = 12
style.Interior.Pattern =
Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid
style.HorizontalAlignment =
Microsoft.Office.Interop.Excel.XlHAlign.xlHAlighLeft

I use more than one style, and once I've created a style with the
features I want I apply it to the ranges where I am entering data.
Styles also have a "NumberFormat" property, and I would think that
this somehow could be used to set cell format to "Text". But if,
for example, I try

style.NumberFormat = "Text"

all that happens is that I end up with a weird custom format.
Nothing else I try seems to work either. Can anyone tell me how
to do what I am trying to do?

 

[Randall Arnold]

Ah, the most simple and effective solution! I forgot all about it. ; )

Randall Arnold

A simple way to force Excel to accept anything as text is to prepend an
apostrophe to the text.

e.g. to write an integer 123 as 00000123 just write:
ActiveCell = "'" & Format(123, "00000000")

Note that this cell is now definitively text - this precludes the use of
formulae such as SUM on these cells, or further number formatting. If
this is an issue consider setting the numberformat as described in other
posts.

HTH,
Gareth

I am creating an Excel workbook using VB.NET, and have run into a
problem. Excel at times insists on reformatting data that I enter
into cells, e.g., converting "01234" to "1234", and this screws me
up when I need to read the data back. When I run into this problem
using Excel interactively I simply change the cell Number format
from "General" to "Text", but I haven't been able to figure out
how to do this using VB.NET. Here is a code sample:

Dim wb as Microsoft.Office.Interop.Excel.Workbook

[...workbook is created...]

Dim style as Microsoft.Office.Interop.Excel.Style

style = wb.Styles.Add("Style1")
style.Font.Name = "Arial"
style.Font.Bold = True
style.Font.Size = 12
style.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid
style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlighLeft

I use more than one style, and once I've created a style with the
features I want I apply it to the ranges where I am entering data.
Styles also have a "NumberFormat" property, and I would think that
this somehow could be used to set cell format to "Text". But if,
for example, I try

style.NumberFormat = "Text"

all that happens is that I end up with a weird custom format.
Nothing else I try seems to work either. Can anyone tell me how
to do what I am trying to do?

 

[scorpion53061]

I tried that trick with my boss and it flew for a little while....till
he changed his mind and wanted the data type changed back. I was a
little red faced.

Ah, the most simple and effective solution! I forgot all about it. ; )

Randall Arnold

A simple way to force Excel to accept anything as text is to prepend an
apostrophe to the text.

e.g. to write an integer 123 as 00000123 just write:
ActiveCell = "'" & Format(123, "00000000")

Note that this cell is now definitively text - this precludes the use of
formulae such as SUM on these cells, or further number formatting. If
this is an issue consider setting the numberformat as described in other
posts.

HTH,
Gareth

I am creating an Excel workbook using VB.NET, and have run into a
problem. Excel at times insists on reformatting data that I enter
into cells, e.g., converting "01234" to "1234", and this screws me
up when I need to read the data back. When I run into this problem
using Excel interactively I simply change the cell Number format
from "General" to "Text", but I haven't been able to figure out
how to do this using VB.NET. Here is a code sample:

Dim wb as Microsoft.Office.Interop.Excel.Workbook

[...workbook is created...]

Dim style as Microsoft.Office.Interop.Excel.Style

style = wb.Styles.Add("Style1")
style.Font.Name = "Arial"
style.Font.Bold = True
style.Font.Size = 12
style.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid
style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlighLeft

I use more than one style, and once I've created a style with the
features I want I apply it to the ranges where I am entering data.
Styles also have a "NumberFormat" property, and I would think that
this somehow could be used to set cell format to "Text". But if,
for example, I try

style.NumberFormat = "Text"

all that happens is that I end up with a weird custom format.
Nothing else I try seems to work either. Can anyone tell me how
to do what I am trying to do?