Rename a jpeg

[AndyDevine1]

hi there
I've receive multiple jpegs from a client each day, each of which I need to rename

Example of the file names I receive are:
UK_Name_Size1.jpeg
UK_Name_Size2.jpeg
UK_Name_Size3.jpeg


I need to do the following
1) Rename the Size part of the string to some alternative text (I was thinking of setting up a table on a separate sheet containing eg Size 1 in cell A1 and AdSize1 in B1, Size 2 in cell A2 and AdSize2 in B2 etc and then indexing against this
2) rename each file to include the new text plus additional information...in other words to turn....
UK_Name_Size1.jpeg into eg 30092014_ UK_Name_AdSize1.jpeg
UK_Name_Size2.jpeg into eg 30092014_ UK_Name_AdSize2.jpeg
UK_Name_Size3.jpeg into eg 30092014_ UK_Name_AdSize3.jpeg


The date could live in eg cell A1.

If you can supply the code to do this it would be very much appreciated

Hope Ive been clear

Many thanks for reading

Andy

 

[Steph77]

This might help:

List Files in a Folder and Rename them – Batch Renamer | Struggling To Excel

 

[AndyDevine1]

Hi

I've found the code for a batch renamer by searching the message board

However my request appears to be more complicated than simply that...the new file name that needs to be created requires adding a character string from a cell into the file name

Are you able to supply the actual code I need?

Thanks

 

[Steph77]

That's one of the things that batch renamer does - you can supply it with text strings to add to the start or end or to replace any string from the middle with another string.

If you want the cell value you're referencing to change for each file then that's a different request, but as it stands if you simply copy/paste A1 + an underscore into the cell the batch renamer is to append to the start (prefix) and then replace Size with AdSize in every file name you will get the results you asked for in your opening post from that batch file.

If you do need something more complicated then you need to specify exactly what you're trying to do in a little more detail.

 

[AndyDevine1]

Hi Steph
I really appreciate your help. On reflection, I'd like to do the following...if can help with parts 1 and 3 it would be very much appreciated

Part 1: In excel, on sheet 1 form a list of the jpeg names in a specific folder in cells A2 (downwards). In cells B2, show the last part of the string eg if character string is "UK_Name_Size1.jpeg" show Size1.

Part 2: To have a table on sheet 2 that shows in column A the short descriptions which appear in the original string eg "Size1" and their long descriptions in column B eg "Advert Size1". In sheet 1 column B I will set up a vlookup to pull in the long version

Part 3: To create a new set of file names based on a value in C1 on sheet 1 (eg 2014_10_02) and replacing the original string with the new one.

In other words, change the original jpegs which read eg "UK_Name_Size1.jpeg" into "eg "2014_10_02_ UK_Name_Advert Size1.jpeg", "UK_Name_Size2.jpeg" into "eg "2014_10_02_ UK_Name_Advert Size2.jpeg"

Hope you can help?

Many thanks

 

[Steph77]

OK the easiest way to do this (using that batch renamer) is as follows:

1) Import the files from the folder you store them in. It will then give you a full list of all of the files in that folder. Copy the file name list to another sheet from A2 downwards. In B2 downwards put this formula:

=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,"_","@",LEN(A2)-LEN(SUBSTITUTE(A2,"_","")))))

This should give you the string without the .jpg tag after the last _ in the filename

2) You know how to do this bit

3) Assuming the VLOOKUP is in column C2 down, then in column D2 down use this formula:

=CONCATENATE(SUBSTITUTE(A2,RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,"_","@",LEN(A2)-LEN(SUBSTITUTE(A2,"_","")))))),C2)

Finally, copy that new column (copy/paste values) into the "New Name" column on the batch file processor.

-------

Final note - sorry if you were looking for VBA to do this automatically, I'm not that good at VBA to be able to write it. Short of someone better at VBA than me providing a more automated answer that's going to be about the easiest way to do it though. The formulas in the other sheet will stay the same for all batches though so you can just keep reusing that second file and just do 2 copy/pastes per batch and run the macros already in the batch file.

Just trying to help - as a thought if you put your formula second sheet on the batch processor file and then use formulas to link the cells between the two sheets it may be possible to do this without a single copy/paste - it would all be automatic. So on your formula sheet A2 "=D16", in A3 "=D17", etc, and then to get the column D back across into the batch renamer sheet J16 = "=D2", J17 "=D3" etc etc