software

I want to copy over text from various fields into a single field.
Here is the only solution (my limited time and my limited programing savy) I
have found so far. Is there a way to minimise or shorten this
equation/formula:

=A3amp;quot; quot;amp;A4amp;quot; quot;amp;A5amp;quot; quot;amp;A6amp;quot; quot;amp;A7amp;quot; quot;amp;A8amp;quot; quot;amp;A9amp;quot; quot;amp;A10amp;quot; quot;amp;A11amp;quot; quot;amp;A12amp;quot;
quot;amp;A13amp;quot; quot;amp;A14amp;quot; quot;amp;A15amp;quot; quot;amp;A16amp;quot; quot;amp;A17amp;quot; quot;amp;A18amp;quot; quot;amp;A19amp;quot; quot;amp;A20amp;quot; quot;amp;A21amp;quot;
quot;amp;A22amp;quot; quot;amp;A23amp;quot; quot;amp;A24amp;quot; quot;amp;A25amp;quot; quot;amp;A26amp;quot; quot;amp;A27

In essense, I want to tell a cell to capture the info in a3 through a27 with
a space inbetween data/value returned.

ThanksTry this small macro:Function glue_it(R As Range) As String
Dim rr As Range
glue_it = quot;quot;
For Each rr In R
glue_it = glue_it amp; quot; quot; amp; rr.Value
Next
End Function

Use it as =glue_it(A3:A27)
--
Gary''s Studentquot;Hatmanquot; wrote:

gt; I want to copy over text from various fields into a single field.
gt; Here is the only solution (my limited time and my limited programing savy) I
gt; have found so far. Is there a way to minimise or shorten this
gt; equation/formula:
gt;
gt; =A3amp;quot; quot;amp;A4amp;quot; quot;amp;A5amp;quot; quot;amp;A6amp;quot; quot;amp;A7amp;quot; quot;amp;A8amp;quot; quot;amp;A9amp;quot; quot;amp;A10amp;quot; quot;amp;A11amp;quot; quot;amp;A12amp;quot;
gt; quot;amp;A13amp;quot; quot;amp;A14amp;quot; quot;amp;A15amp;quot; quot;amp;A16amp;quot; quot;amp;A17amp;quot; quot;amp;A18amp;quot; quot;amp;A19amp;quot; quot;amp;A20amp;quot; quot;amp;A21amp;quot;
gt; quot;amp;A22amp;quot; quot;amp;A23amp;quot; quot;amp;A24amp;quot; quot;amp;A25amp;quot; quot;amp;A26amp;quot; quot;amp;A27
gt;
gt; In essense, I want to tell a cell to capture the info in a3 through a27 with
gt; a space inbetween data/value returned.
gt;
gt; Thanks
gt;

Hi Gary''s Student,
I sometimes need to do that, so I copied your glue_it function into my
personal.xls.
As it stands the result has a leading space so I've changed it to fix
that and I've also made it volatile...

Function glue_it(R As Range) As String
Application.Volatile
Dim rr As Range
glue_it = quot;quot;
For Each rr In R
glue_it = glue_it amp; quot; quot; amp; rr.Value
Next
glue_it = Application.Trim(glue_it)
End Function

Ken JohnsonThanks a bunch, this worked.
Now I was a pain in High School for my Math teachers, I always wanted to
know the rhyme and reason behind formulas, and such I shall continue being so
here in order to learn.
Can you be so kind as to walk me through the logic (the why's of the
equation) of your function.
I just like to be able to understand what it does.
Let me take a crack at it.
Function glue_it : This names the function
THe rest is mumbo jumbo to me.

quot;Gary''s Studentquot; wrote:

gt; Try this small macro:
gt;
gt;
gt; Function glue_it(R As Range) As String
gt; Dim rr As Range
gt; glue_it = quot;quot;
gt; For Each rr In R
gt; glue_it = glue_it amp; quot; quot; amp; rr.Value
gt; Next
gt; End Function
gt;
gt; Use it as =glue_it(A3:A27)gt;gt; In essense, I want to tell a cell to capture the info in a3 through a27
gt;gt; with
gt;gt; a space in-between data/value returned.

If your data is just one column, here's another option...

Function GlueIt(rng) As String
GlueIt = Join(WorksheetFunction.Transpose(rng), Space(1))
End Function

--
HTH. :gt;)

Dana DeLouis
Windows XP, Office 2003
quot;Gary''s Studentquot; gt; wrote in message
...
gt; Try this small macro:
gt;
gt;
gt; Function glue_it(R As Range) As String
gt; Dim rr As Range
gt; glue_it = quot;quot;
gt; For Each rr In R
gt; glue_it = glue_it amp; quot; quot; amp; rr.Value
gt; Next
gt; End Function
gt;
gt; Use it as =glue_it(A3:A27)
gt; --
gt; Gary''s Student
gt;
gt;
gt; quot;Hatmanquot; wrote:
gt;
gt;gt; I want to copy over text from various fields into a single field.
gt;gt; Here is the only solution (my limited time and my limited programing
gt;gt; savy) I
gt;gt; have found so far. Is there a way to minimise or shorten this
gt;gt; equation/formula:
gt;gt;
gt;gt; =A3amp;quot; quot;amp;A4amp;quot; quot;amp;A5amp;quot; quot;amp;A6amp;quot; quot;amp;A7amp;quot; quot;amp;A8amp;quot; quot;amp;A9amp;quot; quot;amp;A10amp;quot; quot;amp;A11amp;quot; quot;amp;A12amp;quot;
gt;gt; quot;amp;A13amp;quot; quot;amp;A14amp;quot; quot;amp;A15amp;quot; quot;amp;A16amp;quot; quot;amp;A17amp;quot; quot;amp;A18amp;quot; quot;amp;A19amp;quot; quot;amp;A20amp;quot; quot;amp;A21amp;quot;
gt;gt; quot;amp;A22amp;quot; quot;amp;A23amp;quot; quot;amp;A24amp;quot; quot;amp;A25amp;quot; quot;amp;A26amp;quot; quot;amp;A27
gt;gt;
gt;gt; In essense, I want to tell a cell to capture the info in a3 through a27
gt;gt; with
gt;gt; a space inbetween data/value returned.
gt;gt;
gt;gt; Thanks
gt;gt;