Wow, nothing like answering my own question.
I did a web search on google, typing in these exact keywords "transpose numbers insert commas"
and came up with this:
http://excel.bigresource.com/Track/excel-duxIwyTY/
http://excel.bigresource.com/Column-of-data-to-one-cell-separated-by-comma-duxIwyTY.htmlI have a list of data in individual cells in one excel column thus...
Item 1
Item 2
Item 3
And I would like the data to read thus in one cell...
Item1,Item2,Item3.
Is there an excel function I can use to do this quickly and efficiently?
Sub concat()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("B2").Value = Join(Application.Transpose(Range("A2:A" & LR)), ",")
End Sub
And it works absolutely perfectly! Wow I usually don't get that lucky with web searches, maybe I will go out and play the Lotto. Anybody got a solid set of six digits for me?
Eric Lutz
________________________________
From: Pamela Zimmerman <pzimmer@eci-consulting.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Thursday, March 15, 2012 3:15 PM
Subject: RE: [MS_AccessPros] Excel, off topic, transpose and insert commas
Hi Eric,
Don't know how to do this in excel.
I would import into access and use a function something like below. I
modified this from one I recently did that returns a much shorter set of
data. Mine uses a string and works fine, since you value will be so long I
used a memo data type here instead. I should think it would work ok but I
haven't tested it.
Public Function NumStr(sTimeStamp As Variant, pID As String) As Memo
Dim ssql As String
Dim rs As Recordset
Dim curNum As String
Dim numList As Memo
Dim db As Dao.Database
ssql = "SELECT ImportTable.Number " _
& "FROM ImportTable;"
Set db = CurrentDb
Set rs = db.OpenRecordset(ssql, dbOpenDynaset)
'rs.MoveFirst
If Not rs.EOF Then
rs.MoveFirst
Do While Not rs.EOF
If rs!Number <> "" And notnull(rs!Number) Then
curNum = rs!Number
numList = numList & curNum & ","
End If
rs.MoveNext
Loop
'trim ending comma
numList = Left(siteRemList, Len(siteRemList) - 1)
End If
rs.Close
Set rs = Nothing
NumStr = Trim(numList)
End Function
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Barry White
Sent: Thursday, March 15, 2012 1:50 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Excel, off topic, transpose and insert commas
Hello all,
I have a column of Excel data that is all numbers, about 15,000 or so, one
number in each cell at present.
However, I want to turn that column into a row, where all the data can be
seen from a single cell in that row.
Example, I want to turn this:
98164754
15642829
15636430
15699463
15600864
15652140
Into this, no spaces, but a comma between each number:
98164754,15642829,15636430,15699463,15600864,15652140
Exactly how do I do this? Because, I know one simply fact, I am NOT doing
this manually.
Eric
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
Assuming Column A is your column of numbers:
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar