Text & Special Character Split

[jawaharprm]

Hi All,
I need to split Special Characters like "," & "and" text character as given in sample excel file pls help on same Thanks.

 

[jawaharprm]

Hi All, Its is working in Textsplit but its available only in 365, but need for users 2010 and 2013 excel..

 

[Peter Bartholomew]

Like you, I would use TEXTSPLIT. Can your users access PowerQuery?

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ReplacedValue = Table.ReplaceValue(Source," and ",",",Replacer.ReplaceText,{"Input"}),
    SplitColumns = Table.SplitColumn(ReplacedValue, "Input", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Input.1", "Input.2", "Input.3", "Input.4"}),
    Result = Table.TransformColumnTypes(SplitColumns,{{"Input.1", type text}, {"Input.2", type text}, {"Input.3", type text}, {"Input.4", type text}})
in
    Result

 

[jawaharprm]

Thanks for your Replay,
Power Query No Sir.

 

[bosco_yip]

This formula work for Excel 2010 and up

In B2, formula copied across right and down:

=TRIM(MID(SUBSTITUTE(", "&SUBSTITUTE(SUBSTITUTE($A2,", and",",")," and",","),", ",REPT(" ",50)),COLUMN(A1)*50,50))