Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|