Extract numbers from text in one cell
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?