Hello I have a list of numbers (exported from another application about 300
numbers) I have to create groups of numbers in one cell and each number
separated by a comma. Some groups have 20 numbers others 5 etc. I tried the
concatenante function with the "," between numbers take too long. Is there a
faster way to do this? thanks in advance

You can use a macro or a User Defined Function.

Macro................

Sub ConCat_Cells()
Dim x As Range
Dim y As Range
Dim z As Range
Dim w As String
Dim sbuf As String
On Error GoTo endit
w = InputBox("Enter the Type of De-limiter Desired")
Set z = Application.InputBox("Select Destination Cell", _
"Destination Cell", , , , , , 8)
Application.SendKeys "+{F8}"
Set x = Application.InputBox("Select Cells...Contiguous or Non-Contiguous", _
"Cells Selection", , , , , , 8)
For Each y In x
If Len(y.text) 0 Then sbuf = sbuf & y.text & w
Next
z = Left(sbuf, Len(sbuf) - 1)
Exit Sub
endit:
MsgBox "Nothing Selected. Please try again."
End Sub

UDF..................

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


Gord Dibben MS Excel MVP

On Tue, 9 Jan 2007 07:11:01 -0800, Wanna Learn
wrote:

Hello I have a list of numbers (exported from another application about 300
numbers) I have to create groups of numbers in one cell and each number
separated by a comma. Some groups have 20 numbers others 5 etc. I tried the
concatenante function with the "," between numbers take too long. Is there a
faster way to do this? thanks in advance

Thanks Gord
This is excellent. I 'm doing something wrong .....
This is my answer 30,705,120,949 it should read 307051,20940 thanks

"Gord Dibben" wrote:

You can use a macro or a User Defined Function.

Macro................

Sub ConCat_Cells()
Dim x As Range
Dim y As Range
Dim z As Range
Dim w As String
Dim sbuf As String
On Error GoTo endit
w = InputBox("Enter the Type of De-limiter Desired")
Set z = Application.InputBox("Select Destination Cell", _
"Destination Cell", , , , , , 8)
Application.SendKeys "+{F8}"
Set x = Application.InputBox("Select Cells...Contiguous or Non-Contiguous", _
"Cells Selection", , , , , , 8)
For Each y In x
If Len(y.text) 0 Then sbuf = sbuf & y.text & w
Next
z = Left(sbuf, Len(sbuf) - 1)
Exit Sub
endit:
MsgBox "Nothing Selected. Please try again."
End Sub

UDF..................

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


Gord Dibben MS Excel MVP

On Tue, 9 Jan 2007 07:11:01 -0800, Wanna Learn
wrote:

Hello I have a list of numbers (exported from another application about 300
numbers) I have to create groups of numbers in one cell and each number
separated by a comma. Some groups have 20 numbers others 5 etc. I tried the
concatenante function with the "," between numbers take too long. Is there a
faster way to do this? thanks in advance

Gord, Sorry I know what I was doing wrong. Again thanks this is a time saver
and definetly a keeper

"Gord Dibben" wrote:

You can use a macro or a User Defined Function.

Macro................

Sub ConCat_Cells()
Dim x As Range
Dim y As Range
Dim z As Range
Dim w As String
Dim sbuf As String
On Error GoTo endit
w = InputBox("Enter the Type of De-limiter Desired")
Set z = Application.InputBox("Select Destination Cell", _
"Destination Cell", , , , , , 8)
Application.SendKeys "+{F8}"
Set x = Application.InputBox("Select Cells...Contiguous or Non-Contiguous", _
"Cells Selection", , , , , , 8)
For Each y In x
If Len(y.text) 0 Then sbuf = sbuf & y.text & w
Next
z = Left(sbuf, Len(sbuf) - 1)
Exit Sub
endit:
MsgBox "Nothing Selected. Please try again."
End Sub

UDF..................

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


Gord Dibben MS Excel MVP

On Tue, 9 Jan 2007 07:11:01 -0800, Wanna Learn
wrote:

Hello I have a list of numbers (exported from another application about 300
numbers) I have to create groups of numbers in one cell and each number
separated by a comma. Some groups have 20 numbers others 5 etc. I tried the
concatenante function with the "," between numbers take too long. Is there a
faster way to do this? thanks in advance

What does your original data look like?

307051 in one cell and 20940 in another?

Will return 307051,20940

Or are the numbers in 4 separate cells?

Will return 30,705,120,940


Gord

On Tue, 9 Jan 2007 10:14:01 -0800, Wanna Learn
wrote:

Thanks Gord
This is excellent. I 'm doing something wrong .....
This is my answer 30,705,120,949 it should read 307051,20940 thanks

"Gord Dibben" wrote:

You can use a macro or a User Defined Function.

Macro................

Sub ConCat_Cells()
Dim x As Range
Dim y As Range
Dim z As Range
Dim w As String
Dim sbuf As String
On Error GoTo endit
w = InputBox("Enter the Type of De-limiter Desired")
Set z = Application.InputBox("Select Destination Cell", _
"Destination Cell", , , , , , 8)
Application.SendKeys "+{F8}"
Set x = Application.InputBox("Select Cells...Contiguous or Non-Contiguous", _
"Cells Selection", , , , , , 8)
For Each y In x
If Len(y.text) 0 Then sbuf = sbuf & y.text & w
Next
z = Left(sbuf, Len(sbuf) - 1)
Exit Sub
endit:
MsgBox "Nothing Selected. Please try again."
End Sub

UDF..................

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


Gord Dibben MS Excel MVP

On Tue, 9 Jan 2007 07:11:01 -0800, Wanna Learn
wrote:

Hello I have a list of numbers (exported from another application about 300
numbers) I have to create groups of numbers in one cell and each number
separated by a comma. Some groups have 20 numbers others 5 etc. I tried the
concatenante function with the "," between numbers take too long. Is there a
faster way to do this? thanks in advance

Thanks for the feedback.

Happy to hear you're sorted out.


Gord

On Tue, 9 Jan 2007 10:22:00 -0800, Wanna Learn
wrote:

Gord, Sorry I know what I was doing wrong. Again thanks this is a time saver
and definetly a keeper

"Gord Dibben" wrote:

You can use a macro or a User Defined Function.

Macro................

Sub ConCat_Cells()
Dim x As Range
Dim y As Range
Dim z As Range
Dim w As String
Dim sbuf As String
On Error GoTo endit
w = InputBox("Enter the Type of De-limiter Desired")
Set z = Application.InputBox("Select Destination Cell", _
"Destination Cell", , , , , , 8)
Application.SendKeys "+{F8}"
Set x = Application.InputBox("Select Cells...Contiguous or Non-Contiguous", _
"Cells Selection", , , , , , 8)
For Each y In x
If Len(y.text) 0 Then sbuf = sbuf & y.text & w
Next
z = Left(sbuf, Len(sbuf) - 1)
Exit Sub
endit:
MsgBox "Nothing Selected. Please try again."
End Sub

UDF..................

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


Gord Dibben MS Excel MVP

On Tue, 9 Jan 2007 07:11:01 -0800, Wanna Learn
wrote:

Hello I have a list of numbers (exported from another application about 300
numbers) I have to create groups of numbers in one cell and each number
separated by a comma. Some groups have 20 numbers others 5 etc. I tried the
concatenante function with the "," between numbers take too long. Is there a
faster way to do this? thanks in advance

These solutions are close to what I need but not quite.
The situation is: I have several thousand records with a variable number of
records (up to 10) for each Store ID (Col A - string). Each record has data
(random length text) in Col B & Col C.
What I need to do is combine all info for all records of each store in one
cell (delimited ;)
For example: If I start with:

Col A B C
Store 1 Product 1 Name 1
Store 1 Product 2 Name 2
Store 1 Product 3 Name 3
Store 2 Product 4 Name 4
Store 2 Product 5 Name 5

I want to end up with:
Col A B C D
Store 1 Product 1 Name 1 Product1; Name1;
....Name3
Store 1 Product 2 Name 2
Store 1 Product 3 Name 3
Store 2 Product 4 Name 4 Product4; Name4;
....Name5
Store 2 Product 5 Name 5

I would then only use the records with something in Col D

How could your solutions be modified to accomplish this? I can get the UDF
to do the job but it requires that I group the stores by the number of
records they have and keep changing the range accordingly. Can't get the
macro to work right.
Hope this makes sense.
Thanks,
wal50

"Gord Dibben" wrote:

Thanks for the feedback.

Happy to hear you're sorted out.


Gord

On Tue, 9 Jan 2007 10:22:00 -0800, Wanna Learn
wrote:

Gord, Sorry I know what I was doing wrong. Again thanks this is a time saver
and definetly a keeper

"Gord Dibben" wrote:

You can use a macro or a User Defined Function.

Macro................

Sub ConCat_Cells()
Dim x As Range
Dim y As Range
Dim z As Range
Dim w As String
Dim sbuf As String
On Error GoTo endit
w = InputBox("Enter the Type of De-limiter Desired")
Set z = Application.InputBox("Select Destination Cell", _
"Destination Cell", , , , , , 8)
Application.SendKeys "+{F8}"
Set x = Application.InputBox("Select Cells...Contiguous or Non-Contiguous", _
"Cells Selection", , , , , , 8)
For Each y In x
If Len(y.text) 0 Then sbuf = sbuf & y.text & w
Next
z = Left(sbuf, Len(sbuf) - 1)
Exit Sub
endit:
MsgBox "Nothing Selected. Please try again."
End Sub

UDF..................

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


Gord Dibben MS Excel MVP

On Tue, 9 Jan 2007 07:11:01 -0800, Wanna Learn
wrote:

Hello I have a list of numbers (exported from another application about 300
numbers) I have to create groups of numbers in one cell and each number
separated by a comma. Some groups have 20 numbers others 5 etc. I tried the
concatenante function with the "," between numbers take too long. Is there a
faster way to do this? thanks in advance