Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text format - setting text colour with code | Excel Discussion (Misc queries) | |||
Max value in 1 column when all values format are text | Excel Programming | |||
Setting a column to Text datatype | Excel Programming | |||
Column format as Text | Excel Programming | |||
column format as text | Excel Programming |