Splitting a rows of comma separated values into multiple lines

[gates2010]

Hi,


I have data in a csv format and multiple lines..


ex:

r1c1,r1c2,r1c3,r1c4

r2c1,r2c2,r2c3,r2c4


I am trying to use excel to split it into multiple lines and separating each row of data by an empty line...


so the above file would change to:


r1c1

r1c2

r1c3

r1c4


r2c1

r2c2

r2c3

r2c4


I want to paste the data into excel and have access convert it to the format above... is this easily doable? can you help me with the script?


Thank you.

 

[SirJB7]

Hi, gates2010!


Give a try to this code, adjusting sheets and ranges as needed:

-----

[pre]

Option Explicit

Sub SplitAndTranspo()
' constants
Const ksInputWS = "Hoja1"
Const ksInputRange = "A1"
Const ksOutputWS = "Hoja2"
Const ksOutputRange = "A1"
' declarations
Dim rngI As Range, rngO As Range
Dim lRowI As Long, iColI As Integer, lRowO As Long, iColO As Integer
Dim I As Long, J As Long, K As Integer, A As String, B As String
Dim sArray() As String
' start
Set rngI = Worksheets(ksInputWS).Range(ksInputRange)
Set rngO = Worksheets(ksOutputWS).Range(ksOutputRange)
With rngI
lRowI = .Row
iColI = .Column
End With
With rngO
lRowO = .Row
iColO = .Column
.ClearContents
End With
' process
I = lRowI
J = lRowO - 1
With rngI
Do Until .Cells(I, iColI).Value = ""
' row
A = .Cells(I, iColI).Value
' split & fill
sArray = Split(A, ",")
For K = LBound(sArray()) To UBound(sArray())
J = J + 1
rngO.Cells(J, iColO).Value = sArray(K)
Next K
' blank
J = J + 1
rngO.Cells(J, iColO).Value = ""
' cycle
I = I + 1
Loop
End With
' end
Beep
End Sub

[/pre]
-----


Regards!

 

[gates2010]

That worked perfectly, thanks again JB7..

 

[SirJB7]

Hi, gates2010!

Glad you solved it. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!

 

[motabrasil]

Hi There,

What changes in the code if you have more than one column with multiple values and others standard columns?

Example:

Column 1 Column2 Column3

PN123,PN546,PN789 ABC,XYZ,KMN Flat Cable


Thanks in advance

 

[SirJB7]

Hi, motabrasil!

No changes, note that Split function is used regardess the number of multiple values.

Regards!

 

[motabrasil]

Hi Sir

Sorry for my poor English sometimes.

I agree with you, but this macro works only if all multiple values are located in the same column. In my case I have two different columns (and hundred lines) with multiple values each. How to extent this macro to cover two columns or more?


Thanks in advance

 

[SirJB7]

Hi, motabrasil!


Não há problema, quase vizinho! (No problem, almost neighbor!)


You've got two choices:


1) Turn constants ksInputRange and ksOutputRange into parameters to be passed to the procedure and then get changed source and target to whatever needed


2) Elaborate a bit more specifying what your requirements are so as to try to provide you an alternative solution. If it's related to your other topic:

http://chandoo.org/forums/topic/copy-and-paste-a-range-based-on-the-variable-value

for which I give you a formula solution and you want to implement a VBA code solution, please upload a sample file so as to select the better option (give a look at the green sticky posts at this forums main page for uploading guidelines).


Regards!

 

[motabrasil]

Hi Sir,


I figured out how to have this working for my needs.

I adjusted my file to use the macro above and it works fine now.

Thanks for all support.

Mota

 

[SirJB7]

Hi, motabrasil!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!