Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default "Text to Columns" for many columns in Excel 2003

Hi,

What is an efficient way to apply the "Text to Columns" feature to many
many (say, 200) columns in Excel 2003? A macro? If so, can I see some
sample code for that?

TIA.

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 301
Default "Text to Columns" for many columns in Excel 2003

Text to Columns is something applied to ROWS, not Columns. It distributes
data in several rows (or one) across columns, so something like
1,2,3,4,5,6 would get distributed to columns A:F if the comma were the
delimiter, for example.
Your question doesn't really apply here, unless I'm misunderstanding the
question.

"NickName" wrote in message
oups.com...
Hi,

What is an efficient way to apply the "Text to Columns" feature to many
many (say, 200) columns in Excel 2003? A macro? If so, can I see some
sample code for that?

TIA.



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default "Text to Columns" for many columns in Excel 2003


Bob Umlas wrote:
Text to Columns is something applied to ROWS, not Columns. It distributes
data in several rows (or one) across columns, so something like
1,2,3,4,5,6 would get distributed to columns A:F if the comma were the
delimiter, for example.
Your question doesn't really apply here, unless I'm misunderstanding the
question.


Then what's an efficient way to apply the "Text to Columns" feature to
many
many (say, 200) rows in Excel 2003? btw, the naming of this feature is
misleading, it would be more accurate if named something like "Convert
to Text" regardless of Column or Row but forget about this.

Also, copying a cell's format and past special (for the format to a
range of cells) would not work, weird.


"NickName" wrote in message
oups.com...
Hi,

What is an efficient way to apply the "Text to Columns" feature to many
many (say, 200) columns in Excel 2003? A macro? If so, can I see some
sample code for that?

TIA.


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default "Text to Columns" for many columns in Excel 2003

Select your range (but it has to be a single column) of 200 cells.

Then click on Data|Text to columns.

And follow the wizard.

NickName wrote:

Bob Umlas wrote:
Text to Columns is something applied to ROWS, not Columns. It distributes
data in several rows (or one) across columns, so something like
1,2,3,4,5,6 would get distributed to columns A:F if the comma were the
delimiter, for example.
Your question doesn't really apply here, unless I'm misunderstanding the
question.


Then what's an efficient way to apply the "Text to Columns" feature to
many
many (say, 200) rows in Excel 2003? btw, the naming of this feature is
misleading, it would be more accurate if named something like "Convert
to Text" regardless of Column or Row but forget about this.

Also, copying a cell's format and past special (for the format to a
range of cells) would not work, weird.


"NickName" wrote in message
oups.com...
Hi,

What is an efficient way to apply the "Text to Columns" feature to many
many (say, 200) columns in Excel 2003? A macro? If so, can I see some
sample code for that?

TIA.


--

Dave Peterson
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default "Text to Columns" for many columns in Excel 2003


Dave Peterson wrote:
Select your range (but it has to be a single column) of 200 cells.

Then click on Data|Text to columns.

And follow the wizard.

No. You did not answer the question. The data sets to be formatted as
"Text" overlaps many rows and many columns. No problem of doing it by
hand (manually), however, that would be silly if you have, say, 300
columns and 500 rows to do, formatting the whole range using datatype
of "Text" does not work neither, very weird.


NickName wrote:

Bob Umlas wrote:
Text to Columns is something applied to ROWS, not Columns. It distributes
data in several rows (or one) across columns, so something like
1,2,3,4,5,6 would get distributed to columns A:F if the comma were the
delimiter, for example.
Your question doesn't really apply here, unless I'm misunderstanding the
question.


Then what's an efficient way to apply the "Text to Columns" feature to
many
many (say, 200) rows in Excel 2003? btw, the naming of this feature is
misleading, it would be more accurate if named something like "Convert
to Text" regardless of Column or Row but forget about this.

Also, copying a cell's format and past special (for the format to a
range of cells) would not work, weird.


"NickName" wrote in message
oups.com...
Hi,

What is an efficient way to apply the "Text to Columns" feature to many
many (say, 200) columns in Excel 2003? A macro? If so, can I see some
sample code for that?

TIA.


--

Dave Peterson




  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default "Text to Columns" for many columns in Excel 2003

If you have 300 columns of data, you're not using xl2003 or below.

But data|text to columns only works on a single column at a time.

Maybe you could have a macro that did what you needed. So you could select the
range to fix and then invoke that macro.



NickName wrote:

Dave Peterson wrote:
Select your range (but it has to be a single column) of 200 cells.

Then click on Data|Text to columns.

And follow the wizard.

