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]
Tidak ada komentar:
Posting Komentar