Setting Column format as text
 

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

Setting Column format as text
 

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.

---
Regards,
Norman



"deluth" wrote in message
...
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

Need to set Column format as text
 

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" wrote:

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.

---
Regards,
Norman



"deluth" wrote in message
...
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

Need to set Column format as text
 

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

---
Regards,
Norman


"deluth" wrote in message
...
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!

Need to set Column format as text
 

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

---
Regards,
Norman



"Norman Jones" wrote in message
...
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

---
Regards,
Norman

Need to set Column format as text
 

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

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



Norman Jones wrote:

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

---
Regards,
Norman

"Norman Jones" wrote in message
...
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

---
Regards,
Norman

--

Dave Peterson

Need to set Column format as text
 

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

---
Regards,
Norman



"Dave Peterson" wrote in message
...
I don't think you'd need this at the end:

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



Norman Jones wrote:

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

---
Regards,
Norman

"Norman Jones" wrote in message
...
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

---
Regards,
Norman

--

Dave Peterson

Need to set Column format as text
 

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



Norman Jones wrote:

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

---
Regards,
Norman

"Dave Peterson" wrote in message
...
I don't think you'd need this at the end:

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



Norman Jones wrote:

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

---
Regards,
Norman

"Norman Jones" wrote in message
...
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

---
Regards,
Norman

--

Dave Peterson

--

Dave Peterson

Need to set Column format as text
 

Dave and Norman,

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

Thanks so much!
Deluth

"Dave Peterson" wrote:

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



Norman Jones wrote:

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

---
Regards,
Norman

"Dave Peterson" wrote in message
...
I don't think you'd need this at the end:

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



Norman Jones wrote:

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

---
Regards,
Norman

"Norman Jones" wrote in message
...
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

---
Regards,
Norman

--

Dave Peterson

--

Dave Peterson