| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.setup
|
|||
|
|||
How do I add a comma between a range of cells?
Example: I have cells A1 through A70 with data in each cell in the following
format 123-03-123. I need a sting of text returned that will combine the cells separated by a comma. Example: 123-34-234,234-23-234,345-23-123, etc... I need to be able to copy and paste the string in to a websearch that specifies "separated by commas no spaces" I know there has to be a simple way to do that but I am a rookie in Excel :( Thanks for all your help... Chris |
|
#2
Posted to microsoft.public.excel.setup
|
|||
|
|||
|
How do I add a comma between a range of cells?
With 70 cells I would use a user defined function. Function ConCatRange(CellBlock As Range) As String Dim cell As Range Dim sbuf As String For Each cell In CellBlock If Len(cell.text) 0 Then sbuf = sbuf & cell.text & "," Next ConCatRange = Left(sbuf, Len(sbuf) - 1) End Function After concatenating the range using =ConCatRange(A1:A70) copy/paste specialvalues. Gord Dibben MS Excel MVP On Tue, 19 Jun 2007 15:03:41 -0700, AZ ChrisD <AZ wrote: Example: I have cells A1 through A70 with data in each cell in the following format 123-03-123. I need a sting of text returned that will combine the cells separated by a comma. Example: 123-34-234,234-23-234,345-23-123, etc... I need to be able to copy and paste the string in to a websearch that specifies "separated by commas no spaces" I know there has to be a simple way to do that but I am a rookie in Excel :( Thanks for all your help... Chris |
|
#3
Posted to microsoft.public.excel.setup
|
|||
|
|||
|
How do I add a comma between a range of cells?
Hi
assuming the concatenated string is in A1 =SUBSTITUTE(A1,"-",",") -- Regards Roger Govier "AZ ChrisD" <AZ wrote in message ... Example: I have cells A1 through A70 with data in each cell in the following format 123-03-123. I need a sting of text returned that will combine the cells separated by a comma. Example: 123-34-234,234-23-234,345-23-123, etc... I need to be able to copy and paste the string in to a websearch that specifies "separated by commas no spaces" I know there has to be a simple way to do that but I am a rookie in Excel :( Thanks for all your help... Chris |
|
#4
Posted to microsoft.public.excel.setup
|
|||
|
|||
|
How do I add a comma between a range of cells?
Ignore that.
I totally misread your question. -- Regards Roger Govier "Roger Govier" wrote in message ... Hi assuming the concatenated string is in A1 =SUBSTITUTE(A1,"-",",") -- Regards Roger Govier "AZ ChrisD" <AZ wrote in message ... Example: I have cells A1 through A70 with data in each cell in the following format 123-03-123. I need a sting of text returned that will combine the cells separated by a comma. Example: 123-34-234,234-23-234,345-23-123, etc... I need to be able to copy and paste the string in to a websearch that specifies "separated by commas no spaces" I know there has to be a simple way to do that but I am a rookie in Excel :( Thanks for all your help... Chris |
|
#5
Posted to microsoft.public.excel.setup
|
|||
|
|||
|
How do I add a comma between a range of cells?
That did it...
Thanks a bunch "Gord Dibben" wrote: With 70 cells I would use a user defined function. Function ConCatRange(CellBlock As Range) As String Dim cell As Range Dim sbuf As String For Each cell In CellBlock If Len(cell.text) 0 Then sbuf = sbuf & cell.text & "," Next ConCatRange = Left(sbuf, Len(sbuf) - 1) End Function After concatenating the range using =ConCatRange(A1:A70) copy/paste specialvalues. Gord Dibben MS Excel MVP On Tue, 19 Jun 2007 15:03:41 -0700, AZ ChrisD <AZ wrote: Example: I have cells A1 through A70 with data in each cell in the following format 123-03-123. I need a sting of text returned that will combine the cells separated by a comma. Example: 123-34-234,234-23-234,345-23-123, etc... I need to be able to copy and paste the string in to a websearch that specifies "separated by commas no spaces" I know there has to be a simple way to do that but I am a rookie in Excel :( Thanks for all your help... Chris |
|
#6
Posted to microsoft.public.excel.setup
|
|||
|
|||
|
How do I add a comma between a range of cells?
Good to hear.
Thanks for the feedback. On Wed, 20 Jun 2007 09:45:02 -0700, AZ ChrisD wrote: That did it... Thanks a bunch "Gord Dibben" wrote: With 70 cells I would use a user defined function. Function ConCatRange(CellBlock As Range) As String Dim cell As Range Dim sbuf As String For Each cell In CellBlock If Len(cell.text) 0 Then sbuf = sbuf & cell.text & "," Next ConCatRange = Left(sbuf, Len(sbuf) - 1) End Function After concatenating the range using =ConCatRange(A1:A70) copy/paste specialvalues. Gord Dibben MS Excel MVP On Tue, 19 Jun 2007 15:03:41 -0700, AZ ChrisD <AZ wrote: Example: I have cells A1 through A70 with data in each cell in the following format 123-03-123. I need a sting of text returned that will combine the cells separated by a comma. Example: 123-34-234,234-23-234,345-23-123, etc... I need to be able to copy and paste the string in to a websearch that specifies "separated by commas no spaces" I know there has to be a simple way to do that but I am a rookie in Excel :( Thanks for all your help... Chris |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I delete text to right of a comma in a range of cells? | Excel Discussion (Misc queries) | |||
| need to remove a comma from end of test in cells | Excel Worksheet Functions | |||
| Put all cells in one cell seperated by comma | New Users to Excel | |||
| Extract comma broken address to cells | Excel Discussion (Misc queries) | |||
| How do i merge data in a row of cells to be comma separated in on. | Excel Discussion (Misc queries) | |||
Linear Mode
