split column a data with delimiter

[Mallesh23]

Hi Team,

I have data in column A, and I want to split column a with delimiter _
Data is my Column and I am expecting result as below by using split function in vba. thanks in advance for your help.

Data	output
abc_pqr_xyz	abc	pqr	xyz
pqr_abc_xyz	pqr	abc	xyz
xyz_pqr_xyz	xyz	pqr	xyz
abc_pqr_xyz	abc	pqr	xyz

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.

 

[alansidman]

An alternative is to use Power Query/ Get and Transform. Mcode follows:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Data", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Data.1", "Data.2", "Data.3"})
in
    #"Split Column by Delimiter"