+ Reply to Thread
Results 1 to 5 of 5

Extracting SOME column data into new columns

  1. #1
    Registered User
    Join Date
    04-25-2014
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2013
    Posts
    8

    Red face Extracting SOME column data into new columns

    Hello!
    Thank you for this great forum and your help!

    Using Excel 2013

    I am hoping my question is a simple one. I've done some searching
    but it is very difficult to determine how to phrase my question in a
    way that yields useful results.
    I encountered a similar problem trying to title the thread :P

    To give you an idea of the grand scheme of things, My goal is to take a log file
    containing test data (see attached image) and drop it into an excel template
    with a button that activates a macro which automatically converts text to columns, applies conditional formatting etc. to the Log data.
    excelshot.jpg

    Here is where I am having difficulty;
    as you can see in the picture, the event column contains a summary for each event that occurs. This information may simply be a "Lamp turned on" event or it could contain error codes and sub codes.

    -I need to write a function that will recognize tif there is an error code in the event column
    and if so, copy that error code value into the "error" column. If there is also a "param" value, this will need to be extracted into the parameter column.

    -Once I've extracted those values into their respective columns I would like to then have excel find those codes in another table and populate the corresponding "Description" column with the description found in the error code table. I suspect this is a job for vlookup but since I'm already posting my first question I figure I'll ask about this one as well.

    Please note that just looking for "100" in the event column will not do because various other events do occur that output 100 as a value, like "Lamp intensity 100". However, error codes will always be preceded by the word "Error " and followed by a comma. Also, the error codes range from single digit to 3 digit. What I mean by this is an error 3 is the number 3, not 003. I'm not sure if this makes it more difficult.

    Thank you for your time and expertise!

  2. #2
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Extracting SOME column data into new columns

    Hello and welcome to the forum,

    Assuming that:
    1. your data is in Sheet1, cell A1
    2. your table with the error code description is in Sheet2, cell A1,
    3. error parameter are always the last digits in the cell,

    then try this code:

    Please Login or Register  to view this content.
    PS: Next time, please post a sample workbook instead of a picture. That way, we don't have to recreate your workbook and/or make some guesses.
    GC Excel

    If this post helps, then click the star icon (*) in the bottom left-hand corner of my post to Add reputation.

  3. #3
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Extracting SOME column data into new columns

    try this version to keep only the error number (1 to 3 digits)

    Please Login or Register  to view this content.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,240

    Re: Extracting SOME column data into new columns

    Hi, welcome to the forum

    See if this will help you?

    A
    B
    C
    1
    Event Error Param
    2
    Error 100, parm 123
    100
    123
    3
    Lam int 100
    4
    error 10, xxx
    10
    5
    Error 1000, parm 12
    100
    12
    6
    parm 1234
    1234

    B2=IFERROR(--SUBSTITUTE(MID(A2,SEARCH("error",A2,1)+6,3),",",""),"")
    copied down
    C2=IFERROR(--SUBSTITUTE(MID(A2,SEARCH("parm",A2,1)+5,5),",",""),"")
    copied down

    If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,469

    Re: Extracting SOME column data into new columns

    For column D
    Please Login or Register  to view this content.
    For columnE
    Please Login or Register  to view this content.
    Attached Files Attached Files

+ 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] Help: extracting text from column if two other columns match
    By rivendale34 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-19-2013, 04:42 PM
  2. Extracting Row Information if Value appears in Columns to List under Column Heading
    By cristame in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-10-2013, 11:26 AM
  3. [SOLVED] Extracting data from multiple columns to one column without duplicates
    By blue_clouds_mountain in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 12-31-2012, 05:46 PM
  4. Extracting Data Out of 2 Columns
    By mycon73 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-13-2011, 05:36 PM
  5. [SOLVED] Extracting data from other columns
    By Polar in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-10-2005, 09:06 AM

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