Indirect, Concatenate, & ?
 

I have a column of data ("Original") that I added a comma to in the adjoining
column:


Original Added Comma

I6680 I6680,
M1121 M1121,
B0265 B0265,
E0003 E0003,
X6126 X6126,
M2686 M2686,

I would like to have a single cell look like this:
I6680,M1121,B0265,E0003,X6126,M2686.......

I have have successfully done this by =B1&B2&B3&B4&B5&B6&B7&B9&B10...

Is there an easier way than having to manually typing that formula???


TIA!

JEFF wrote...
....
I would like to have a single cell look like this:
I6680,M1121,B0265,E0003,X6126,M2686.......

I have have successfully done this by =B1&B2&B3&B4&B5&B6&B7&B9&B10...

Is there an easier way than having to manually typing that formula???

No easier way unless you're willing to use an add-in or VBA.

The best add-in for this is Laurent Longre's MOREFUNC.XLL, which is
available from http://xcell05.free.fr/english/. Once installed, it
provides a function named MCONCAT which you could use as
MCONCAT(A1:A100,",").

One VBA possibility is given in

http://groups-beta.google.com/group/...e=source&hl=en

(or http://makeashorterlink.com/?Z6B425D1B ).

You can't do this easily with formulas, there is no real way to do recursive
functions... it can be done very easily with a VBA function, but if you're
afraid of VBA that won't work. :)

If not... hit ALT-F11 to access your VBA editor. In the upper-left, there is
a list of objects in your workbook. RIGHT-Click on the very top object,
which says something like "VBAProject (Book1)" and choose "Insert - Module"
.... this will add a folder down below that says Modules, and give you a
blank white screen. At this point the title bar should have "[Module1
(Code)]" at the end of its title. You're in the right place!

Next, copy and paste everything between the --'s into the empty window:
----------
Public Function TextConcat(cCells As Range) As String

Dim cCell As Range
Dim cString As String

For Each cCell In cCells
cString = cString & cCell.Text
Next cCell

TextConcat = cString

End Function
----------

Close your VBA editor window and you're back on your worksheet. Go to the
cell where you want your concatenated data, and use your newly created
function: =textconcat(A1:A9) ...etc. :) The only side-effect is the macro
warning when you open the file, but there are other ways to get around that,
see other posts in the excel.programming newsgroup.


"JEFF" wrote in message
...
I have a column of data ("Original") that I added a comma to in the
adjoining
column:


Original Added Comma

I6680 I6680,
M1121 M1121,
B0265 B0265,
E0003 E0003,
X6126 X6126,
M2686 M2686,

I would like to have a single cell look like this:
I6680,M1121,B0265,E0003,X6126,M2686.......

I have have successfully done this by =B1&B2&B3&B4&B5&B6&B7&B9&B10...

Is there an easier way than having to manually typing that formula???


TIA!

Beautiful! Any chance you could walk me through the code?

"JPW" wrote:

You can't do this easily with formulas, there is no real way to do recursive
functions... it can be done very easily with a VBA function, but if you're
afraid of VBA that won't work. :)

If not... hit ALT-F11 to access your VBA editor. In the upper-left, there is
a list of objects in your workbook. RIGHT-Click on the very top object,
which says something like "VBAProject (Book1)" and choose "Insert - Module"
.... this will add a folder down below that says Modules, and give you a
blank white screen. At this point the title bar should have "[Module1
(Code)]" at the end of its title. You're in the right place!

Next, copy and paste everything between the --'s into the empty window:
----------
Public Function TextConcat(cCells As Range) As String

Dim cCell As Range
Dim cString As String

For Each cCell In cCells
cString = cString & cCell.Text
Next cCell

TextConcat = cString

End Function
----------

Close your VBA editor window and you're back on your worksheet. Go to the
cell where you want your concatenated data, and use your newly created
function: =textconcat(A1:A9) ...etc. :) The only side-effect is the macro
warning when you open the file, but there are other ways to get around that,
see other posts in the excel.programming newsgroup.


"JEFF" wrote in message
...
I have a column of data ("Original") that I added a comma to in the
adjoining
column:


Original Added Comma

I6680 I6680,
M1121 M1121,
B0265 B0265,
E0003 E0003,
X6126 X6126,
M2686 M2686,

I would like to have a single cell look like this:
I6680,M1121,B0265,E0003,X6126,M2686.......

I have have successfully done this by =B1&B2&B3&B4&B5&B6&B7&B9&B10...

Is there an easier way than having to manually typing that formula???


TIA!

Are you looking for an explanation of what the code is doing? Did you try
using this code to see if it meets your needs?

"JEFF" wrote in message
...
Beautiful! Any chance you could walk me through the code?

"JPW" wrote:

You can't do this easily with formulas, there is no real way to do
recursive
functions... it can be done very easily with a VBA function, but if
you're
afraid of VBA that won't work. :)

If not... hit ALT-F11 to access your VBA editor. In the upper-left, there
is
a list of objects in your workbook. RIGHT-Click on the very top object,
which says something like "VBAProject (Book1)" and choose "Insert -
Module"
.... this will add a folder down below that says Modules, and give you a
blank white screen. At this point the title bar should have "[Module1
(Code)]" at the end of its title. You're in the right place!

Next, copy and paste everything between the --'s into the empty window:
----------
Public Function TextConcat(cCells As Range) As String

Dim cCell As Range
Dim cString As String

For Each cCell In cCells
cString = cString & cCell.Text
Next cCell

TextConcat = cString

End Function
----------

Close your VBA editor window and you're back on your worksheet. Go to the
cell where you want your concatenated data, and use your newly created
function: =textconcat(A1:A9) ...etc. :) The only side-effect is the macro
warning when you open the file, but there are other ways to get around
that,
see other posts in the excel.programming newsgroup.


"JEFF" wrote in message
...
I have a column of data ("Original") that I added a comma to in the
adjoining
column:


Original Added Comma

I6680 I6680,
M1121 M1121,
B0265 B0265,
E0003 E0003,
X6126 X6126,
M2686 M2686,

I would like to have a single cell look like this:
I6680,M1121,B0265,E0003,X6126,M2686.......

I have have successfully done this by =B1&B2&B3&B4&B5&B6&B7&B9&B10...

Is there an easier way than having to manually typing that formula???


TIA!

It works well, with one proviso: If I add an account, I need to refresh it
by going into the formula bar and hitting the return..... Not a big deal,
but I'm not the end user. Other than that, it is awesome. I was just
looking for some verbiage as to the code and what I am instructing excel to
do....

Thanks.

"JPW" wrote:

Are you looking for an explanation of what the code is doing? Did you try
using this code to see if it meets your needs?

"JEFF" wrote in message
...
Beautiful! Any chance you could walk me through the code?

"JPW" wrote:

You can't do this easily with formulas, there is no real way to do
recursive
functions... it can be done very easily with a VBA function, but if
you're
afraid of VBA that won't work. :)

If not... hit ALT-F11 to access your VBA editor. In the upper-left, there
is
a list of objects in your workbook. RIGHT-Click on the very top object,
which says something like "VBAProject (Book1)" and choose "Insert -
Module"
.... this will add a folder down below that says Modules, and give you a
blank white screen. At this point the title bar should have "[Module1
(Code)]" at the end of its title. You're in the right place!

Next, copy and paste everything between the --'s into the empty window:
----------
Public Function TextConcat(cCells As Range) As String

Dim cCell As Range
Dim cString As String

For Each cCell In cCells
cString = cString & cCell.Text
Next cCell

TextConcat = cString

End Function
----------

Close your VBA editor window and you're back on your worksheet. Go to the
cell where you want your concatenated data, and use your newly created
function: =textconcat(A1:A9) ...etc. :) The only side-effect is the macro
warning when you open the file, but there are other ways to get around
that,
see other posts in the excel.programming newsgroup.


"JEFF" wrote in message
...
I have a column of data ("Original") that I added a comma to in the
adjoining
column:


Original Added Comma

I6680 I6680,
M1121 M1121,
B0265 B0265,
E0003 E0003,
X6126 X6126,
M2686 M2686,

I would like to have a single cell look like this:
I6680,M1121,B0265,E0003,X6126,M2686.......

I have have successfully done this by =B1&B2&B3&B4&B5&B6&B7&B9&B10...

Is there an easier way than having to manually typing that formula???


TIA!

Aha, fix for your refreshing problem: directly under the Public Function
line, add this line:
Application.Volatile

As for the code itself... brief explanations follow. For more information on
how to build VBA functions you may want to google for some tutorials on
basic syntax and the like.

'--The first line names our function and defines what goes in (a Range) and
out (a String)
Public Function TextConcat(cCells As Range) As String
Application.Volatile '--Tells Excel to recalculate this with each change

Dim cCell As Range '--Says "cCell" will represent a Range (one or more
cells)
Dim cString As String '--Says "cString" will represent a String of
characters

For Each cCell In cCells '--Steps through each cell in the selected area.
cString = cString & cCell.Text '--Adds each new cell's text to the variable
cString
Next cCell '--Moves on to the next cell

TextConcat = cString '--Passes the variable cString back as the formula
"result"

End Function




"JEFF" wrote in message
...
It works well, with one proviso: If I add an account, I need to refresh
it
by going into the formula bar and hitting the return..... Not a big deal,
but I'm not the end user. Other than that, it is awesome. I was just
looking for some verbiage as to the code and what I am instructing excel
to
do....

Thanks.

"JPW" wrote:

Are you looking for an explanation of what the code is doing? Did you try
using this code to see if it meets your needs?

