VBA: Split text in cells to multiple rows

S

sr1111

New Member
Joined
Sep 2, 2022
Messages
46
Office Version
  1. 2013
  2. 2011
  3. 2010
  4. 2007
Platform
  1. Windows
Hi,

I am looking for a VBA to split the text into cells into rows.

Input:
test.xlsx
A
1
2Company, id*; ¤¤¤Communications Media, http://www.Communications.com/Media.
3Company, id*1; ¤¤¤enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u.+=+enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u.+=+enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u..///Company, Name 33T; ¤¤¤enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u.///Company, Name 33T; ¤¤¤enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u.
Sheet1


Output1:
test.xlsx
A
1
2Company, id*; ¤¤¤Communications Media, http://www.Communications.com/Media.
3Company, id*1; ¤¤¤enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u.+=+enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u.+=+enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u..
4Company, Name 33T; ¤¤¤enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u.
5Company, Name 33T; ¤¤¤enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u.
Sheet2


output2:
test.xlsx
A
1
2Company, id*; ¤¤¤Communications Media, http://www.Communications.com/Media.
3Company, id*1; ¤¤¤enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u.
4Company, id*1; enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u.
5Company, id*1; enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u..
6Company, Name 33T; ¤¤¤enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u.
7Company, Name 33T; ¤¤¤enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u.
Sheet3


output3:
test.xlsx
AB
1
2Company, id*¤¤¤Communications Media, http://www.Communications.com/Media.
3Company, id*1¤¤¤enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u.
4Company, id*1enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u.
5Company, id*1enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u..
6Company, Name 33T¤¤¤enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u.
7Company, Name 33T¤¤¤enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u.
Sheet4
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Splitting text is all about patterns and/or rules. What are your rules for how the text should be split to a new line?
 
Upvote 0
Since you are using 2013, you can apply the splitting rules in Power Query (Available for download from MS) which allows to split the the results to rows and not just columns. Post #2 will help to understand your criteria.
 
Upvote 0
To make sure you rules to split text:
Rule 1. "///"
Rule 2. "+=+" and append "Company ID" which before "; "

How about the above rules?
 
Upvote 0
try this.

VBA Code: 
Sub sbSplitCompany()
    Dim R1 As Range
    Set R1 = Range("A2") 'set the first cell to split
    
    Dim R2 As Range
    Set R2 = Range("C2") 'set the first cell to put result
    R2.Offset(-1, 0) = "CompanyID" 'set the result title
    R2.Offset(-1, 1) = "Result"
    
    Dim i 'the i cell to split
    Dim myArray1 '1st split array
    Dim myArray2 '2nd split array
    Dim e  '1st array element
    Dim e2 '2nd array element
    Dim j 'check if the 1st element of 2nd split
    Dim myCompanyID 'Company ID
    
    Do Until R1.Offset(i, 0) = ""
        myArray1 = Split(R1.Offset(i, 0), "///") '1st split
        For Each e In myArray1
            myCompanyID = Split(e, "; ")(0) 'company ID
            myArray2 = Split(e, "+=+") '2nd split
            For Each e2 In myArray2
                R2.Offset(Rows.Count - R2.Row, 0).End(xlUp).Offset(1, 0) = myCompanyID 'CompanyId to R2
                If j = 0 Then 'judge if 1st e2
                    R2.Offset(Rows.Count - R2.Row, 1).End(xlUp).Offset(1, 0) = Split(e2, "; ")(1) '1st Result of e2 to R2
                Else
                    R2.Offset(Rows.Count - R2.Row, 1).End(xlUp).Offset(1, 0) = e2 'Result to R2
                End If
                j = j + 1
            Next e2
            j = 0
        Next e
        'next cell
        i = i + 1
    Loop
End Sub
 
Upvote 0
HongRu said:
try this.

VBA Code: 
Sub sbSplitCompany()
    Dim R1 As Range
    Set R1 = Range("A2") 'set the first cell to split
  
    Dim R2 As Range
    Set R2 = Range("C2") 'set the first cell to put result
    R2.Offset(-1, 0) = "CompanyID" 'set the result title
    R2.Offset(-1, 1) = "Result"
  
    Dim i 'the i cell to split
    Dim myArray1 '1st split array
    Dim myArray2 '2nd split array
    Dim e  '1st array element
    Dim e2 '2nd array element
    Dim j 'check if the 1st element of 2nd split
    Dim myCompanyID 'Company ID
  
    Do Until R1.Offset(i, 0) = ""
        myArray1 = Split(R1.Offset(i, 0), "///") '1st split
        For Each e In myArray1
            myCompanyID = Split(e, "; ")(0) 'company ID
            myArray2 = Split(e, "+=+") '2nd split
            For Each e2 In myArray2
                R2.Offset(Rows.Count - R2.Row, 0).End(xlUp).Offset(1, 0) = myCompanyID 'CompanyId to R2
                If j = 0 Then 'judge if 1st e2
                    R2.Offset(Rows.Count - R2.Row, 1).End(xlUp).Offset(1, 0) = Split(e2, "; ")(1) '1st Result of e2 to R2
                Else
                    R2.Offset(Rows.Count - R2.Row, 1).End(xlUp).Offset(1, 0) = e2 'Result to R2
                End If
                j = j + 1
            Next e2
            j = 0
        Next e
        'next cell
        i = i + 1
    Loop
