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]

i used that method but still not match all i need

Sub splittext()

Dim textstring As String, warray() As String, counter As Integer, strg As String


textstring = Range("A3").Value
warray() = split(textstring, "//")
For counter = LBound(warray) To UBound(warray)


strg = warray(counter)
Cells(counter + 3, 1).Value = Trim(strg)
Next counter


End Sub

 

[alansidman]

Using Power Query which is part of Excel and available in Versions 2010 and later here is the Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("//", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),
    #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "Custom", each if Value.Is(Value.FromText([Column1.4]), type text) then [Column1.4] else ""),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if Value.Is(Value.FromText([Column1.4]), type number) then [Column1.4] else ""),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Column1.4"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1.1", "Name"}, {"Column1.2", "Product"}, {"Column1.3", "Contact Way"}, {"Custom", "Number"}, {"Custom.1", "Subject"}})
in
    #"Renamed Columns"

[table="class:thin_grid"]
[tr][td]v[/td]
[td="bgcolor:#ECF0F0, align:center"]A[/td]
[td="bgcolor:#ECF0F0, align:center"]B[/td]
[td="bgcolor:#ECF0F0, align:center"]C[/td]
[td="bgcolor:#ECF0F0, align:center"]D[/td]
[td="bgcolor:#ECF0F0, align:center"]E[/td]
[/tr][tr][td="bgcolor:#ECF0F0, align:center"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Name[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Product[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Contact Way[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Number[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Subject[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Ahmed [/td]
[td="bgcolor:#FFFFFF, align:CENTER"] ABC.com [/td]
[td="bgcolor:#FFFFFF, align:CENTER"] call [/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"] 123456789 [/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Ahmed [/td]
[td="bgcolor:#FFFFFF, align:CENTER"] Abc.com [/td]
[td="bgcolor:#FFFFFF, align:CENTER"] Mail [/td]
[td="bgcolor:#FFFFFF, align:CENTER"] abc.com issue [/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[/tr]
[/table]

 

[ZVI]

VBA solution:

Sub SplitText1()
 
  Const SourceRange = "A2:A3" ' <-- Source data range, change to suit
  Const DestCell = "C3"       ' <-- 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 4)
    If Val(a(3)) = a(3) Then
      a(4) = a(3)
      a(3) = Empty
    End If
    Range(DestCell).Offset(i).Resize(, 5).Value = a
    i = i + 1
  Next
 
End Sub

 

[ZVI]

According to the destination data layout in post 1 this: If Val(a(3)) = a(3) Then
should be relpaced by that: If Val(a(3)) <> a(3) Then

 

[AhmedRady]

Thanks very much for your great effort but i can't add power query, the issue is related to my operating system i can't update "explorer 8" which is should be " explorer 9" at lest to be enable run the power query on my MOS, i hope if there is another solution.

 

[AhmedRady]

i will try it now , thanks in advance

 

[AhmedRady]

it's working , thanks , but i need to be at that level which enable me to implement my ideas

VBA solution:

Sub SplitText1()
 
  Const SourceRange = "A2:A3" ' <-- Source data range, change to suit
  Const DestCell = "C3"       ' <-- 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 4)
    If Val(a(3)) = a(3) Then
      a(4) = a(3)
      a(3) = Empty
    End If
    Range(DestCell).Offset(i).Resize(, 5).Value = a
    i = i + 1
  Next
 
End Sub

 

[alansidman]

Have you even looked at the power query solution I provided you? Very simple and easy to enable.

If you don't know about PQ then look at this: https://www.poweredsolutions.co/2015/01/23/power-query/

 

[AhmedRady]

Have you even looked at the power query solution I provided you? Very simple and easy to enable.

If you don't know about PQ then look at this: https://www.poweredsolutions.co/2015/01/23/power-query/

yes i try but the issue is related to add it to my MOS ,as i mentioned before i used old version of Explorer " explorer 8" , the below error message appears "Microsoft power query for excel x64 setup wizard ended prematurely " and informed my " please make sure that you have internet explorer 9 RTM or later installed"