No. You did not answer the question. The data sets to be formatted as
"Text" overlaps many rows and many columns. No problem of doing it by
hand (manually), however, that would be silly if you have, say, 300
columns and 500 rows to do, formatting the whole range using datatype
of "Text" does not work neither, very weird.

NickName wrote:

Bob Umlas wrote:
Text to Columns is something applied to ROWS, not Columns. It distributes
data in several rows (or one) across columns, so something like
1,2,3,4,5,6 would get distributed to columns A:F if the comma were the
delimiter, for example.
Your question doesn't really apply here, unless I'm misunderstanding the
question.

Then what's an efficient way to apply the "Text to Columns" feature to
many
many (say, 200) rows in Excel 2003? btw, the naming of this feature is
misleading, it would be more accurate if named something like "Convert
to Text" regardless of Column or Row but forget about this.

Also, copying a cell's format and past special (for the format to a
range of cells) would not work, weird.


"NickName" wrote in message
oups.com...
Hi,

What is an efficient way to apply the "Text to Columns" feature to many
many (say, 200) columns in Excel 2003? A macro? If so, can I see some
sample code for that?

TIA.


--

Dave Peterson


--

Dave Peterson
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default "Text to Columns" for many columns in Excel 2003


Dave Peterson wrote:
If you have 300 columns of data, you're not using xl2003 or below.

But data|text to columns only works on a single column at a time.

Yes, I know that.


Maybe you could have a macro that did what you needed. So you could select the
range to fix and then invoke that macro.

Please refer to my OP. I've never used Excel macro before, not an
extensive Excel user. Thks.




NickName wrote:

Dave Peterson wrote:
Select your range (but it has to be a single column) of 200 cells.

Then click on Data|Text to columns.

And follow the wizard.

No. You did not answer the question. The data sets to be formatted as
"Text" overlaps many rows and many columns. No problem of doing it by
hand (manually), however, that would be silly if you have, say, 300
columns and 500 rows to do, formatting the whole range using datatype
of "Text" does not work neither, very weird.

NickName wrote:

Bob Umlas wrote:
Text to Columns is something applied to ROWS, not Columns. It distributes
data in several rows (or one) across columns, so something like
1,2,3,4,5,6 would get distributed to columns A:F if the comma were the
delimiter, for example.
Your question doesn't really apply here, unless I'm misunderstanding the
question.

Then what's an efficient way to apply the "Text to Columns" feature to
many
many (say, 200) rows in Excel 2003? btw, the naming of this feature is
misleading, it would be more accurate if named something like "Convert
to Text" regardless of Column or Row but forget about this.

Also, copying a cell's format and past special (for the format to a
range of cells) would not work, weird.


"NickName" wrote in message
oups.com...
Hi,

What is an efficient way to apply the "Text to Columns" feature to many
many (say, 200) columns in Excel 2003? A macro? If so, can I see some
sample code for that?

TIA.


--

Dave Peterson


--

Dave Peterson


  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default "Text to Columns" for many columns in Excel 2003

What version of excel are you using?

If it's xl2003 or below, then if you have 200 columns of data to parse, some of
those columns can't be done!

You will only end up with 256 columns no matter what you do.

If you want to see some code, then I'd suggest you record a macro when you do
one column the way you want.

NickName wrote:

Dave Peterson wrote:
If you have 300 columns of data, you're not using xl2003 or below.

But data|text to columns only works on a single column at a time.

Yes, I know that.


Maybe you could have a macro that did what you needed. So you could select the
range to fix and then invoke that macro.

Please refer to my OP. I've never used Excel macro before, not an
extensive Excel user. Thks.




NickName wrote:

Dave Peterson wrote:
Select your range (but it has to be a single column) of 200 cells.

Then click on Data|Text to columns.

And follow the wizard.

No. You did not answer the question. The data sets to be formatted as
"Text" overlaps many rows and many columns. No problem of doing it by
hand (manually), however, that would be silly if you have, say, 300
columns and 500 rows to do, formatting the whole range using datatype
of "Text" does not work neither, very weird.

NickName wrote:

Bob Umlas wrote:
Text to Columns is something applied to ROWS, not Columns. It distributes
data in several rows (or one) across columns, so something like
1,2,3,4,5,6 would get distributed to columns A:F if the comma were the
delimiter, for example.
Your question doesn't really apply here, unless I'm misunderstanding the
question.

Then what's an efficient way to apply the "Text to Columns" feature to
many
many (say, 200) rows in Excel 2003? btw, the naming of this feature is
misleading, it would be more accurate if named something like "Convert
to Text" regardless of Column or Row but forget about this.

Also, copying a cell's format and past special (for the format to a
range of cells) would not work, weird.


"NickName" wrote in message
oups.com...
Hi,

What is an efficient way to apply the "Text to Columns" feature to many
many (say, 200) columns in Excel 2003? A macro? If so, can I see some
sample code for that?

TIA.


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson
  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default "Text to Columns" for many columns in Excel 2003


If you want to see some code, then I'd suggest you record a macro when you do
one column the way you want.

Man, you're almost as absent-minded as me sometimes, the title says
Excel 2003.

OK, about the macro, I have a question, the recorded macro looks like
this
Sub Convert2Text()
'
' Convert2Text Macro
'
Selection.TextToColumns Destination:=Range("AH1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 2), TrailingMinusNumbers:=True
End Sub

Now, what I wanted to do is, to expand the Range, so, change the above
to something like

Sub Convert2Text()
'
' Convert2Text Macro
'
Selection.TextToColumns Destination:=Range("AI1..FA255"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 2), TrailingMinusNumbers:=True
End Sub

But VB does not like it, could I use a range like that? Or syntax
error? Tks.



NickName wrote:

Dave Peterson wrote:
If you have 300 columns of data, you're not using xl2003 or below.

But data|text to columns only works on a single column at a time.

Yes, I know that.


Maybe you could have a macro that did what you needed. So you could select the
range to fix and then invoke that macro.

Please refer to my OP. I've never used Excel macro before, not an
extensive Excel user. Thks.




NickName wrote:

Dave Peterson wrote:
Select your range (but it has to be a single column) of 200 cells.

Then click on Data|Text to columns.

And follow the wizard.

No. You did not answer the question. The data sets to be formatted as
"Text" overlaps many rows and many columns. No problem of doing it by
hand (manually), however, that would be silly if you have, say, 300
columns and 500 rows to do, formatting the whole range using datatype
of "Text" does not work neither, very weird.

NickName wrote:

Bob Umlas wrote:
Text to Columns is something applied to ROWS, not Columns. It distributes
data in several rows (or one) across columns, so something like
1,2,3,4,5,6 would get distributed to columns A:F if the comma were the
delimiter, for example.
Your question doesn't really apply here, unless I'm misunderstanding the
question.

Then what's an efficient way to apply the "Text to Columns" feature to
many
many (say, 200) rows in Excel 2003? btw, the naming of this feature is
misleading, it would be more accurate if named something like "Convert
to Text" regardless of Column or Row but forget about this.

Also, copying a cell's format and past special (for the format to a
range of cells) would not work, weird.


"NickName" wrote in message
oups.com...
Hi,

What is an efficient way to apply the "Text to Columns" feature to many
many (say, 200) columns in Excel 2003? A macro? If so, can I see some
sample code for that?

TIA.


--

Dave Peterson

--

Dave Peterson


--

Dave Peterson


  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default "Text to Columns" for many columns in Excel 2003

I don't usually read the subjects. I figure that anyone would give the
particulars in the body of the message. But I guess I'm wrong.

The next problem you're going to have when you try to parse each column is that
as soon as you parse on column, I'm betting that the adjacent columns (to the
right) get overwritten by the Data|Text to columns.

How are you going to approach this?

Do you know the maximum number of fields that will be created? Does your data
have that many columns available in those adjacent columns or will you have to
insert columns. Or do you skip way over to AH1--I can't tell where your
selection is when the macro starts.

Option Explicit
Sub Convert2Text2()

Dim iCol As Long
With ActiveSheet
'assumes that you'll skip 6 columns between data|text to columns
For iCol = .Range("ah1").Column To .Range("Fa1").Column Step 6
With .Range(.Cells(1, iCol), .Cells(255, iCol))
If Application.CountA(.Cells) 0 Then
.TextToColumns Destination:=.Cells(1), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
FieldInfo:=Array(1, 2), TrailingMinusNumbers:=True
End If
End With
Next iCol
End With
End Sub

I expected no more than 6 fields from any column to be overwritten. If your
data isn't set up this way, then this won't work.

NickName wrote:

If you want to see some code, then I'd suggest you record a macro when you do
one column the way you want.

Man, you're almost as absent-minded as me sometimes, the title says
Excel 2003.

