import text columns into excel

Hi I have a text file in the attached document.
I don't know how to import only the red columns in excel without the blue colored text and without the divisions " | "
at the end they must be 18 columns
I can try importing text text / columns but then
I don't know how to delete the text in blue.
max

Here at home I have Excel 2007, in the office 365 office.
I know it's possible to do it with powerquery, an explanation how to do it?
Thank you
max

The original file is txt, the attached one is docx only to highlight the colored columns

Here this.

Hi olly i not have power query

Hi !

In this case, according to your post #6 text file, attach at least a workbook for the exact result expected …

As the text attachment has many errors, it's better to attach a new one without any
and with the result expected workbook according to this new text file …

Originally Posted by max_max

the file txt in post #6 I see no errors

Just open in Excel your text file sample, well answer to the import assistant and check your columns :
you will find more than expected and one missing !

By the way your need is at very beginner level : activate the macro recorder and open the text file …
Rows to delete are those where column A is not empty, easy !

I did so:
In the fle attached 1 macro created with the recorder
Copy the txt file post # 6 paste into A1 of the attached workbook
start macro1
Now you have to delete empty and invalid lines like the blue lines of the doc post # 1 file

Before running any code, once the text pasted to the worksheet, check first around row #55,
don't you see the huge error ?‼ Two lines on the same row !

What is the date format in this text file : MDY, DMY ?
Don't you see the error for the second date column as the text separator is missing ?
As you used a workaround with the fixed width but the easy way (in order to delete the undesired rows)
with a correct file is the delimiter way … So you lose the second line on the same row !
With your way, to delete unwanted rows is to check if first column is text instead of a number …

I see no errors in line 55 after pasting the text
The date format for the 3 columns is in Italian G/M/A (D/M/Y)



Creazione Iniz.lav fine lavor
---------- -------- ----------
04/07/2016 5072016 08/07/2016


The date of the second column is in the txt file without /
the other 2 dates have /

As I wrote to open the text file in Excel - like any workbook - but you copy the text,
share at least your filled workbook instead of an empty one like in your post #15 !
I hope you test with the same text file attachment from post #6 …

The attached workbook is the same as the post # 15 file with the post # 6 txt pasted
select column A start macro1

So check row #56 of this attachment : two lines in the same row ! …

Now I've seen ...
The division into columns text is not so exact
However, the result must be like the last workbook

So since the beginning I suspect it's very not the original source text file will all the errors I've seen
as I can't imagine someone created such a data file except if it's an idiot ! With a correct file it's so easy !

So from your last attachment use a helper column with a formula to check if cell in column A is text so it returns TRUE
and if not text obviously it returns FALSE.
Then you sort all the range upon this helper column : all the TRUE rows to delete are at the end of the range
so it's easy to clear the block at once ! Then you clear the helper column, that's it !

O.k. I leave my macro.

A help for this?
The date format for the 3 columns is in Italian G/M/A (D/M/Y)


Creazione Iniz.lav fine lavor
---------- -------- ----------
04/07/2016 5072016 08/07/2016


The date of the second column is in the txt file without /
the other 2 dates have /

5052015 which then in Excel becomes with customized cell format gg/mm/aaa ##########
15052015 which then in Excel becomes with customized cell format gg/mm/aaa 15/05/2015

Your date issue is your bad as when you used the Macro Recorder you did not well answer to the assistant for each column format …

So the easy way is to reactivate the Recorder, open the text file by the same way when you open a workbook
- better than manually pasting the data ! - and well answer to the assistant, in particular for the columns date format
and for the decimal separator via the "Advanced" button …

I rebuilt the procedure with the recorde, but no options for choosing the date but only the format year month day

Sorry, I misread but you can try to update the FieldInfo in your recorded procedure
with the same second parameter than previous or next column, I think it's 4 …
Who is the fool who created this file ?‼

Do you mean the txt file or the macro/workbook?
All 3 columns for date are set GMA date

Yes, who is the dumb who created the source text file ?! Many errors …

So if the Excel assistant can't transform the column as date, once the undesired rows deleted (or cleared),
you may use a code or a formula …

Exactly what I'm looking for is finding a custom cell format to transform the date or a vba that adds " / " to dates

Excel mysteries.
The attached txt file is a part of the txt file of post # 5.
It's just the dates.
I don't understand why these dates in the macro text / columns then become exact in the central column without #########

Not on my side, maybe it depends on which options you choosed, if you have recorded a code, I wanna see it …

The txt file is inside the folder
C:\Users\massimo\Desktop
but mistake here

With Workbooks.Open("C:\Users\massimo\Desktop\new text.txt").Sheets(1).UsedRange

As I shared how I get the correct dates … I have just edited my previous post, try it after updating the F constant …

Ok. marcL now no error works and the dates are all correct.
But for the other txt file?

So it was your bad (wrong file path) as the open codeline is the same ‼

As Excel well converts strings to dates without "/" or "-" for 8 characters strings like "01062019" and not if "1062019"
so you just have to add a zero for 7 characters strings before the conversion …

I'm sorry, marcL, I didn't understand the last post #36

As I don't know which part you did not understand …

Hi I did this:
I redid the macro with the recorder with txt post # 6
I put text / columns in the wizard on the 3 dates
the date format column 1/3
the text format column 2
after importing into excel with the macro the 3 columns I put this format
column 1/3 custum size dates dd / mm / yyyy
column 2 custom size standard 00000000
in the end they look like this:

12/02/2018 | 02022018 | 05/14/2015
02/02/2016 | 12022018 | 05/14/2019

the dates column 2 is now without ##############

So is it fixed or … ?

We say with your help I solved almost everything.
Thank you.
One thing now the format for column 2 is custom standard 00000000
is it possible that it becomes 12/12/2012 or do you need a support column with a formula?

It depends, whatever by code or by formula …

For example for 5072016 just adding a zero like 05072016 then you can use the Excel Convert feature (or Text2Columns via VBA).

From the beginning the logic is at child level but as your text attachment is so weird with many errors
- maybe it's only a print catch file but if it was created for an export its author is brain less, at least with some mental desease ! -
I can't waste time for any workarround without a real text file …

Now I've to go to see a match, see you maybe tonite or the next day.