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 :

name	product	contact way	number	subject	issue
AHMED	ABC.COM	call	123456789
ahmed	abc.com	mail		abc.com issue

<tbody>
</tbody>

 

[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"

v	A	B	C	D	E
1	Name	Product	Contact Way	Number	Subject
2	Ahmed	ABC.com	call		123456789
3	Ahmed	Abc.com	Mail	abc.com issue

 

[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"