Split comma separated text into multiple columns

lricher

New Member
Joined
Apr 14, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hi All,
i have a VBA macro that allows me to extract ";" separated text into multiple rows. however, i reach the bottom of the excel sheet, ie row# 1048576 and, as a result, i get an error from excel.
how can i encapsulate my macro so that when i reach row# 1048000 the split continues on the ext column (and so on) until i reach the end of the data needed to be split?
my macro is as follows:

VBA Code:
Sub TestSplit_V1()
'
Dim i, n, lastrow As Long
Dim j As Integer
Dim Chain As String
Dim Table() As String

lastrow = Cells(Rows.Count, "A").End(xlUp).Row 'identify last row value

n = 1

    For i = 1 To lastrow
    Chain = Cells(i, 2).Value 'Select data to split from 2nd column
    Table() = Split(Chaine, ";") 'Split based on ";"
    For j = 0 To UBound(Table)
        Cells(n, 3).Value = Table(j) 'Writing data on 3rd column
        n = n + 1
    Next 'Next "i"

Next 'Next "n"
    
End Sub

Now i know i'm supposed to add another integer or Long value such as "h" for instance, but there is also a need to go back to the starting row.
i suspect something like Cells(n,h).value and n needs to go back to the value of n = 1 but not sure how to do it.

I also know there is a need to deal with the following:
  • Run macro as long as n < lastrow
  • Change column when i = 1048000 then give i the value of i = 1
Any help out there?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,507
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub lricher()
   Dim i As Long, r As Long, c As Long
   Dim Cl As Range
   Dim Sp As Variant
   
   c = 3
   For Each Cl In Range("B1", Range("B" & Rows.Count).End(xlUp))
      Sp = Split(Cl.Value, ";") 'Split based on ";"
      For i = 0 To UBound(Sp)
         If r = 1040001 Then
            r = 1
            c = c + 1
         Else
            r = r + 1
         End If
         Cells(r, c).Value = Sp(i) 'Writing data on 3rd column
      Next i
   Next Cl
End Sub
 
Solution

lricher

New Member
Joined
Apr 14, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
@Fluff
This worked wonders!!!
Thanks ever so much.
I guess this is when i should say or wonder... "why have i not seeked help sonner ?! "
Thanks again.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,507
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,141,571
Messages
5,707,151
Members
421,494
Latest member
Chiggi_897

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top