Import option without delimeter

Frequent Contributor

Hello,

How to import text file to excel which does not have any delimiter, can we still import that file with accurate columns?

Please help...???

Waiting for your reply...???

11 Replies

@Zan_Hanifee 

The Import Text Wizard has two options: Fixed Width and Delimited. If your text file doesn't have a delimiter and if the columns don't have a fixed width, there is no way to divide the data into columns accurately - you will probably have to correct the data manually.

@Zan_Hanifee 

You may use Fixed Width. In any case Excel shall know how to split on columns,

@Hans Vogelaar 

Thank you for the reply sir... Can we insert with the help of VBA code?

@Sergei Baklan 

Thank you for the reply sir... Can we insert with the help of VBA code?

@Zan_Hanifee 

It all depends on what the text file looks like. Without knowing that it's impossible to provide help.

@Hans Vogelaar 

Here it is a screenshot which i want to import text file to excel file.

Screenshot (1518).png

@Zan_Hanifee Try Power Query.

 

If your data is as consistent as your screenshot suggests, it's not so difficult. see attached.

 

@Zan_Hanifee 

Perhaps your actual file has some non-printable character as delimiter, when logic of splitting will be more reliable.

@Riny_van_Eekelen 

Thank you for giving the solution. Sir can you explain me procedure in Power Query?

Please?

@Zan_Hanifee If you haven't used PQ before, it's not so easy to explain. But PQ has options to split a column when a character follows a number (digit). That's how you can determine the end of the first column, between 5 and A. Then, the newly created 2nd column is split for every time when there is a change from lower case character to an upper case character (space doesn't count as a character). That way, every time there is a capital letter (but not after a space) PQ splits the column.

It the data, however, is NOT so consistent, it may not work.

@Riny_van_Eekelen 

Thank you so much Sir