OK, about the macro, I have a question, the recorded macro looks like
this
Sub Convert2Text()
'
' Convert2Text Macro
'
Selection.TextToColumns Destination:=Range("AH1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 2), TrailingMinusNumbers:=True
End Sub

Now, what I wanted to do is, to expand the Range, so, change the above
to something like

Sub Convert2Text()
'
' Convert2Text Macro
'
Selection.TextToColumns Destination:=Range("AI1..FA255"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 2), TrailingMinusNumbers:=True
End Sub

But VB does not like it, could I use a range like that? Or syntax
error? Tks.


NickName wrote:

Dave Peterson wrote:
If you have 300 columns of data, you're not using xl2003 or below.

But data|text to columns only works on a single column at a time.
Yes, I know that.


Maybe you could have a macro that did what you needed. So you could select the
range to fix and then invoke that macro.
Please refer to my OP. I've never used Excel macro before, not an
extensive Excel user. Thks.




NickName wrote:

Dave Peterson wrote:
Select your range (but it has to be a single column) of 200 cells.

Then click on Data|Text to columns.

And follow the wizard.

No. You did not answer the question. The data sets to be formatted as
"Text" overlaps many rows and many columns. No problem of doing it by
hand (manually), however, that would be silly if you have, say, 300
columns and 500 rows to do, formatting the whole range using datatype
of "Text" does not work neither, very weird.

NickName wrote:

Bob Umlas wrote:
Text to Columns is something applied to ROWS, not Columns. It distributes
data in several rows (or one) across columns, so something like
1,2,3,4,5,6 would get distributed to columns A:F if the comma were the
delimiter, for example.
Your question doesn't really apply here, unless I'm misunderstanding the
question.

Then what's an efficient way to apply the "Text to Columns" feature to
many
many (say, 200) rows in Excel 2003? btw, the naming of this feature is
misleading, it would be more accurate if named something like "Convert
to Text" regardless of Column or Row but forget about this.

Also, copying a cell's format and past special (for the format to a
range of cells) would not work, weird.


"NickName" wrote in message
oups.com...
Hi,

What is an efficient way to apply the "Text to Columns" feature to many
many (say, 200) columns in Excel 2003? A macro? If so, can I see some
sample code for that?

TIA.


--

Dave Peterson

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default "Text to Columns" for many columns in Excel 2003


Dave Peterson wrote:

Option Explicit
Sub Convert2Text2()

Dim iCol As Long
With ActiveSheet
'assumes that you'll skip 6 columns between data|text to columns
For iCol = .Range("ah1").Column To .Range("Fa1").Column Step 6
With .Range(.Cells(1, iCol), .Cells(255, iCol))
If Application.CountA(.Cells) 0 Then
.TextToColumns Destination:=.Cells(1), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
FieldInfo:=Array(1, 2), TrailingMinusNumbers:=True
End If
End With
Next iCol
End With
End Sub

Nice! Thank you, it looks like it has successfully converted all these
columns to Text datatype (Preview show data for these columns), I
changed one thing, I used Step 1 instead of Step 6 for I don't need to
skip any column.

btw, minor separate problem, during data import to sql server 2000, DTS
complained about one particular column, "Data source column X is too
large for the specified buffer size, which I'll check with sql server
ng to see where/how to extend buffer size.

  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 17
Default "Text to Columns" for many columns in Excel 2003

On Wed, 6 Sep 2006 10:54:47 -0400, "Bob Umlas"
wrote:

Text to Columns is something applied to ROWS, not Columns.


Wrong. It IS applied to columns. You highlight a single column containing
text data you want to alter.

Say you have a column of Firstname, Lastname combinations, and you want to
break out the last name to the next column over to the right. This is a
perfect scenario for Text to Columns and it takes only as long as it takes
you to highlight the column (or cells) that you want to run the routine on.


Tom

--
remove .spoo to reply by email
  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default "Text to Columns" for many columns in Excel 2003


Tom Hall wrote:
On Wed, 6 Sep 2006 10:54:47 -0400, "Bob Umlas"
wrote:

Text to Columns is something applied to ROWS, not Columns.


Wrong. It IS applied to columns. You highlight a single column containing
text data you want to alter.

Say you have a column of Firstname, Lastname combinations, and you want to
break out the last name to the next column over to the right. This is a
perfect scenario for Text to Columns and it takes only as long as it takes
you to highlight the column (or cells) that you want to run the routine on.


Tom


OK. There's some new information. I realized that converting a
worksheet into CSV then import it into a SQL Server db would work great
as well, something anyone else could also use in addition to the
technique that Dave Peterson has written.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Excel 2000 VBA Application on Excel 2003 Excel Worksheet Functions 0 August 8th 06 02:36 AM
Columns in excel 2003 Jackie D Excel Worksheet Functions 6 November 10th 05 07:32 PM
How do I open a Quattro Pro 7.0 file in Excel 2003 cpatrick83 Excel Discussion (Misc queries) 0 August 22nd 05 09:41 PM
quattro pro converter Excel 2003 dr88363 New Users to Excel 0 February 27th 05 06:00 PM
sharing/using/saving Excel 2002 files in Excel 2003 maze2009 Excel Discussion (Misc queries) 0 January 20th 05 08:27 PM


All times are GMT +1. The time now is 01:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"