extract data separated by dots from a single cell-CodePudding

I am new in excel I need some help

The cells contains different length of data, I try to use text to column it does not work because of the amount of dots. How can I use vba to populate each text or number in a separated cells by ignoring the number of dots than delete the line anywhere is there an empty cell in column A and B.

Data exemple:

CodePudding user response：

If the "." (dot) is the element to be stripped from strings in cells (eg no floating point numbers, nor "." is an important mark), you can use this code including deleting entire lines.

The code loops through the specified range (oRng) and when it finds ".." it will replace it with ".". Then, when no more ".." is found, indicating that the replacement job has completed, generating an error (caught), it proceeds to delete the blank rows from the blank cells in column "A".

Option Explicit

Sub fnCleanAndSplit()
    Dim oRng As Excel.Range
    Dim oCell As Excel.Range
    Dim fDone As Boolean

    Set oRng = ThisWorkbook.Sheets(1).Range("A1:A7")

    Do
        For Each oCell In oRng.Cells
            oCell.Value = VBA.Replace(oCell.Value, "..", ".")
        Next
        On Error GoTo lblDone
        fDone = oRng.Find("..") = ""
        On Error GoTo 0
    Loop Until fDone
    
lblDone:
    oRng.TextToColumns Destination:=oRng.Cells(1), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, Other:=True, OtherChar _
        :=".", TrailingMinusNumbers:=True
    oRng.SpecialCells(xlCellTypeBlanks).Select
    oRng.Parent.Activate 'just in case it is not activated
    Selection.EntireRow.Delete
    
End Sub