turning a one column txt file into usable table

[michgork]

hi,

i have this data

Column1
textstring that specifies if something is new
LOCATION NAME ADR POST TLF. DATE SEGMENT
locationdata name Street,nr 7845-cityname tlfnr YYYYYMMDD SEGMENTNAME
textstring that specifies if something is new
LOCATION NAME ADR POST TLF. DATE SEGMENT
locationdata name Street,nr 7845-cityname tlfnr YYYYYMMDD SEGMENTNAME
locationdata name Street,nr 7845-cityname tlfnr YYYYYMMDD SEGMENTNAME
locationdata name Street,nr 7845-cityname tlfnr YYYYYMMDD SEGMENTNAME
locationdata name Street,nr 7845-cityname tlfnr YYYYYMMDD SEGMENTNAME
locationdata name Street,nr 7845-cityname tlfnr YYYYYMMDD SEGMENTNAME	11
locationdata name Street,nr 7845-cityname tlfnr YYYYYMMDD SEGMENTNAME
locationdata name Street,nr 7845-cityname tlfnr YYYYYMMDD SEGMENTNAME
locationdata name Street,nr 7845-cityname tlfnr YYYYYMMDD SEGMENTNAME
locationdata name Street,nr 7845-cityname tlfnr YYYYYMMDD SEGMENTNAME
locationdata name Street,nr 7845-cityname tlfnr YYYYYMMDD SEGMENTNAME
textstring that specifies if something is shutting down
LOCATION NAME ADR POST TLF. DATE SEGMENT
locationdata name Street,nr 7845-cityname tlfnr YYYYYMMDD SEGMENTNAME
locationdata name Street,nr 7845-cityname tlfnr YYYYYMMDD SEGMENTNAME
locationdata name Street,nr 7845-cityname tlfnr YYYYYMMDD SEGMENTNAME
locationdata name Street,nr 7845-cityname tlfnr YYYYYMMDD SEGMENTNAME
locationdata name Street,nr 7845-cityname tlfnr YYYYYMMDD SEGMENTNAME
locationdata name Street,nr 7845-cityname tlfnr YYYYYMMDD SEGMENTNAME
textstring that specifies if something is new
LOCATION NAME ADR POST TLF. DATE SEGMENT
locationdata name Street,nr 7845-cityname tlfnr YYYYYMMDD SEGMENTNAME

I would like to turn it into this

Location	Name	ADR	POST	TLF	DATE	SEGMENT	status
xxxx	xxxx	xxx	xxxx	xxxx	xxxxx	xxxx	new
xxxx	xxxx	xxx	xxxx	xxxx	xxxxx	xxxx	closed

i'm lost.. help?

 

[pinarello]

Hello,

it would be better if you would upload your test data here as an Excel file or as a text file, and it would be even better if the demo data would not all have the same length!!!

 

[michgork]

Hello,

it would be better if you would upload your test data here as an Excel file or as a text file, and it would be even better if the demo data would not all have the same length!!!

I managed to untangle the various headings and split up the column with data into smaller bits, all the "location name adr" etc i managed to turn into column headers but ended up not needing them so i completely removed them.

First i made a calculated column to be equal to column1 if text contains "new" or "closed" otherwise null, then i filled down that column so each entry beneath them got either the new or closed value.
Replaced the new or close value in column1 with null
Then i split column1 into a ton of columns, it was not comma separated or tab separated but space separated...
so from there it was more basic stuff..

sorry for the inconvinience