Results 1 to 3 of 3

Thread: turning a one column txt file into usable table

  1. #1
    Neophyte michgork's Avatar
    Join Date
    Jul 2016
    Posts
    4
    Articles
    0
    Excel Version
    2016

    turning a one column txt file into usable table



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?

  2. #2
    Seeker pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    16
    Articles
    0
    Excel Version
    Office 365
    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!!!

  3. #3
    Neophyte michgork's Avatar
    Join Date
    Jul 2016
    Posts
    4
    Articles
    0
    Excel Version
    2016
    Quote Originally Posted by pinarello View Post
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •