CODEWITHSUNDEEP
vbaexcelsplit
Mariusz Adamczewski
Mariusz Adamczewski

Reputation: 107

Split Data in VBA

Currently I'm facing with an issue and specially I don't know how to solve that. I have got a table:
--------------------------
| d.dasd |
| dasda.dasda |
| fdsfs.fsdf |
| fdsfdsaf.fdsafasdf |
| dasda.dasdasdas |
---------------------------

The thing is that I want to delete string after the "dot". I wrote a script in VBA:

Sub SplitText()

Dim Counter As Integer
Counter = 1
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Dim Temp As String
Dim Splited As String

Do While Counter <= LastRow

    Temp = Cells(Counter, 1).Value
    Splited = Split(Temp, ".")
    MsgBox Splited

Counter = Counter + 1
Loop

End Sub

But I have got issue in 'Splited = Split(Temp, ".")
The data after the dot should be deleted.

What should I do?

Upvotes: 1

Views: 288

Answers (2)

Scott Craner
Scott Craner

Reputation: 152660

As per the comments, if all you want is the text in front of the . then only change the one line:

Sub SplitText()

Dim Counter As Integer
Counter = 1
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Dim Temp As String
Dim Splited As String

Do While Counter <= LastRow

    Temp = Cells(Counter, 1).Value
    Splited = Split(Temp, ".")(0)
    MsgBox Splited

Counter = Counter + 1
Loop

End Sub

enter image description here

Upvotes: 1

ManishChristian
ManishChristian

Reputation: 3784

Try this code: 

Sub SplitText()

    Dim Counter As Integer
    Counter = 1
    Dim i As Integer
    Dim LastRow As Long
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    Dim Temp As String
    Dim Splited As Variant  '~~> Splited will be Variant and NOT string

    Do While Counter <= LastRow

        Temp = Cells(Counter, 1).Value
        Splited = Split(Temp, ".")  

        '~~> Run a loop on array and get all the items
        For i = LBound(Splited) To UBound(Splited)
            MsgBox Splited(i)
        Next

        Counter = Counter + 1
    Loop

End Sub

Upvotes: 1

Related Questions