+ Reply to Thread
Results 1 to 4 of 4

split up RS-232 Data to different columns

  1. #1
    Registered User
    Join Date
    05-15-2021
    Location
    The Netherlands
    MS-Off Ver
    2016
    Posts
    4

    split up RS-232 Data to different columns

    Hi,

    I have a question about sorting out RS232 Data send from a device in Excel. The data is not live, but is given to me as a txt-file, normally over 20000 lines. At this moment I am using Power Query to import the file and remove the blanc lines. This is not ideal because the data is not always in the same format like shown in the txt-file attached to this question.

    I want to split the data to different columns and as far as I can see now I am only intersted in the data belonging to #B, #M, #I and #s. At this moment I am using multiple if-statements to sort out the data to different columns and I am using Power Query again to remove the blancs. After that I am using different macro's to remove the # and the letter and adding a time interval so that I can create some figures.

    In my opinion it is much quicker and easier when I can search the original data and pick the output. I can do it with VLOOKUP and LEFT, but is still has a lot of blanc spaces. What I tried but did not succeed so far is searching for one of the datasets of interest and add the output as a new cell to an existing column.

    As an example: If I search for #s, the first value of it (#s2 = 2) is the first value of column #s. When I find another #s, that value will be the next value in the column #s.

    I hope this question is clear enough to understand and to answer it and I am looking forward to the reactions.

    Thanks in advance,
    Chris
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: split up RS-232 Data to different columns

    See attached

    in D3


    =IFERROR(INDEX($B$3:$B$47,AGGREGATE(15,6,1/(LEFT($B$3:$B$47,2)=D$1)*(ROW($A$3:$A$47)-ROW($A$2)),ROWS($1:1))),"")
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    05-15-2021
    Location
    The Netherlands
    MS-Off Ver
    2016
    Posts
    4

    Re: split up RS-232 Data to different columns

    John,

    Thank you very much for your quick response. It is working altough I am still understanding it exactly to use this type of solution for other problems that defenitely will come.

    Thans again,
    Chris

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: split up RS-232 Data to different columns

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] 2 columns data split to more columns based on groups in 2nd column
    By vendam in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-09-2019, 05:43 AM
  2. [SOLVED] Split data from 1 column to 3 columns
    By PaulS57 in forum Excel General
    Replies: 5
    Last Post: 06-06-2019, 04:39 PM
  3. Split data into columns
    By SonDur in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-18-2014, 04:12 PM
  4. [SOLVED] Split Data in Columns
    By naveenmarapaka in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-05-2013, 06:54 AM
  5. Split uneven data from a single columns to multiple columns
    By pfoz in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-13-2013, 08:24 AM
  6. Data split in 2-3 columns
    By mohan.r1980 in forum Excel General
    Replies: 6
    Last Post: 04-14-2012, 09:45 AM
  7. Split cell data into two columns
    By floribunda in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-01-2009, 10:16 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1