# Thread: List draw and Record of drawings that plot

1. ## 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  Reply With Quote

2. ## Extract numbers from text in one cell Originally Posted by davidp 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?  Reply With Quote

#### Posting Permissions

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