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

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

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

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

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

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