Extracting Required Values from one filename into separate columns

Hello guys.

I have a hundred list of long filesname in one worksheet. Ex , 44WDS_SO#_PN#2054085-30-01_SN#112176228_OPN#1400/1500.pdf


From the filename,what i want to do is extracting only the required values such as Type , Part Number (PN) , Serial Number(SN), and Operation number (OPN). Then, those value will be separated into 4 different columns.

Certain filesname have no OPN number so i want it to leave it blank. But to those filesname which have more than one OPN number(# that have "/") , the different value will be separated into different rows. You can better understand what i mean when u refer to my attachment.


I really want my worksheet to have this feature instead of key in the values manually.Hope u can help me guys. Thanks in advance .

Try the attached.

Thanks anyway. What i expect, is halfly work now. Just need some modification to suit some condition which is

1) when the filesname have more than one OPN number (let say 3 OPN no) , the data will be extracted and separated into its consequence row below it (so there will be 3 rows of different OPN no) . Is it this condition is related to what u said, MID, LEN, Right, Left . I'm not so quite familiar with excel actually, then i will google it later.

When i refer to ur work, i have difficulty to digest part of the code. What is the puprpose of those redhiglight colour.

=IFERROR(MID(A7,SEARCH("sn",A7)+3,9),"")

.

---------- Post added at 03:28 AM ---------- Previous post was at 12:53 AM ----------

Dear Jindon,

Thanks for the code .

But how come i cant display your vba? It is equipped with password that i cant access. But i can see that it works entirely fine.


May i know how you do that?

Here's the code

Please Login or Register  to view this content.

Nahh..! It works. Thats very genius. I guess this is 2nd time u help me bro.

Thanks for helping. Thread solved.

Dear Jindon,

Sorry for the late question.

Your code works to me before. But I'm not sure why, when i plug the code into my worksheet it runs error message.

Does it because some limitations from the code that i'm not clear enough? Can u give a look at "master' worksheet.

.

try change to

Please Login or Register  to view this content.

Still cant..

i'm not sure. And it keeps highlighting error on ,.Offset(1).Resize(n).Value = b.
something wrong with my workbook maybe?

Are you running the code with the file you have posted?
And what is the ActiveSheet name when you run the code?

Copy of Folder Update.xlsmYes.. i run your code on this file, where my Activesheet is "master".

Master?

It is not the same question.
What is your desired result then?

Actually i want it to be more versatile, because most of the filesname come with different name template . But i really hope for the main function, it can capture the most important part such as Type and serial Number (SN) especially. This column can not be left blank.


For the certain filesname that have more than those above criteria,which is Part Number (PN) , and Operation Number OPN) and date, they can be included to. But if those filesname doesnt contaion this value, its okey to leave it blank. This one is optional criteria. You may refer to this,Copy of Folder Update.xlsm which i expect them to be.



Sorry for later request. Thanks dude.

This is for "Master"

Please Login or Register  to view this content.

Thanks dude ! Impressively work.