Formula or Macro to Make List of Data From Lines Break

[muhammad susanto]

hi all..

how to make list of department from line break data like this:
you can use formula or vba no mater

list name extract.xlsx
	C	D	E
1	list of department		my target/desired result
2	1 . BKN Kantor Regional XII Pekanbaru2 . Perwakilan BKKBN Provinsi Riau3 . Stasiun Meteorologi Kelas I SSK II4 . BNN Provinsi Riau5 . Kantor SAR Pekanbaru6 . BPK Perwakilan Provinsi Riau7 . Balai Besar POM Pekanbaru8 . Perwakilan BPKP Provinsi Riau9 . BPS Provinsi Riau10 . Kejaksaan Tinggi Riau11 . Kanwil Kementerian Agama Provinsi Riau12 . Kanwil BPN Provinsi Riau13 . Kanwil Kementerian Hukum dan HAM Riau14 . Stasiun KIPM Pekanbaru15 . Kantor Kesehatan Pelabuhan Kelas II Pekanbaru16 . Politeknik Kesehatan Kemenkes Riau17 . BDK Pekanbaru18 . Kanwil DJP Riau19 . Kanwil DJPb Riau20 . Kanwil DJKN Riau, Sumatera Barat, dan Kepulauan Riau21 . Kanwil DJBC Riau22 . Balai Monitor Spektrum Frekuensi Radio Kelas I Pekanbaru23 . Balai Besar KSDA Riau24 . Balai Diklat Kehutanan Pekanbaru25 . BPKH XIX Pekanbaru26 . BWS Sumatera III27 . PJN Wilayah I Provinsi Riau28 . Balai Prasarana Permukiman Wilayah Riau29 . LPMP Riau30 . Balai Bahasa Riau31 . KSOP Kelas I Dumai32 . Korem 031/Wirabima33 . Lanud Roesmin Nurjadin34 . Balai Pengkajian Teknologi Pertanian Riau35 . Balai Karantina Pertanian Kelas I Pekanbaru36 . Universitas Riau37 . Panti Sosial Bina Remaja Rumbai38 . Polda Riau39 . KPU Provinsi Riau40 . RRI Pekanbaru41 . TVRI Riau42 . Pengadilan Tinggi Pekanbaru43 . Pengadilan Tinggi Agama Pekanbaru44 . PTUN Pekanbaru		BKN Kantor Regional XII Pekanbaru
3			Perwakilan BKKBN Provinsi Riau
4			Stasiun Meteorologi Kelas I SSK II
5			etc..
Sheet2

the clue is after number & mark dot like this 1. text.....or 8. text.....etc....please see the table above (bold mark).
anyone help me out thank in advance

sst

 

[alansidman]

Try this power query Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"list of department", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "list of department"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Split Column by Delimiter", "list of department", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"list of department.1", "list of department.2", "list of department.3"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Character Transition",{"list of department.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([list of department.1] <> "1 "))
in
    #"Filtered Rows"

 

[muhammad susanto]

hi alansidman, thank you so much!!