Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do you repeat a function in a formula
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do you repeat a function in a formula
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) -- Gary''s Student "Hatman" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do you repeat a function in a formula
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do you repeat a function in a formula
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. "Gary''s Student" wrote: 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) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do you repeat a function in a formula
In essense, I want to tell a cell to capture the info in a3 through a27
with 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. :) Dana DeLouis Windows XP, Office 2003 "Gary''s Student" wrote in message ... 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) -- Gary''s Student "Hatman" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Price function difference in Output formula vis a vis Manual Calculation | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
function CELL() to return the formula in the referenced cell | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Function Formula is displayed not results | Excel Worksheet Functions |