Thursday, December 29, 2011

Text to column

Hi,
if you are finding a code such like Text to Column . it can help you


    Sub TextToColumn()
        Dim ArrList
        Dim rngCell As Range
        Dim strDelimeter    As String
        strDelimeter = Application.InputBox("Please Provide a Delimeter", "Text To Column")
        For Each rngCell In Selection
            ArrList = Split(rngCell.Value, strDelimeter)
            If WorksheetFunction.CountA(rngCell.Offset(, 1).Resize(1, UBound(ArrList) + 1)) > 1 Then
                If MsgBox("Some data can be replaced ,Do you want to Continue", vbYesNo) = vbYes Then
                    rngCell.Offset(, 1).Resize(1, UBound(ArrList) + 1).Value = ArrList
                End If
                Else
                rngCell.Offset(, 1).Resize(1, UBound(ArrList) + 1).Value = (ArrList)
                rngCell.Offset(, 1).Resize(1, UBound(ArrList) + 1).Value = rngCell.Offset(, 1).Resize(1, UBound(ArrList) + 1).Value
            End If
        Next
    End Sub

No comments: