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

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" <[email protected]> wrote in message
news:[email protected]...
>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

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" <[email protected]> wrote in message
> news:[email protected]...
> >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
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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!
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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

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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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

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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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

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" <[email protected]> wrote in message
> > news:[email protected]...
> > >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" <[email protected]> wrote in message
> > >> news:[email protected]...
> > >> > 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
>