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.

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.

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.

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

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

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

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

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.