# VBA: Split text in cells to multiple rows



## sr1111 (Dec 25, 2022)

Hi,

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

*Input:*
test.xlsxA12Company, 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.xlsxA12Company, 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.xlsxA12Company, 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.xlsxAB12Company, 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


----------



## rlv01 (Dec 25, 2022)

Splitting text is all about patterns and/or rules. What are your rules for how the text should be split to a new line?


----------



## alansidman (Dec 25, 2022)

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.


----------



## HongRu (Dec 26, 2022)

To make sure you rules to split text:
Rule 1. "///"
Rule 2. "+=+" and append "Company ID" which before "; "

How about the above rules?


----------



## HongRu (Dec 26, 2022)

try this.


```
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
```


----------



## sr1111 (Dec 26, 2022)

HongRu said:


> try this.
> 
> 
> ```
> ...


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.


----------



## HongRu (Dec 26, 2022)

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.


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

```
Set R2 = Range("C2")
```
for example, if you have a worksheet named "Result"

```
Set R2 = Worksheets("Result").Range("C2")
```


----------



## sr1111 (Dec 26, 2022)

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.


----------



## HongRu (Dec 26, 2022)

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.


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.


----------



## sr1111 (Dec 26, 2022)

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.
> ...



Input

test.xlsxAB1CompanyIDUnique ID2Company, id*; ¤¤¤Communications Media, http://www.Communications.com/Media///Company, id*;///Company, id*;1@#$%2company3Company, 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@#$%3a4id, comi;///com, id;///com, id;1@#$%4b5id, comi;id, comi;id, comi;id, comi;id, comi;///com, id;///com, id;1@#$%5c6id, comi;///===id, comi;///1@#22$%5c7 ===id, comi12;///1@#22$%6c8 ===id, comi13;1@#22$% 7c910 ===id, comi13Sheet1

*Result*

test.xlsxABC1CompanyIDResultUnique ID2Company, id*¤¤¤Communications Media, http://www.Communications.com/Media1@#$%2company3Company, id*1@#$%2company4Company, id*1@#$%2company5Company, id*1¤¤¤enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u.1@#$%3a6Company, id*1enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u.1@#$%3a7Company, id*1enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u..1@#$%3a8Company, 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@#$%3a9Company, 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@#$%3a10Company, id*1@#$%3a11Company, id*1@#$%3a12id, comi1@#$%4b13com, id1@#$%4b14com, id1@#$%4b15id, comiid, comi;id, comi;id, comi;id, comi;1@#$%5c16com, id1@#$%5c17com, id1@#$%5c18id, comi1@#22$%5c19 ===id, comi1@#22$%5c20 ===id, comi121@#22$%6c21 ===id, comi131@#22$% 7c22 ===id, comi13Result1


----------



## sr1111 (Dec 25, 2022)

Hi,

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

*Input:*
test.xlsxA12Company, 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.xlsxA12Company, 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.xlsxA12Company, 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.xlsxAB12Company, 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


----------



## HongRu (Dec 26, 2022)

Pardon me.
I can't figure out what your rules of splitting are.


----------



## sr1111 (Dec 26, 2022)

HongRu said:


> Pardon me.
> I can't figure out what your rules of splitting are.



Extra, in the Input Sheet, I have added a "Unique ID" column in B. 
Anyway, Thank you very much.


----------



## kvsrinivasamurthy (Dec 26, 2022)

Code to get output3

```
Sub Split_Text()
Dim A, M, N
Dim Lr As Long, T As Long, Ta As Long

Lr = Range("A" & Rows.Count).End(xlUp).Row
A = Sheets("Sheet1").Range("A2:A" & Lr)
With Sheets("Sheet2")
.Range("A1") = "Result"
For T = 1 To UBound(A, 1)
A(T, 1) = Replace(A(T, 1), "+=+", "///" & Left(A(T, 1), InStr(1, A(T, 1), "; ") + 2))
M = ""
M = Split(A(T, 1), "///")
    For Ta = 0 To UBound(M)
    N = ""
    N = Split(M(Ta), "; ")
    .Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Resize(1, UBound(N) + 1) = N ' Application.Transpose(M)
    Next Ta
