How to split text

[KIM Chanthan]

Hi,

I have one doubt regarding how to split text.
I have a text like 1232_ABC_Chea Kana and I want to split it by underscore ( _ ) . First I am thinking about find function, but it could not help since there are so many underscore.
please find attached file for more detail.
Thanks,

 

[bosco_yip]

To split text by underscore " _ "

Try..................

In B2, copied across and down :

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



Regards
Bosco

 

[KIM Chanthan]

Thanks it work perfectly. However, in the formula, I have changed from 50 to len ($A2) to avoid any mistake.
Thanks again for help.

 

[AlanSidman]

The quickest and easiest way is to use Power Query/Get and Transform. Here is the Mcode and the file for review.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Source", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Source.1", "Source.2", "Source.3", "Source.4"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Source.1", Int64.Type}, {"Source.2", type text}, {"Source.3", type text}, {"Source.4", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Source.1", "Code"}, {"Source.2", "Name"}, {"Source.3", "Province"}, {"Source.4", "Branch"}})
in
    #"Renamed Columns"

 

[Marc L]

Hi,​

​

as it no needs any code neither any formula just using the Excel Basics feature Text To Columns ! The easiest & quickest way …​

​