"JEFF" wrote in message
...
Beautiful! Any chance you could walk me through the code?

"JPW" wrote:

You can't do this easily with formulas, there is no real way to do
recursive
functions... it can be done very easily with a VBA function, but if
you're
afraid of VBA that won't work. :)

If not... hit ALT-F11 to access your VBA editor. In the upper-left,
there
is
a list of objects in your workbook. RIGHT-Click on the very top
object,
which says something like "VBAProject (Book1)" and choose "Insert -
Module"
.... this will add a folder down below that says Modules, and give you
a
blank white screen. At this point the title bar should have "[Module1
(Code)]" at the end of its title. You're in the right place!

Next, copy and paste everything between the --'s into the empty
window:
----------
Public Function TextConcat(cCells As Range) As String

Dim cCell As Range
Dim cString As String

For Each cCell In cCells
cString = cString & cCell.Text
Next cCell

TextConcat = cString

End Function
----------

Close your VBA editor window and you're back on your worksheet. Go to
the
cell where you want your concatenated data, and use your newly created
function: =textconcat(A1:A9) ...etc. :) The only side-effect is the
macro
warning when you open the file, but there are other ways to get around
that,
see other posts in the excel.programming newsgroup.


"JEFF" wrote in message
...
I have a column of data ("Original") that I added a comma to in the
adjoining
column:


Original Added Comma

I6680 I6680,
M1121 M1121,
B0265 B0265,
E0003 E0003,
X6126 X6126,
M2686 M2686,

I would like to have a single cell look like this:
I6680,M1121,B0265,E0003,X6126,M2686.......

I have have successfully done this by
=B1&B2&B3&B4&B5&B6&B7&B9&B10...

Is there an easier way than having to manually typing that
formula???


TIA!

Thanks for the tutorial.... Unfortunately, the refresh is still necessary

"JPW" wrote:

Aha, fix for your refreshing problem: directly under the Public Function
line, add this line:
Application.Volatile

As for the code itself... brief explanations follow. For more information on
how to build VBA functions you may want to google for some tutorials on
basic syntax and the like.

'--The first line names our function and defines what goes in (a Range) and
out (a String)
Public Function TextConcat(cCells As Range) As String
Application.Volatile '--Tells Excel to recalculate this with each change

Dim cCell As Range '--Says "cCell" will represent a Range (one or more
cells)
Dim cString As String '--Says "cString" will represent a String of
characters

For Each cCell In cCells '--Steps through each cell in the selected area.
cString = cString & cCell.Text '--Adds each new cell's text to the variable
cString
Next cCell '--Moves on to the next cell

TextConcat = cString '--Passes the variable cString back as the formula
"result"

End Function




"JEFF" wrote in message
...
It works well, with one proviso: If I add an account, I need to refresh
it
by going into the formula bar and hitting the return..... Not a big deal,
but I'm not the end user. Other than that, it is awesome. I was just
looking for some verbiage as to the code and what I am instructing excel
to
do....

Thanks.

"JPW" wrote:

Are you looking for an explanation of what the code is doing? Did you try
using this code to see if it meets your needs?

"JEFF" wrote in message
...
Beautiful! Any chance you could walk me through the code?

"JPW" wrote:

You can't do this easily with formulas, there is no real way to do
recursive
functions... it can be done very easily with a VBA function, but if
you're
afraid of VBA that won't work. :)

If not... hit ALT-F11 to access your VBA editor. In the upper-left,
there
is
a list of objects in your workbook. RIGHT-Click on the very top
object,
which says something like "VBAProject (Book1)" and choose "Insert -
Module"
.... this will add a folder down below that says Modules, and give you
a
blank white screen. At this point the title bar should have "[Module1
(Code)]" at the end of its title. You're in the right place!

Next, copy and paste everything between the --'s into the empty
window:
----------
Public Function TextConcat(cCells As Range) As String

Dim cCell As Range
Dim cString As String

For Each cCell In cCells
cString = cString & cCell.Text
Next cCell

TextConcat = cString

End Function
----------

Close your VBA editor window and you're back on your worksheet. Go to
the
cell where you want your concatenated data, and use your newly created
function: =textconcat(A1:A9) ...etc. :) The only side-effect is the
macro
warning when you open the file, but there are other ways to get around
that,
see other posts in the excel.programming newsgroup.


"JEFF" wrote in message
...
I have a column of data ("Original") that I added a comma to in the
adjoining
column:


Original Added Comma

I6680 I6680,
M1121 M1121,
B0265 B0265,
E0003 E0003,
X6126 X6126,
M2686 M2686,

I would like to have a single cell look like this:
I6680,M1121,B0265,E0003,X6126,M2686.......

I have have successfully done this by
=B1&B2&B3&B4&B5&B6&B7&B9&B10...

Is there an easier way than having to manually typing that
formula???


TIA!