I used text to column by selected Tab character as delimiter. So, if I paste data to this workbook, that will be separated in two column.

B03=08^t1174.8364.9474.6340.2381.8794x3.10.10/47.46x10/18.64.87.78.894.640x7/49.08.04.40.54x7
becomes
in Cell A -->B03=08
in Cell B --> 1174.8364.9474.6340.2381.8794x3.10.10/47.46x10/18.64.87.78.894.640x7/49.08.04.40.54x7
Everything was fine after I got this code that can separate cell (like Text to Column function) and do some other functions (change "X" to "x", etc).

Option Explicit
Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng             As Range
Dim rngC            As Range 'Single cell for loop
Dim arrstr()        As String
Dim strSplit        As String
Dim findPos         As Long

Dim strFinal        As String

Application.EnableEvents = False
Application.ScreenUpdating = False

Set rng = Intersect(Columns(1), Target)

If Not rng Is Nothing Then

    For Each rngC In Target.Cells
    
        If Not rngC.Value = "" Then
        
            arrstr = Split(rngC.Value, "/")
            
            Dim i As Long
            For i = LBound(arrstr) To UBound(arrstr)
            
                strSplit = arrstr(i)
                findPos = InStr(strSplit, "x")
                
                If findPos > 0 Then
                    strSplit = Left(strSplit, findPos - 1) & "x" & Replace(Right(strSplit, Len(strSplit) - findPos), "x", ".")
                End If
                
                strFinal = strFinal & strSplit & "/"
            
            Next i
            
            If Not strFinal = "" Then
                strFinal = Left(strFinal, Len(strFinal) - 1)
                rngC.Value = strFinal
            End If
            
        End If
        
    Next rngC
    
    If Application.CountA(rng) > 0 Then
        rng.TextToColumns DataType:=xlDelimited, Other:=True, OtherChar:="#"
    End If
    
End If

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub
But, after pasted the data above, it becomes like this
in cell A --> B03=08
in cell B --> B03=081174.8364.9474.6340.2381.8794x3.10.10/47.46x10/18.64.87.78.894.640x7/49.08.04.40.54x7
This happens if I paste data in A2, but in A1, it works properly

cats2.jpg
I'm using Excel 2007
I hope there's someone can help me
Any help would be great, thanks a lot

Regards,



Jasa