Set cell value using based on cell length

Question

I have this kind of product id in column D:

1942-PS-M-01
1942-PS-M-233
1942-P-M-25
1942-P-L-200

I want to set the value in column G Using the last 2/3 number in the product id value.

I tried to use formula such =MID([Product_ID],11,3) and =MID([Sample_ID],10,3), but none of this id efficient and I have to enter the formula 1 by 1 since the id is doest not have same length.

Is there any way to do so by using vba? because I have almost 20k rows of data

I have an idea like using if (len([Product_ID])<13 then =MID([Product_ID],11,3) or if (len([Product_ID])<12 then =MID([Product_ID],10,3) but doest not know how to implement this in vba

Research `RegExp` or go here -> https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops?rq=1 — alowflyingpig, Dec 03 '19 at 03:54
Try formula `=TRIM(RIGHT(SUBSTITUTE(TRIM(D2), "-", REPT(" ", 99)), 99))`. [Reference](https://www.extendoffice.com/documents/excel/4934-excel-reverse-find.html) — Ahmed AU, Dec 03 '19 at 07:46

score 1 · Accepted Answer · answered Dec 03 '19 at 15:14

Use the following formula:

=MID(D1,FIND("-",D1,FIND("-",D1,FIND("-",D1)+1)+1)+1,LEN(D1))

This will work as long as there are consistently three dashes in your product id. It looks for the position of the first dash, then the second, then the third and extracts the rest of the string after the third dash.

Set cell value using based on cell length

1 Answers1