turning a one column txt file into usable table

michgork

New member
Joined
Jul 1, 2016
Messages
7
Reaction score
0
Points
0
Excel Version(s)
2016
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 SEGMENTNAME11
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
NameADRPOSTTLF DATESEGMENTstatus
xxxx
xxxxxxxxxxxxxxxxxxxxxxxxnew
xxxx
xxxxxxxxxxxxxxxxxxxxxxxxclosed

i'm lost.. help? :)
 
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!!!
 
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
 
Back
Top