Results 1 to 2 of 2

Thread: List draw and Record of drawings that plot

  1. #1
    Seeker davidp's Avatar
    Join Date
    Jan 2017
    Posts
    9
    Articles
    0
    Excel Version
    2013

    List draw and Record of drawings that plot



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

    Hello,

    I am generating a list of drawings, I would also like to have a record of drawings that are plot
    I have the next question.
    In sheet (List), I have a list of drawings with its title.
    In sheet (Search) would like to write in Draw (for example 001) in title would be written automatically, according to the title of the sheet (List)
    How can I do it?
    Plan.xlsm

  2. #2
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    885
    Articles
    0
    Excel Version
    Excel 2013

    Extract numbers from text in one cell

    Quote Originally Posted by davidp View Post
    How can I do it?
    I did not fully understand you for the 'C' column. I assume that you are putting the text manually (green font in the label attached to the file).
    If you want a unique text in the 'C' column, then set this ARRAY formula (the blue text in the label attached to the file).
    Code:
    =IFERROR(INDEX(LISTADOV;MATCH(0;COUNTIF($C$5:C5;LISTADOV);0));"")
    The formula in 'B' column (Draw) is as follows: (This formula extracts the serial number from the text in the 'C' column, after the first dash)
    Code:
    =IFERROR(LEFT(RIGHT(C6;LEN(C6)-FIND("-";C6));3);"")
    The formula in 'D' column (No Sheet) is as follows: (This formula extracts the number from the text in the 'C' column, after the 'H' letters)
    Code:
    =IFERROR(SUBSTITUTE(MID(MID(C6;FIND("-";C6;FIND("-";C6)+1)+1;256);2;2);".";"";1);"")
    The formula in 'E' column (Sheet) is as follows: (This formula extracts the number from the text in the 'C' column, after the 'dot' character)
    Code:
    =IFERROR(LEFT(REPLACE(C6;1;FIND(".";C6)+0;"");FIND("_";REPLACE(C6;1;FIND(".";C6)+0;""))-1);"")
    The formula in 'F' column (No Rev) is as follows: (This formula extracts the number from the text in the 'C' column, at the end of the text or after the 'R' letters)
    Code:
    =IFERROR(MID(MID(C6;SEARCH("_";C6;1)+1;3);2;3);"")
    The formula in 'J' column (Title) is as follows: (I named 'List!B6:E105' range as "Data")
    Code:
    =IFERROR(VLOOKUP($C6;Data;4;FALSE);"")
    Note, I have taken into account that there is a possibility of two digits of each number (green font).

    Does help you?
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

Posting Permissions

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