+ Reply to Thread
Results 1 to 12 of 12

Help Parsing variable length test

  1. #1
    Registered User
    Join Date
    01-11-2016
    Location
    New York
    MS-Off Ver
    2013
    Posts
    8

    Help Parsing variable length test

    I'm still pretty new to Excel, but with that said, I thought you would need a unique delimiter to parse text. I'm having trouble parsing the following text.
    I'm showing the example visually, as well as uploading a small Excel file as my example.

    This file is auto generated as an export function of the program that produces it.
    As you can see my Description Column is very busy. I would like to parse it into three separate columns

    The text in the description column is always variable. My question is, without a clear delimiter, How do I parse it?

    Thanks,
    RM
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    10-06-2008
    Location
    Norway
    MS-Off Ver
    2010
    Posts
    365

    Re: Help Parsing variable length test

    It seems every line ends with line break (char(10)). One way is to use mid combined With search.

    This should return the first line in A2:
    =MID(A2,1,SEARCH(CHAR(10),A2,1)-1)

    And this the second.
    =MID(A2,B2+1,(SEARCH(CHAR(10),A2,SEARCH(CHAR(10),A2,1)+1))-(SEARCH(CHAR(10),A2,1)))

  3. #3
    Valued Forum Contributor
    Join Date
    10-06-2008
    Location
    Norway
    MS-Off Ver
    2010
    Posts
    365

    Re: Help Parsing variable length test

    You could also substitute char(10) With "*" using the substitute command, and then use the text to column tool on the result.

  4. #4
    Registered User
    Join Date
    01-11-2016
    Location
    New York
    MS-Off Ver
    2013
    Posts
    8

    Re: Help Parsing variable length test

    Thanks for the quick reply. I'm working on using the line break to parse.
    Sorry I have seen examples that include the substitute line but not sure how to code it correctly.
    RM

  5. #5
    Registered User
    Join Date
    01-11-2016
    Location
    New York
    MS-Off Ver
    2013
    Posts
    8

    Re: Help Parsing variable length test

    estige:
    I getting a #VALUE! error when I try to parse the second or third line.
    The first part of the code works well. I get the entire first line in a new column.
    When I try to run the second statement in a new column I get the #VALUE! error.

    What am I doing wrong?
    Of course I still need the third line in a third column. I'm confused as to how to do this.
    RM

  6. #6
    Valued Forum Contributor
    Join Date
    10-06-2008
    Location
    Norway
    MS-Off Ver
    2010
    Posts
    365

    Re: Help Parsing variable length test

    The easiest way is to substitute. B2:
    =SUBSTITUTE(A2,CHAR(10),"*")

    Copy formula Down,
    Select column and copy/paste at values
    Use text to table With * as delimiter.

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Help Parsing variable length test

    This is another way. It requires a powerful amount of space "padding" to pull it off. In B2 filled down and across until you get blanks.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Dave

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Help Parsing variable length test

    I also managed to do this with Text to columns under Data.

    Select 'Delimited' then in step 2 for delimiters check "Other". The box next to it hold down the Ctrl key while hitting the letter J. You'll see a small dot. Click Finish.

  9. #9
    Registered User
    Join Date
    01-11-2016
    Location
    New York
    MS-Off Ver
    2013
    Posts
    8

    Re: Help Parsing variable length test

    Thanks to all:
    I got passed my issue and both solutions work well for me.
    I'm going to mark this thread as solved.
    Thank you for the help.
    RM

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Help Parsing variable length test

    You're welcome. Thanks for the feedback.

  11. #11
    Registered User
    Join Date
    06-15-2016
    Location
    USA
    MS-Off Ver
    2007
    Posts
    1

    Re: Help Parsing variable length test

    I am new. But i am very happy for this post.

  12. #12
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Help Parsing variable length test

    RusefSandi,

    Welcome to the forum. Glad to hear you got something out of this thread. Feedback like that is much appreciated. Thanks for taking the time to post.

+ 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. Replies: 5
    Last Post: 06-18-2015, 09:10 AM
  2. [SOLVED] Text parsing with variable length
    By dtrimble in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 06-11-2014, 03:36 PM
  3. Replies: 3
    Last Post: 11-21-2012, 03:28 PM
  4. [SOLVED] Parsing Excel Test Script with Reference Keyword List
    By cMac333i in forum Excel Programming / VBA / Macros
    Replies: 23
    Last Post: 08-02-2012, 03:23 AM
  5. Replies: 6
    Last Post: 04-14-2012, 04:07 AM
  6. [SOLVED] Parsing data of variable lengths
    By JP Romano in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-02-2011, 04:54 PM
  7. [SOLVED] String parsing with variable lenght strings
    By frosterrj in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 03-31-2006, 06:50 PM

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