Making a number have Text format

  • Thread starter Thread starter Howard Kaikow
  • Start date Start date
H

Howard Kaikow

I am inserting stuff into some cells as follows:

shtExcel.Range(Cells(lngRow, 1), Cells(lngRow, 5)) = Split(strBuffer, vbTab)

Then, I change the cells in columns 2-5 to numeric formattrying to change
some of the cells to numeric format. using code like

With shtExcel.Range(Cells(lngFormat, 2),Cells(lngFormat + lngHigh, 3))
.Value = .Value
End With

Then assigning the relevant format for the cells in eaxh column.

However, the cells in column 1 are supposed to retain Text format, but some
cells have all numbers, e.g., 000, and are intended to stay as Text.

If I set Application.ErrorCheckingOptions.NumberAsText = false, I achieve my
goal while the workbook is open.
However, if I save the workbook, exit Excel, then re-open the workbook, the
NumberAsText propety reverts to whatever the user has chosen.

How can I programmatically change numbers to Text format?
If I save the value in a string variable, then assign to the Value property,
the cell is treated as a number, e.g., 000 becomes 0.

Is there a way to do the deed without changing a user's NumberAsText
property?
 
I did not understand your problem correctly but whenever and where eve
you need to conver a value to text format you may use *CStr* function
Example is as follows:
Range("A2")=Cstr(Range("A1"
 
"gajendra_vba" <[email protected]>
wrote in message
I did not understand your problem correctly but whenever and where ever
you need to conver a value to text format you may use *CStr* function.
Example is as follows:
Range("A2")=Cstr(Range("A1")
Click to expand...

I tried

For Each rngCell In .Range(Cells(lngFormat, 1),
Cells(lngFormat + lngHigh, 1))
With rngCell
.Value = CStr(.Value)
End With
Next rngCell

Did not change the format.
 
I cannot use NumberAsText anyway, because that property is not in Excel 97
or Excel 2000.
Property is only in Excel 2002 and 2003.

I would prefer to support all 4 versions, tho I'd be willing to ditch
support for Excel 97, if there was no alternative.
 
Is there a single command that will convert a multicell range with numerical
entries from values stored as numbers to values stored as text? I don't
believe there is.

You can always loop and prepend a single quote.
 
Tom Ogilvy said:
Is there a single command that will convert a multicell range with numerical
entries from values stored as numbers to values stored as text? I don't
believe there is.

You can always loop and prepend a single quote.
Click to expand...

Thanx, I fergot about that, I'll give it a try.
 
I tried

.Range(Cells(lngFormat, 1), Cells(lngFormat + lngHigh,
1)).NumberFormat = "@"
For i = 0 To lngHigh
With Cells(lngFormat + i, 1)
If IsNumeric(.Value) Then
strTemp = CStr(.Value)
.Value = "'" & strTemp
End If
End With
Next i

Excel still insists on marking the cells as errors.

The only real issue is how to programmatically tell Excel to ignore the
"error" for those cells.
Is there a way to do that?

The NumberAsText property can be overridden by the user's options, not to
mention the property is not supported by Excel 97 and 2000.
 
Hi Howard,

Don't see the original post :-(

Do you mean something like:

Sub b()
Dim s As String

s = Format$(1.2345, "$0.000")
Debug.Print s
End Sub

or

Sub c()
Dim s As String

s = Format$(1234568, "00-00-00-00")
Debug.Print s
End Sub

Perhaps I'm missing something. As I say I can't see your original post.

Regards,

Peter Beach
 
Peter Beach said:
Hi Howard,

Don't see the original post :-(

Do you mean something like:

Sub b()
Dim s As String

s = Format$(1.2345, "$0.000")
Debug.Print s
End Sub

or

Sub c()
Dim s As String

s = Format$(1234568, "00-00-00-00")
Debug.Print s
End Sub

Perhaps I'm missing something. As I say I can't see your original post.
Click to expand...


The following is the original post.
---------------------------
I am inserting stuff into some cells as follows:

shtExcel.Range(Cells(lngRow, 1), Cells(lngRow, 5)) = Split(strBuffer, vbTab)

Then, I change the cells in columns 2-5 to numeric formattrying to change
some of the cells to numeric format. using code like

With shtExcel.Range(Cells(lngFormat, 2),Cells(lngFormat + lngHigh, 3))
.Value = .Value
End With

Then assigning the relevant format for the cells in eaxh column.

However, the cells in column 1 are supposed to retain Text format, but some
cells have all numbers, e.g., 000, and are intended to stay as Text.

If I set Application.ErrorCheckingOptions.NumberAsText = false, I achieve my
goal while the workbook is open.
However, if I save the workbook, exit Excel, then re-open the workbook, the
NumberAsText propety reverts to whatever the user has chosen.

How can I programmatically change numbers to Text format?
If I save the value in a string variable, then assign to the Value property,
the cell is treated as a number, e.g., 000 becomes 0.

Is there a way to do the deed without changing a user's NumberAsText
property?

------------------------------
And I followed this with the following post:
------------------------------------------
I tried

.Range(Cells(lngFormat, 1), Cells(lngFormat + lngHigh,
1)).NumberFormat = "@"
For i = 0 To lngHigh
With Cells(lngFormat + i, 1)
If IsNumeric(.Value) Then
strTemp = CStr(.Value)
.Value = "'" & strTemp
End If
End With
Next i

Excel still insists on marking the cells as errors.

The only real issue is how to programmatically tell Excel to ignore the
"error" for those cells.
Is there a way to do that?

The NumberAsText property can be overridden by the user's options, not to
mention the property is not supported by Excel 97 and 2000.
 
Hi Howard,
The only real issue is how to programmatically tell Excel to ignore the
"error" for those cells.
Is there a way to do that?
Click to expand...

That would be the Errors collection for the range:

rng.Errors(XlErrorChecks.xlNumberAsText).Ignore = True

Regards

Stephen Bullen
Microsoft MVP - Excel

Professional Excel Development
The most advanced Excel VBA book available
www.oaltd.co.uk/ProExcelDev
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Changing Number Format 3
Number stored as text 4
Number displayed as text difficulties 4
Importing Text 2
Make Macro Work On A Different PC 5
Update this macro to make it generate a counter as text 7
Problem using Match on columns of numbers with leading zeroesformatted as text ??? 4
Excel Convert text to Number 9

Back
Top