Setting Column format as text

[Guest]

I am converting a spreadsheet from one format to another. I am having
trouble with setting the format of one of the column to 'text'. I tried
worksheet.Column.NumberFormat = "Text" with no luck. The value of
NumberFormat in the Watch window still shows up as "Null" and the column
still did not format correctly - numeric value still shows up as numbers...

Another question is if I do set the format correctly to text, would all the
data values being entered be converted to text automatically? Even if the
data is a numeric value?

Appreciate any help that can be given.

Deluth

 

[Norman Jones]

Hi Deluth,

Try:

ActiveSheet.Columns("A:A").NumberFormat = "@"

Another question is if I do set the format correctly to text, would all
the
data values being entered be converted to text automatically? Even if the
data is a numeric value?

Yes.

 

[Guest]

Hi Norman,

Thank you for the quick response. Unfortunately, this method did not work
for me. I set the format as you suggested: ...NumberFormat = "@" in the
beginning of the subroutine. In the middle, I set the cell value as so:
destCell.Value = srcCell.Value
Some of srcCell.Value are text, but most are numbers, but I wanted the
entire column to be represented as text. The resulting value for the cells
with numbers were in numbers. For example, a value of "2200505000099" became
"2.20051E+12"

Should I have done a string conversion? If so, how do I know when the cell
value starts with a text and when it starts with a number? Do I need to test
the cell value for numeric and then convert? There must be an easy way to do
this...

Any help would greatly be appreciated!

 

[Norman Jones]

Hi Deluth,

For example, a value of "2200505000099" became "2.20051E+12"

This appears only to happen with 12+ digit numbers.

For single cell ranges, the following worked for me:

Sub Test1()
Dim destCell As Range, srcCell As Range

Set destCell = Range("A1")
Set srcCell = Range("C1")

srcCell.Value = "2200505000099" '13 digit Test value

With destCell
.NumberFormat = "@"
.Value = CStr(srcCell.Value)
End With
End Sub

For multi-cell ranges, the following worked for me:

Sub Test2()
Dim destRng As Range
Dim srcRng As Range
Dim rcell As Range

Set srcRng = Range("C1:C10")
Set destRng = Range("A1:A10")

destRng.NumberFormat = "@"
destRng.Value = srcRng.Value
For Each rcell In destRng
rcell.Value = CStr(rcell.Value)
Next
End Sub

 

[Norman Jones]

Hi Deluth,

And if the ranges were multi-area ranges, perhaps something like:

Sub Test3()
Dim destRng As Range
Dim srcRng As Range
Dim rcell As Range
Dim i As Long

Set srcRng = Range("C1:C3,C5:C7,C10:C11")
Set destRng = Range("A1:A3,B5:B7,A10:A11")

destRng.NumberFormat = "@"

For i = 1 To srcRng.Areas.Count
destRng.Areas(i).Value = srcRng.Areas(i).Value
Next i

For Each rcell In destRng
rcell.Value = CStr(rcell.Value)
Next
End Sub

 

[Dave Peterson]

I don't think you'd need this at the end:

For Each rcell In destRng
rcell.Value = CStr(rcell.Value)
Next

 

[Norman Jones]

Hi Dave,

I don't think you'd need this at the end:

For Each rcell In destRng
rcell.Value = CStr(rcell.Value)
Next

If the srcRng included 12+ digit numbers, failure to include this
For...Next clause results in such numbers appearing in the destRng in
scientific notation representation.

This,at least, was my experience testing under xl2k; I have not, as yet,
tested with other versions

 

[Dave Peterson]

Ah, I should have read the whole thread <vbg>.

But depending on the format of the source range, this could work, too:

For i = 1 To srcRng.Areas.Count
destRng.Areas(i).Value = srcRng.Areas(i).Text
Next i

Hi Dave,

I don't think you'd need this at the end:

For Each rcell In destRng
rcell.Value = CStr(rcell.Value)
Next

If the srcRng included 12+ digit numbers, failure to include this
For...Next clause results in such numbers appearing in the destRng in
scientific notation representation.

This,at least, was my experience testing under xl2k; I have not, as yet,
tested with other versions

 

[Guest]

Dave and Norman,

Awesome!! Both methods worked!! You rock!!

Thanks so much!
Deluth