There is one crucial feature to the 2007 Excel that has been overlooked.
Throughout all versions of Excel there has always been the feature of
converting text to table, however there is no way to do this in reverse.
There is no way to merge two columns of data and to keep all of the data
without one column overwriting the other. If only there were an automatic
way to merge two columns of data and to be able to place a delimited
character in-between, just like the €œConvert Text to Columns Wizard€�, except
in reverse. Currently, the only way to merge two columns of data is to
manually go row-by-row and cut and paste them together. However, for 500,000
rows of data€¦ this is impossible. Or to use a function to merge two columns,
however this requires that the original two columns remain. This is also
unacceptable. If Microsoft really wants to make Excel more functional, how
can this vast improvement be overlooked?


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

With text in A1 and B1, in C1 try =A1&","&B1
Is that what you want?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Stosh" wrote in message
...
There is one crucial feature to the 2007 Excel that has been overlooked.
Throughout all versions of Excel there has always been the feature of
converting text to table, however there is no way to do this in reverse.
There is no way to merge two columns of data and to keep all of the data
without one column overwriting the other. If only there were an automatic
way to merge two columns of data and to be able to place a delimited
character in-between, just like the "Convert Text to Columns Wizard",
except
in reverse. Currently, the only way to merge two columns of data is to
manually go row-by-row and cut and paste them together. However, for
500,000
rows of data. this is impossible. Or to use a function to merge two
columns,
however this requires that the original two columns remain. This is also
unacceptable. If Microsoft really wants to make Excel more functional,
how
can this vast improvement be overlooked?


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

No, I want to select two columns, click a "Merge Columns" button, maybe have
it ask me if I want a delimited character or space between the two sets of
data, and have the two columns of data become one column. A1 + B1 = new A1
with data from both previous columns. If I use that function you provided,
that requires the original two referenced columns to remain.

"Bernard Liengme" wrote:

With text in A1 and B1, in C1 try =A1&","&B1
Is that what you want?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

After the formula has been copied to all cells, do a Copy, then Paste
Special, choose Values, and then the original two columns can be deleted.

"Stosh" wrote:

No, I want to select two columns, click a "Merge Columns" button, maybe have
it ask me if I want a delimited character or space between the two sets of
data, and have the two columns of data become one column. A1 + B1 = new A1
with data from both previous columns. If I use that function you provided,
that requires the original two referenced columns to remain.

"Bernard Liengme" wrote:

With text in A1 and B1, in C1 try =A1&","&B1
Is that what you want?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

But why should it be that difficult and that much work just to join two
columns? That is still a lot of work for over 500,000 rows. I want it done
in just 2-3 button presses, just like the "Text to Column Wizard", without
having to copy a formula. I would still have to drag that formula to all
500,000 rows. That alone would take several minutes.

I know that there is a long and laborious method of doing it now, however
what I am suggesting is that a simple merge function be added to the new 2007
Excel. Remember, Im dealing with well over 500,000 rows of data.

"KePaHa" wrote:

After the formula has been copied to all cells, do a Copy, then Paste
Special, choose Values, and then the original two columns can be deleted.

"Stosh" wrote:

No, I want to select two columns, click a "Merge Columns" button, maybe have
it ask me if I want a delimited character or space between the two sets of
data, and have the two columns of data become one column. A1 + B1 = new A1
with data from both previous columns. If I use that function you provided,
that requires the original two referenced columns to remain.

"Bernard Liengme" wrote:

With text in A1 and B1, in C1 try =A1&","&B1
Is that what you want?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

Stosh wrote...
There is one crucial feature to the 2007 Excel that has been overlooked.
Throughout all versions of Excel there has always been the feature of
converting text to table, however there is no way to do this in reverse.
....

Presumably you mean Data Text to Columns when you write converting
text to table. It was added in Excel 97.

As for the reverse, use formulas. If you wanted to merge cols A and B,
in another range write the formulas =A1&whatever_delimiter&B1, fill
down (easier when the formulas would be in col C adjacent to cols A and
B), paste special as values into col A, clear the formulas, and merge
cols A and B (or just clear col B).

If you really have to have this, it's not that complicated a macro.
[All lines begin with at least 2 spaces. Any that don't have wrapped
from the end of the previous line and should be reconnected.]

'-- begin VBA ------
Sub foo()
Const TTL As String = "Merge Table"
Static s As String
Dim i As Long, j As Long, k As Long, t As String

If Not TypeOf Selection Is Range Then
MsgBox Prompt:="No range selected.", Title:=TTL,
Buttons:=vbOKOnly
Exit Sub
End If

s = InputBox(Prompt:="Enter delimiter.", Title:=TTL, Default:=s)

For i = 1 To Selection.Areas.Count
For j = 1 To Selection.Areas(i).Rows.Count
t = ""
For k = 1 To Selection.Areas(i).Columns.Count
t = t & s & Selection.Areas(i).Cells(j, k).Text
Selection.Areas(i).Cells(j, k).ClearContents
Next k
Selection.Areas(i).Cells(j, 1).Value = Mid(t, Len(s) + 1)
Next j
Next i

End Sub
'-- end VBA ------

If even 0.01% of Excel users each were allowed to add just one
must-have feature, Excel would become completely unwieldy.

"Harlan Grove" wrote:

If even 0.01% of Excel users each were allowed to add just one
must-have feature, Excel would become completely unwieldy.

I know there are macros, I know there are scripts, I know there are
formulas, I can even go out and buy an add-on pack that would add the
features I just talked about€¦ however all of that is beside the point. If
Excel allows you to separate data from one column into two columns, why
wouldnt it also then allow you to take the two columns and rejoin them just
as easily? Why should I have to do three times as much work to do the same
action in reverse that Excel just allowed me to do very easily forwards?

