Jan 25 2021 05:49 AM - edited Jan 25 2021 07:14 AM
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...???
Jan 25 2021 07:16 AM
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.
Jan 25 2021 07:24 AM
You may use Fixed Width. In any case Excel shall know how to split on columns,
Jan 25 2021 07:25 AM
Thank you for the reply sir... Can we insert with the help of VBA code?
Jan 25 2021 07:25 AM
Thank you for the reply sir... Can we insert with the help of VBA code?
Jan 25 2021 07:46 AM
It all depends on what the text file looks like. Without knowing that it's impossible to provide help.
Jan 25 2021 08:01 AM - edited Jan 25 2021 08:02 AM
Jan 25 2021 08:34 AM
@Excel Try Power Query.
If your data is as consistent as your screenshot suggests, it's not so difficult. see attached.
Jan 25 2021 08:40 AM
Perhaps your actual file has some non-printable character as delimiter, when logic of splitting will be more reliable.
Jan 25 2021 08:45 AM
Thank you for giving the solution. Sir can you explain me procedure in Power Query?
Please?
Jan 25 2021 09:00 AM
@Excel 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.
Jan 25 2021 09:05 AM
Thank you so much Sir:smiling_face_with_smiling_eyes: