Indirect, Concatenate, & ?

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

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