Its as if Excel said, €œyes I can add 1 + 1 together for you to make the
number 2, however if you want to take the number 2 and make it back into 1
and 1, youll need to do calculus all on your own€¦ I cant help with that.€�

The two actions just go together; they cant provide one half of a service
and not the other. Its like buying a car and having the salesman tell you
that it can only go forwards, never in reverse.

I also know for a fact that this feature isnt just something that I want.
I know many other Excel users that have complained to me that they wish Excel
had this function and cant understand why it already doesnt. If Excel can
take one column and make it into two, why cant it do the reverse, it just
makes common sense.

Stosh wrote...
"Harlan Grove" wrote:
If even 0.01% of Excel users each were allowed to add just one
must-have feature, Excel would become completely unwieldy.

I know there are macros, I know there are scripts, I know there are
formulas, I can even go out and buy an add-on pack that would add the
features I just talked about... however all of that is beside the point. If
Excel allows you to separate data from one column into two columns, why
wouldn't it also then allow you to take the two columns and rejoin them just
as easily? Why should I have to do three times as much work to do the same
action in reverse that Excel just allowed me to do very easily forwards?

Excel doesn't need to provide the opposite of every current feature.
Such as the opposite of Paste Special, Values.

There are MANY ways to perform the operation you describe, it's just
that none of those ways is a simple, single menu command.

Actually, if the data you want to recombine is the only data in the
particular worksheet, you could save the worksheet in CSV format to
have it recombined with comma or OS list separator character between
fields, or you could save it in Formatted Text (PRN) format to have it
recombined into fixed width fields.

Fortunately Excel 2007 has passed feature lock, so this won't make it
into Excel 2007. With luck it'll never make it in. If Excel features
could be voted into being, my vote would offset yours on this.

It's as if Excel said, "yes I can add 1 + 1 together for you to make the
number 2, however if you want to take the number 2 and make it back into 1
and 1, you'll need to do calculus all on your own... I can't help with that."

Fatally flawed analogy. It's more in line with Excel being able to open
files in more file formats that it'll save in (at least in Excel 2007,
which will read Excel 2 through 4 XLS and Excel 4 XLW files, but won't
save in those formats). Or convert pivot tables back into simple
tables.

The two actions just go together; they can't provide one half of a service
and not the other. It's like buying a car and having the salesman tell you
that it can only go forwards, never in reverse.

Since they already do, and have commanding market share, it looks like
they can indeed provide only one side and not the other. And another
flawed analogy. It's more like priests being willing to preside over
marriages but not divorces. Marriage and divorce are two sides of the
same coin, but they don't need to be handled by the same 3rd parties.

I also know for a fact that this feature isn't just something that I want.
I know many other Excel users that have complained to me that they wish Excel
had this function and can't understand why it already doesn't. If Excel can
take one column and make it into two, why can't it do the reverse, it just
makes common sense.

And the responses you're received show that there are several other
people who've figured out how to use the tools Excel does provide to do
this. Yes, it'd be simpler if this were built in. So would be regular
expression support, eigenvector/eigenvalue calculation, complex matrix
operations, time series support, a formatting layer that'd allow
character by character formatting within formula results, . . .

What you're asking for is ridiculously simple to do in comparison
already.

"Harlan Grove" wrote:

What you're asking for is ridiculously simple to do in comparison
already.

Look, I dont know why you are so bent on shooting this down and are being
so negative, and frankly I dont care. I am trying to make a positive
contribution to the project, unlike you telling me that what I have to offer
is worthless and I never should have bothered. If you dont like it thats
fine, but you dont see me ranting on every thread that what is already in
the program is good enough. How can something ever get better if what it has
is always good enough? Also, if Excel has passed feature lock, then why
would they still be asking for suggestions? This threads existence proves
that wrong. And luckily there have been more positive votes on this thread
than your one negative, so thank you for voting. All I know is that
Microsoft ASKED me to post here, so I did.

Stosh wrote...
....
. . . I am trying to make a positive
contribution to the project, unlike you telling me that what I have to offer
is worthless and I never should have bothered. . . .

I didn't say it was worthless. I said it was unnecessary. I also said,
"Yes, it'd be simpler if this were built in."

That said, my priorities differ from yours, and your feature from my
perspective is unnecessary bloat. Excel is already more bloated than
I'd like, so your suggestion from my perspective would make Excel
worse. Don't I get to point this out?

. . . If you don't like it that's
fine, but you don't see me ranting on every thread that what is already in
the program is good enough. . . .

That I've NEVER said. Excel has ample room for improvement and
streamlining. But that doesn't mean I need to believe your pet feature
is a good idea.

. . . How can something ever get better if what it has is always good enough? . . .

There are way too many examples of software getting worse in aggregate
from too many individual, well-intentioned features being added. In a
program already as bulky and complex as Excel, the developers should be
VERY restrained about adding new features. Indiscriminately adding
features seldom makes software better.


. . . Also, if Excel has passed feature lock, then why would they still be asking for
suggestions? . . .

You don't understand that facility. They're not asking for suggestions
specific to Excel 2007. The suggestion box has been around for years,
and at this point it'd be for Excel 2010. In general, features are
never added only withdrawn after a program enters the beta testing
phase.

. . . This thread's existence proves that wrong. . . .

Not quite. I just want to keep Excel 2010's incremental bulkiness as
limited as possible.

. . . And luckily there have been more positive votes on this thread
than your one negative, so thank you for voting. All I know is that
Microsoft ASKED me to post here, so I did.

Since I don't use Microsoft's web portal, I have no idea what any
'votes' would be. Don't really care either.