Convert .txt to csv

[Harini1100]

Hello Guys,

I'm working for US Healthcare industry, i received the .txt file from insurance company and I want to convert it into csv file. The file is looks like

20240218~HL*65*3*PT~NM1*QC*1*BAST*LILOAN****MI*YHZ7123456~TRN*2*I242583~STC*A1:20*20240227*WQ*225~REF*D9*NA~DTP*472*RD8*20240218-

Kindly help how to do it with in excel.

Thanks,
Subbu

 

[rpaulson]

Bring your data in with Power Query (Data - From Text/csv)
once its loaded and looks correct, then save as CSV

 

[Harini1100]

I done that , but the problem is all the data comes in single cell, I want it different columns

 

[Harini1100]

Here is the example image for reference.

 

[rpaulson]

you will need to go thru and set your delimiters. In PQ , right click on you column then choose split by delimiter, then define them as needed.