Instead of using the CONCATENATE function, which I assume you are, use the & operator.
In B2 put =A1 & "," & A2
In B3 put =B2 & "," & A3
Then copy down.
Back once again with a new problem.
So here is my deal.
I have a list of terms in a spreadsheet. Assume they start in cell A1 and they descend down for the next 300 cells. Basically I need to pull those terms into a single text string where the terms are comma delimited.
What I have been doing is concatenating them so they all end with a comma, copy them 25 at a time, pasting values and transposing then running a concatenate formula for the 25 terms. Do this 10-15 times to create that many comma delimited lists then concatenate those lists to create one all in one list.
The list (starting in A1) looks like this:
but I need:
If possible to do this with a formula please do so as my knowledge of using VBA modules is limited but if this must be done using VBA please realize that I'm in the thrid grade compared to your knowledge so please explain how to implement the module as clearly as possible.
Create your own function to loop thru the values and concatenate them. If you know how to insert a module, put this in one.
Put this in the cell to grab the first 16 cells of column I:VB:Function csvRange(myRange As Range) Dim csvRangeOutput For Each entry In myRange csvRangeOutput = csvRangeOutput & entry.Value & ", " Next csvRange = csvRangeOutput End Function
Last edited by flee01; October 27th, 2006 at 05:31.
If you want code then you could use this which eliminates the loop.
Note this will only work if the range passed to the function is only 1 column.VB:Function csvRange(myRange As Range) csvRange = myRange csvRange = Application.Transpose(csvRange) csvRange = Join(csvRange, ",") End Function
If you are dealing with multiple columns use flee01's code.
How do I do it the other way around? I want to take a list of words separate by a comma in a single cell to populate into a column with each word without the comma presented. More important, I want to do the following:
A1 contains: "apples, oranges, peaches"
A2 contains: "yellow, black, blue"
I want to put each word (without the comma) into a single cell all in one column:
How do I do this?
There are currently 3 users browsing this thread. (0 members and 3 guests)