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

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 special>values.


Gord Dibben MS Excel MVP

On Tue, 19 Jun 2007 15:03:41 -0700, AZ ChrisD <AZ
ChrisD@discussions.microsoft.com> 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

Hi

assuming the concatenated string is in A1
=SUBSTITUTE(A1,"-",",")


--
Regards

Roger Govier


"AZ ChrisD" <AZ ChrisD@discussions.microsoft.com> wrote in message
news:62EA0F29-7DE5-45E3-9FF7-53E389A1C7F5@microsoft.com...
> 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

Ignore that.
I totally misread your question.

--
Regards

Roger Govier


"Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message
news:epjkPZtsHHA.1416@TK2MSFTNGP06.phx.gbl...
> Hi
>
> assuming the concatenated string is in A1
> =SUBSTITUTE(A1,"-",",")
>
>
> --
> Regards
>
> Roger Govier
>
>
> "AZ ChrisD" <AZ ChrisD@discussions.microsoft.com> wrote in message
> news:62EA0F29-7DE5-45E3-9FF7-53E389A1C7F5@microsoft.com...
>> 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
>
>

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 special>values.
>
>
> Gord Dibben MS Excel MVP
>
> On Tue, 19 Jun 2007 15:03:41 -0700, AZ ChrisD <AZ
> ChrisD@discussions.microsoft.com> 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
>
>

Good to hear.

Thanks for the feedback.

On Wed, 20 Jun 2007 09:45:02 -0700, AZ ChrisD
<AZChrisD@discussions.microsoft.com> 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 special>values.
>>
>>
>> Gord Dibben MS Excel MVP
>>
>> On Tue, 19 Jun 2007 15:03:41 -0700, AZ ChrisD <AZ
>> ChrisD@discussions.microsoft.com> 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
>>
>>