Next T
End With
End Sub
```


----------



## rlv01 (Dec 26, 2022)

HongRu said:


> Pardon me.
> I can't figure out what your rules of splitting are.


Neither can I.


----------



## sr1111 (Dec 27, 2022)

Thank you 
kvsrinivasamurthy​
And In sheet1/column B, I have a unique ID in the respective row as well. If a unique ID is available in a row cell, how to copy that into the output? I want to see from which ID the results are appearing.


----------



## sr1111 (Dec 27, 2022)

And In sheet1/column B, I have a unique ID in the respective row as well. If a unique ID is available in a row cell, how to copy that into the output? I want to see from which ID the results are appearing.


HongRu said:


> Pardon me.
> I can't figure out what your rules of splitting are.


----------



## kvsrinivasamurthy (Dec 27, 2022)

If unique ID is available is it to be copied all the split rows . Pl paste a image or mini sheet showing how is data and expected result.


----------



## sr1111 (Jan 6, 2023)

kvsrinivasamurthy said:


> If unique ID is available is it to be copied all the split rows . Pl paste a image or mini sheet showing how is data and expected result.



Here iit is:

*Input:*


test111.xlsxAB1CompanyIDUnique ID2  Company, id*; ¤¤¤Communications Media, http://www.Communications.com/Media///Company, id*;///   Company, id**;1@#$%2company3Company, 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  1@#                    $%3a    1@#     $%3a4id, comi;///com1, id;///com22, id;1@#$%4b 1@#$%4b5id, comi;///===id, comi;///1@#22$%6cSheet1

*Output:*
test111.xlsxABC1ResultotherUnique ID2Company, id¤¤¤Communications Media, http://www.Communications.com/Media1@#$%2company3Company, id*1@#$%2company4Company, id**1@#$%2company5Company, 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  1@#                    $%3a    1@#     $%3a6Company, id*1enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u.1@#     $%3a  1@#                    $%3a    1@#     $%3a7Company, id*1enter fr gic an, ght cer tit, reo th and ce ersit, and, p0 99, 9A. c e:9k@o.u..1@#     $%3a  1@#                    $%3a    1@#     $%3a8Company, 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.u1@#     $%3a  1@#                    $%3a    1@#     $%3a9Company, 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  1@#                    $%3a    1@#     $%3a10Company, Name 33T1@#     $%3a  1@#                    $%3a    1@#     $%3a11Company, Name 33T1@#     $%3a  1@#                    $%3a    1@#     $%3a12Company, id*1@#     $%3a  1@#                    $%3a    1@#     $%3a13Company, id*1@#     $%3a  1@#                    $%3a    1@#     $%3a14id, comi1@#$%4b 1@#$%4b15com1, id1@#$%4b 1@#$%4b16com22, id1@#$%4b 1@#$%4b17id, comi1@#22$%6c18===id, comi1@#22$%6cSheet2


----------



## kvsrinivasamurthy (Saturday at 4:39 AM)

There is some problem with A3 value. There are only # numbers of "Company, Name 33T". Expected result shows $ numbers and "///" are missing.
Code:

```
Sub Split_Text()
Dim A, M
Dim Lr As Long, T As Long, Ta As Long, Ro As Long, Cnt As Long

Lr = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
A = Sheets("Sheet1").Range("A2:B" & Lr)
With Sheets("Sheet2")
.Range("A1").CurrentRegion.Clear
.Range("A1") = "Result"
For T = 1 To UBound(A, 1)
A(T, 1) = Replace(A(T, 1), "+=+", "///" & Left(A(T, 1), InStr(1, A(T, 1), "; ") + 2))
M = ""
M = Split(A(T, 1), "///")
    For Ta = 0 To UBound(M)
    If M(Ta) <> "" Then
    Cnt = InStr(1, M(Ta), ";")                         
        If Cnt > 0 Then                                
        Ro = .Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
        .Range("A" & Ro & ":C" & Ro).NumberFormat = "@"
        .Range("A" & Ro) = Trim(Left(M(Ta), Cnt - 1))  
        .Range("B" & Ro) = Trim(Mid(M(Ta), Cnt + 1))
        .Range("C" & Ro) = A(T, 2)
        End If
    End If
    Next Ta
Next T
End With
End Sub
```


----------

