Split cell values based on a delimiter

[Junarkar]

Hi,

below is a sample of my cell value;
248527/248528/248529/248690/248691/248692/248693/248694/248695
I want to separate each 6 digit numbers in different cells. So the result will looks like below;

248527

248697

248529

248690

248691

248692

248693

248694

248695

The "/" delimiter will not change and the numbers will be always 6 digit.

Can any one help me please.

 

[vletm]

Junarkar
You could test one of these from page

8 Ways to Split Text by Delimiter in Excel | How To Excel

www.howtoexcel.org

 

[p45cal]

test one of these from page

and Office 365:
=TEXTSPLIT(H3,"/")

 

[AlanSidman]

and yet more option with Power Query

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", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9"})
in
    #"Split Column by Delimiter"

 

[Marc L]

Hi, or just using the easy Excel feature Text To Columns from the Data menu …​

​

 

[nageswara rao]

just use text to column feature which is found data->data tools->text to columns. that's it

 

[Junarkar]

and yet more option with Power Query

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", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9"})
in
    #"Split Column by Delimiter"

Thanks alot I tried text to columns. But I would love to use such VBA codes.

 

[Junarkar]

just use text to column feature which is found data->data tools->text to columns. that's it

That never came to my mind. Thanks alot

 

[Junarkar]

Hi, or just using the easy Excel feature Text To Columns from the Data menu …​

​

Thanks alot for the simple solution Marc.

 

[vletm]

Junarkar
About Your ...But I would love to use such VBA codes.
Then You should open Your thread in VBA Macros
instead of Ask an Excel Question