Extract Text from the Filename based on certain characters in the filename

[chetanojha]

Dear Forum Members,

I have a requirement where I copy content from one excel sheet and transpose it to another excel sheet. While pasted data into one of the column example Column A - i want to paste the text from the soure filename. The rule of this text extraction (from the source filename) is below:

I receive files with three different naming convention. I want to extract string from the filename to identify the from where orders are coming to me.
a. For Filename "ORD Load ORDERS - ORD-DL-LOND-003.xls" -- (If the filename contains DL, I need to extract “ORD-DL-LOND-003” from the filename and put into column A)
b. For Filename "ORD-OL-003 (SWA 10.16).xls" -- (If the filename contains OL, I need to extract “ORD-OL-LOND-003(SWA 10.16)” from the filename and put into column A)
c. For Filename "ORD Load ORDERS - ORD-OFF-002.xls" -- (If the filename contains OFF, I need to extract “ORD-OFF-002” from the filename and put into column A)

At the moment, I am using below code to populate the Column A with the filename (Fname). But the problem is - it is pasting the whole directory path of the path.

Please Login or Register  to view this content.

Fname - is the variable which contains the whole path of the source file.

Any help would be appreciated. Thanks.

[chetanojha]

I receive files with three different naming convention. I want to extract string from the filename to identify the from where orders are coming to me.
a. For Filename "ORD Load ORDERS - ORD-DL-LOND-003.xls" -- (If the filename contains DL, I need to extract “ORD-DL-LOND-003” from the filename and put into column A)
b. For Filename "ORD-OL-003 (SWA 10.16).xls" -- (If the filename contains OL, I need to extract “ORD-OL-LOND-003(SWA 10.16)” from the filename and put into column A)
c. For Filename "ORD Load ORDERS - ORD-OFF-002.xls" -- (If the filename contains OFF, I need to extract “ORD-OFF-002” from the filename and put into column A)

[bakerman2]

To extract basename you can use this.

Please Login or Register  to view this content.

[chetanojha]

Dear Bakerman2,

Thanks for your switf reply. The above code gives me filename(without xls or xlsx extension). How do i extract the string from the filename (or base file name) based on the code ?

[bakerman2]

How about this ?

Please Login or Register  to view this content.

[chetanojha]

This is awesome. Thanks a lot bajerman2. Although I thought, due to my lack of knowledge , that this code may not work for all the three scenarios i have mentioned above ... this has solved my problem completly. Thanks a ton for this....

How about this ?

Please Login or Register  to view this content.

[chetanojha]

Can I also ask the following:

I am copying the data from one of the colum of source excel sheet and paste it to the destination excel sheet using below code:

Please Login or Register  to view this content.

Now the problem - I am having is that the format of the newly pasted row in the destination is differed from the previous row. For example, if the above code is pasting the data in row#415 .. the format of the row#415 should be same as the format of row#414 i.e. previous row.

Is there any way where I can copy the format of the previous row before pasting the data to the new row?

[bakerman2]

Untested but something like this should work.

Please Login or Register  to view this content.