Forum Discussion
separate columns in excel (imported from .txt file)
Hi all.
I have a a table to bring in from a .txt file lots of lines with an excerpt below.
I have tried to import data and copy paste etc and then tried to separate columns using delimiters and also number of characters but there is no consistency in the numbers in the columns, it keeps separating different rows differently (ie if i use space the title of the object on the 2nd row may have two or three words so using space as a delimiter is out, same for the number of characters and position). Any suggestions - is too much data to try and clean up by doing one of the above then trying to tidy it!
2 Replies
Maybe the delimiter is just a tab character.
Did you try opening the file from excel?
Or just open a blank workbook and try to import the file using power query, which usually autodetects the delimiter. To do so, go to Data tab, then in Get & Transform Data group of icons use From Text/CSV.
Mine are using Notepad++, enabling a monospaced font, and check the alignment.
