How do you repeat a function in a formula

[Guest]

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:

=A3&" "&A4&" "&A5&" "&A6&" "&A7&" "&A8&" "&A9&" "&A10&" "&A11&" "&A12&"
"&A13&" "&A14&" "&A15&" "&A16&" "&A17&" "&A18&" "&A19&" "&A20&" "&A21&"
"&A22&" "&A23&" "&A24&" "&A25&" "&A26&" "&A27

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

Thanks

 

[Guest]

Try this small macro:


Function glue_it(R As Range) As String
Dim rr As Range
glue_it = ""
For Each rr In R
glue_it = glue_it & " " & rr.Value
Next
End Function

Use it as =glue_it(A3:A27)

 

[Ken Johnson]

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 = ""
For Each rr In R
glue_it = glue_it & " " & rr.Value
Next
glue_it = Application.Trim(glue_it)
End Function

Ken Johnson

 

[Guest]

Thanks 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.

 

[Dana DeLouis]

In essense, I want to tell a cell to capture the info in a3 through a27
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. :>)

Dana DeLouis
Windows XP, Office 2003