End Sub
Click to expand...
Thank you. Partially worked. can I get the result on the next page?
I found two issues (1) if there are multiple ";" between "///" then it should consider the first one only and copy up to "///". I got the data up to the second ";".
(2) Secondly if the data is like this "company-1, what;///company2, Star second;" it is showing an error.
 
Upvote 0
sr1111 said:
Thank you. Partially worked. can I get the result on the next page?
I found two issues (1) if there are multiple ";" between "///" then it should consider the first one only and copy up to "///". I got the data up to the second ";".
(2) Secondly if the data is like this "company-1, what;///company2, Star second;" it is showing an error.
Click to expand...
In my code, I just use "; " (note: there is a space behind ";") for judging "CompanyID", not splitting.
In your sample, the spilt signs seems "///" and "+=+", not "; ".
We should define clearly about the rules of splitting.


I didn't find any error while data without "///".


You can get the reult wherever you like.
Just amend the line below
VBA Code: 
Set R2 = Range("C2")
for example, if you have a worksheet named "Result"
VBA Code: 
Set R2 = Worksheets("Result").Range("C2")
 
Upvote 0
I found two issues (Otherwise I have to clean the data manually before running the VBA)
(1) if there are multiple ";" then it should consider the first one only. I got the text before the second ";" (I mean I have not got the complete text because of second or third ";").
(2) Secondly if the data is like this "company-1, what;///company2, Star second;" it is showing an error.
 
Upvote 0
sr1111 said:
I found two issues (Otherwise I have to clean the data manually before running the VBA)
(1) if there are multiple ";" then it should consider the first one only. I got the text before the second ";" (I mean I have not got the complete text because of second or third ";").
(2) Secondly if the data is like this "company-1, what;///company2, Star second;" it is showing an error.
Click to expand...
mmm...I can't catch precisely your idea.
English is not my native language.

My code runs out the result exactly as your output3.

Maybe you can provide another sample and output with more data for all kind of situations by xl2bb.
 
Upvote 0
HongRu said:
mmm...I can't catch precisely your idea.
English is not my native language.

My code runs out the result exactly as your output3.

Maybe you can provide another sample and output with more data for all kind of situations by xl2bb.
Click to expand...

Input

test.xlsx
AB
1CompanyIDUnique ID
2Company, id*; ¤¤¤Communications Media, http://www.Communications.com/Media///Company, id*;///Company, id*;1@#$%2company
3Company, id*1; ¤¤¤enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u.+=+enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u.+=+enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u..///Company, Name 33T; ¤¤¤enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u.; ¤¤¤enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u.///Company, Name 33T; Company, Name 33T;¤¤¤enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u.; ¤¤¤enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u.///Company, id*;///Company, id*1@#$%3a
4id, comi;///com, id;///com, id;1@#$%4b
5id, comi;id, comi;id, comi;id, comi;id, comi;///com, id;///com, id;1@#$%5c
6id, comi;///===id, comi;///1@#22$%5c
7 ===id, comi12;///1@#22$%6c
8 ===id, comi13;1@#22$% 7c
9
10 ===id, comi13
Sheet1


Result

test.xlsx
ABC
1CompanyIDResultUnique ID
2Company, id*¤¤¤Communications Media, http://www.Communications.com/Media1@#$%2company
3Company, id*1@#$%2company
4Company, id*1@#$%2company
5Company, id*1¤¤¤enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u.1@#$%3a
6Company, id*1enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u.1@#$%3a
7Company, id*1enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u..1@#$%3a
8Company, Name 33T¤¤¤enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u.; ¤¤¤enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u.1@#$%3a
9Company, Name 33TCompany, Name 33T;¤¤¤enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u.; ¤¤¤enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u.1@#$%3a
10Company, id*1@#$%3a
11Company, id*1@#$%3a
12id, comi1@#$%4b
13com, id1@#$%4b
14com, id1@#$%4b
15id, comiid, comi;id, comi;id, comi;id, comi;1@#$%5c
16com, id1@#$%5c
17com, id1@#$%5c
18id, comi1@#22$%5c
19 ===id, comi1@#22$%5c
20 ===id, comi121@#22$%6c
21 ===id, comi131@#22$% 7c
22 ===id, comi13
Result1
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Seperate Data In One Cell Across Multiple Cells
Replies
6
Views
2K
Alex Blakenburg
A
C
  • Question Question
All data from one cell to other rows and columns
Replies
1
Views
699
mackc557
M
R
  • Question Question
VBA for making dynamic bucketwise report from Ageing report while providing days from Form
Replies
0
Views
904
ravi123
R
P
  • Question Question
Excel-VBA: Split text in single cell into multiple rows with origin cell dependent on filter
Replies
0
Views
1K
phiman13
P
B
  • Question Question
Excel VBA error with sum formula
Replies
0
Views
2K
brightwayhr
B

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top