Maybe cancatenate
 

Hello I have a column with 452 entries . each entry consists of a number,
(example, 13700701) I need to add a comma after each entry then combine all
the numbers in one cell so that I can export to a business object report .
I've been doing this one at a time How can I do this faster? Help Please

Maybe cancatenate
 

Note the If Len(mstr) 255 Then Exit For


Sub createstring()
For Each c In Range("f1:f5")
mstr = mstr & "," & c
If Len(mstr) 255 Then Exit For
Next c
Range("f7") = Right(mstr, Len(mstr) - 1)
End Sub


--
Don Guillett
SalesAid Software

"Wanna Learn" wrote in message
...
Hello I have a column with 452 entries . each entry consists of a
number,
(example, 13700701) I need to add a comma after each entry then combine
all
the numbers in one cell so that I can export to a business object report
.
I've been doing this one at a time How can I do this faster? Help Please

Maybe cancatenate
 

Wanna

How about 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

Usage is..........=ConCatRange(A1:A452)

The convert to values using paste special.

Or a macro which would involve no formula to convert.

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


Gord Dibben MS Excel MVP

On Mon, 11 Dec 2006 08:27:01 -0800, Wanna Learn
wrote:

Hello I have a column with 452 entries . each entry consists of a number,
(example, 13700701) I need to add a comma after each entry then combine all
the numbers in one cell so that I can export to a business object report .
I've been doing this one at a time How can I do this faster? Help Please

Maybe cancatenate
 

Don
Please clarify, I do not really understand your answer

"Don Guillett" wrote:

Note the If Len(mstr) 255 Then Exit For


Sub createstring()
For Each c In Range("f1:f5")
mstr = mstr & "," & c
If Len(mstr) 255 Then Exit For
Next c
Range("f7") = Right(mstr, Len(mstr) - 1)
End Sub


--
Don Guillett
SalesAid Software

"Wanna Learn" wrote in message
...
Hello I have a column with 452 entries . each entry consists of a
number,
(example, 13700701) I need to add a comma after each entry then combine
all
the numbers in one cell so that I can export to a business object report
.
I've been doing this one at a time How can I do this faster? Help Please

Maybe cancatenate
 

This is a macro that would look in the f1:f5 rangetake the numberadd a
commatake the next number,etc
Try it by changing the range(s) to suit your needs.

--
Don Guillett
SalesAid Software

"Wanna Learn" wrote in message
...
Don
Please clarify, I do not really understand your answer

"Don Guillett" wrote:

Note the If Len(mstr) 255 Then Exit For


Sub createstring()
For Each c In Range("f1:f5")
mstr = mstr & "," & c
If Len(mstr) 255 Then Exit For
Next c
Range("f7") = Right(mstr, Len(mstr) - 1)
End Sub


--
Don Guillett
SalesAid Software

"Wanna Learn" wrote in message
...
Hello I have a column with 452 entries . each entry consists of a
number,
(example, 13700701) I need to add a comma after each entry then combine
all
the numbers in one cell so that I can export to a business object
report
.
I've been doing this one at a time How can I do this faster? Help
Please

Maybe cancatenate
 

Don;
I tried your code and F7 came out a number, converting the F3:F5 range
to 000's (see below) - F7 should probably be in text format (right?) so
I used instead:

Range("f7") = Application.WorksheetFunction.Substitute(mstr, ",", "'",
1)

And got
137000701,138000701,237000701,737000701,937000701

Versus

1.37000701138E+44


"Don Guillett" wrote in message
:

Note the If Len(mstr) 255 Then Exit For


Sub createstring()
For Each c In Range("f1:f5")
mstr = mstr & "," & c
If Len(mstr) 255 Then Exit For
Next c
Range("f7") = Right(mstr, Len(mstr) - 1)
End Sub


--
Don Guillett
SalesAid Software

"Wanna Learn" wrote in message
...
Hello I have a column with 452 entries . each entry consists of a
number,
(example, 13700701) I need to add a comma after each entry then combine
all
the numbers in one cell so that I can export to a business object report
.
I've been doing this one at a time How can I do this faster? Help Please

Maybe cancatenate
 

When I tested f7 was formatted as general to start with as was the number
list. Worked fine

--
Don Guillett
SalesAid Software

"JMay" wrote in message
...
Don;
I tried your code and F7 came out a number, converting the F3:F5 range to
000's (see below) - F7 should probably be in text format (right?) so I
used instead:

Range("f7") = Application.WorksheetFunction.Substitute(mstr, ",", "'", 1)

And got
137000701,138000701,237000701,737000701,937000701

Versus

1.37000701138E+44


"Don Guillett" wrote in message
:

Note the If Len(mstr) 255 Then Exit For


Sub createstring()
For Each c In Range("f1:f5")
mstr = mstr & "," & c
If Len(mstr) 255 Then Exit For
Next c
Range("f7") = Right(mstr, Len(mstr) - 1)
End Sub


--
Don Guillett
SalesAid Software

"Wanna Learn" wrote in message
...
Hello I have a column with 452 entries . each entry consists of a
number,
(example, 13700701) I need to add a comma after each entry then combine
all
the numbers in one cell so that I can export to a business object
report
.
I've been doing this one at a time How can I do this faster? Help
Please

Maybe cancatenate
 

What was the output of F7? Can you display?
Sorry,, just trying to sort things out..

"Don Guillett" wrote in message
:

When I tested f7 was formatted as general to start with as was the number
list. Worked fine

--
Don Guillett
SalesAid Software

"JMay" wrote in message
...
Don;
I tried your code and F7 came out a number, converting the F3:F5 range to
000's (see below) - F7 should probably be in text format (right?) so I
used instead:

Range("f7") = Application.WorksheetFunction.Substitute(mstr, ",", "'", 1)

And got
137000701,138000701,237000701,737000701,937000701

Versus

1.37000701138E+44


"Don Guillett" wrote in message
:

Note the If Len(mstr) 255 Then Exit For


Sub createstring()
For Each c In Range("f1:f5")
mstr = mstr & "," & c
If Len(mstr) 255 Then Exit For
Next c
Range("f7") = Right(mstr, Len(mstr) - 1)
End Sub


--
Don Guillett
SalesAid Software

"Wanna Learn" wrote in message
...
Hello I have a column with 452 entries . each entry consists of a
number,
(example, 13700701) I need to add a comma after each entry then combine
all
the numbers in one cell so that I can export to a business object
report
.
I've been doing this one at a time How can I do this faster? Help
Please

Maybe cancatenate
 

1370000,1370000,1370000,1370000,


--
Don Guillett
SalesAid Software

"JMay" wrote in message
...
What was the output of F7? Can you display?
Sorry,, just trying to sort things out..

"Don Guillett" wrote in message
:

When I tested f7 was formatted as general to start with as was the number
list. Worked fine

--
Don Guillett
SalesAid Software

"JMay" wrote in message
...
Don;
I tried your code and F7 came out a number, converting the F3:F5 range
to
000's (see below) - F7 should probably be in text format (right?) so I
used instead:

Range("f7") = Application.WorksheetFunction.Substitute(mstr, ",", "'",
1)

And got
137000701,138000701,237000701,737000701,937000701

Versus

1.37000701138E+44


"Don Guillett" wrote in message
:

Note the If Len(mstr) 255 Then Exit For


Sub createstring()
For Each c In Range("f1:f5")
mstr = mstr & "," & c
If Len(mstr) 255 Then Exit For
Next c
Range("f7") = Right(mstr, Len(mstr) - 1)
End Sub


--
Don Guillett
SalesAid Software

"Wanna Learn" wrote in message
...
Hello I have a column with 452 entries . each entry consists of
a
number,
(example, 13700701) I need to add a comma after each entry then
combine
all
the numbers in one cell so that I can export to a business object
report
.
I've been doing this one at a time How can I do this faster? Help
Please

Maybe cancatenate
 

Oh well, let's drop it -- LOL
Thank you so much for your significant contribution to this
and other Excel sites. ..have been seeing you "out-here"
for the past few years. You can always take your answers
"to-the-bank".
Merry Christmas to you and your family
Jim May


"Don Guillett" wrote:

1370000,1370000,1370000,1370000,


--
Don Guillett
SalesAid Software

"JMay" wrote in message
...
What was the output of F7? Can you display?
Sorry,, just trying to sort things out..

"Don Guillett" wrote in message
:

When I tested f7 was formatted as general to start with as was the number
list. Worked fine

--
Don Guillett
SalesAid Software

"JMay" wrote in message
...
Don;
I tried your code and F7 came out a number, converting the F3:F5 range
to
000's (see below) - F7 should probably be in text format (right?) so I
used instead:

Range("f7") = Application.WorksheetFunction.Substitute(mstr, ",", "'",
1)

And got
137000701,138000701,237000701,737000701,937000701

Versus

1.37000701138E+44


"Don Guillett" wrote in message
:

Note the If Len(mstr) 255 Then Exit For


Sub createstring()
For Each c In Range("f1:f5")
mstr = mstr & "," & c
If Len(mstr) 255 Then Exit For
Next c
Range("f7") = Right(mstr, Len(mstr) - 1)
End Sub


--
Don Guillett
SalesAid Software

"Wanna Learn" wrote in message
...
Hello I have a column with 452 entries . each entry consists of
a
number,
(example, 13700701) I need to add a comma after each entry then
combine
all
the numbers in one cell so that I can export to a business object
report
.
I've been doing this one at a time How can I do this faster? Help
Please