HOW i can split data !

[AhmedRady]

Hello All,
i need to split the some data like :
Ahmed // ABC.com // call // 123456789
or
Ahmed // Abc.com // Mail // abc.com issue

and i need when ever the excel find that format *.com/*.net/*.edu put that data in product column and wherever find number put it in the number column
o know i should use split but i don't know how ????

to be
like that format :
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]name[/TD]
[TD]product[/TD]
[TD]contact way[/TD]
[TD]number[/TD]
[TD]subject[/TD]
[TD]issue[/TD]
[/TR]
[TR]
[TD]AHMED[/TD]
[TD]ABC.COM[/TD]
[TD]call[/TD]
[TD]123456789[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ahmed[/TD]
[TD]abc.com[/TD]
[TD]mail[/TD]
[TD][/TD]
[TD]abc.com issue[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[AhmedRady]

kindly i need know to know how

A- i can add many rows as every time i write in Cell 3 the split data go to row 5 then 6 then 7 and so on

B-i can make 2 conditions :
1- if the cell A3 content number that number is start with "0" transfer to number column "column E"
2- if the Cell A3 content (( *.com or *.net )) that word transfer to product column "column B"

Sub SplitText1()

Const SourceRange = "A3" ' <-- Source data range, change to suit
Const DestCell = "a5" ' <-- First destination cell, change to suit

Dim a As Variant, Cell As Range, i As Long

For Each Cell In Range(SourceRange)
a = split(Cell.Value, "//")

ReDim Preserve a(0 To 6)
If Val(a(3)) = a(3) Then
a(4) = a(3)
a(3) = Empty
End If
Range(DestCell).Offset(i).Resize(, 6).Value = a
i = i + 1
Next



End Sub

 

[alansidman]

@Ahmed
FYI

Effective January 12, 2016, Internet Explorer 8 is no longer supported on any client or server version of Windows, due to new policies specifying that only the newest version of IE available for a supported version of Windows will be supported.

If you are running newer versions of Windows, it might be time to upgrade your browser.

 

[AhmedRady]

No , sir i used old version of Windows so that i can't upgrade my explorer

@Ahmed
FYI

Effective January 12, 2016, Internet Explorer 8 is no longer supported on any client or server version of Windows, due to new policies specifying that only the newest version of IE available for a supported version of Windows will be supported.

If you are running newer versions of Windows, it might be time to upgrade your browser.

 

[ZVI]

kindly i need know to know how

A- i can add many rows as every time i write in Cell 3 the split data go to row 5 then 6 then 7 and so on

B-i can make 2 conditions :
1- if the cell A3 content number that number is start with "0" transfer to number column "column E"
2- if the Cell A3 content (( *.com or *.net )) that word transfer to product column "column B"

Try this:

Sub SplitText2()
 
  Dim a As Variant, b() As String, Part As Variant, LPart As String
  Dim i As Long, j As Long, k As Long
 
  ' Find row of the destination cell
  i = Columns("A").Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByRows, _
                   SearchDirection:=xlPrevious, SearchFormat:=False).Row
  If i < 5 Then i = 5 Else i = i + 1
 
  ' Split data of A3 to array a()
  a = Split(Range("A3"), " // ")
 
  ' Prepare destination array
  ReDim b(0 To 5)
 
  ' Fill b()
  For j = 0 To UBound(a)
    Part = Trim(a(j))
    LPart = LCase(Part)
    If LPart Like "*.com" Or LPart Like "*.net" Then
      b(1) = Part ' Goes to column "B"
    ElseIf IsNumeric(LPart) And LPart Like "0*" Then
      If Not IsEmpty(b(4)) Then b(5) = b(4)
      b(4) = Part ' Goes to column "E"
    Else
      b(k) = Part
      k = k + 1
      If k = 1 Then k = 2
    End If
  Next
   
  ' Write b() to the dest range
  Range("A" & i).Resize(, 6).Value = b()
 
End Sub

Note: In post 1 the number is in D column not in E