+ Reply to Thread
Results 1 to 6 of 6

text to table - complicated

  1. #1
    Registered User
    Join Date
    07-06-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    3

    text to table - complicated

    Hello,

    I have a text string that is dumped into an excel table daily.

    looks like this:

    STRING
    40/36/Al/N/B11
    30/48/Fe/Y/B12
    20/36/Al/Y/old B12
    30/36/Al/Y/B12
    30/12/Fe/N/old B12
    20/48/Al/Y/MISC
    20/48/Fe/Y/MISC
    30/12/Al/Y/MISC
    30/12/Al/N/B12
    20/24/Fe/Y/MISC

    I want to be able to automatically organize the strings to look like this (separated into cells) while maintain text and number formats as appropriate and not converting the original string and not having to copy and paste and manually have to convert text to table (heading should be aligned with corresponding columns):

    type size frame insulate Color
    40 40 36 Al B11
    30 30 48 Fe B12
    20 20 36 Al old B12
    30 30 36 Al B12
    30 30 12 Fe old B12
    20 20 48 Al MISC
    20 20 48 Fe MISC
    30 30 12 Al MISC
    30 30 12 Al B12
    20 20 24 Fe MISC

    There can be 1000's of lines and some blanks so going through doing a manual parse is out of the question.

    thanks in advance

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: text to table - complicated

    text to columns using / as delimiter?
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    07-06-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: text to table - complicated

    thanks for your reply; looking forward to your suggestion.

    Currently yes, the "/" is the delimiter.

    as you know it is easy to do the text to table or do a Lookup of sorts in a portion of the string manually. I need something automatic.

  4. #4
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: text to table - complicated

    I think you misunderstood Martin's answer.

    Text to Columns is an automated tool in Excel that splits cells contents into columns, you should use this and tell it to split the string using "/"

  5. #5
    Registered User
    Join Date
    07-06-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: text to table - complicated

    Quote Originally Posted by Kyle123 View Post
    I think you misunderstood Martin's answer.

    Text to Columns is an automated tool in Excel that splits cells contents into columns, you should use this and tell it to split the string using "/"
    +++++

    Didn't misunderstand; the T to C tool it's not automated in that the delimiter needs to be selected and the the tool run.

    I want something that will automatically recognize the "/" as the delimiter, MAINTAIN the original string (not convert it) and turn it into the format I provided.

    "I want to be able to automatically organize the strings to look like this (separated into cells) while maintain text and number formats as appropriate and not converting the original string and not having to copy and paste and manually have to convert text to table (heading should be aligned with corresponding columns)..."

    thanks

  6. #6
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: text to table - complicated

    well , your slave seems to have died.
    so your question really is
    please please pretty please cold someone automate this for me using your obviously vast knowledge of vba?
    have you actually tried recording a macro to do this?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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