Remove last 15 characters from all cells in a row...

Hello,

i am a total newbie when it comes to excel so please be gentle.

What I want to do is remove the last 15 characters from all cells within a column but keep the remaining 4. Let me explain further..

the column contains the file name of an image, so I need to keep the .jpg/.gif/.png extension but remove the last 15 characters of the file name. Also there will be some clashes with some of the rows as there will be fewer than 15 characters to start with.

I have bulk renamed all my images using the remove last 15 characters in the programme I have for this. Hence the reason i want to now copy it over to my spreadsheets.

Thanks in advance
Rob

Provide a couple of examples.

Ok.

change
file-name-of-image-fifteen-letter.jpg

to
file-name-of-image.jpg

the second on has 15 characters removed but retains the file extension .jpg

I assume if I just remove the last 15 characters it will remove the file extension too. Now i can't do a find and replace and simply remove all instances of .jpg/.gif/.png as I do not know which rows have .jpg etc There are 15,ooo rows to edit too.

I hope this helps explain it better.

Thanks
Rob

Originally Posted by Kevin UK

Hi DaddyChan

One way is the Replace function, assuming your data is in A1:

Formula:

Please Login or Register  to view this content.

Thanks Kevin,

I assume that will replace column A cell 1?
or is it all of column A?

Also how do i put that in?

As I said total newbie to excel.. actually my first time using it

Thanks
Rob

Here is a copy of the full column. I put it into column A as i can make changes and copy it over to the full spreadsheet after.

Book1.xlsx

@AZ-XL

Thank you, this looks like what i need. But not sure how to use this?

Originally Posted by AZ-XL

Here is my array formula
a little long, but hope other excel genuises will make shorter one

=LEFT(A1,MATCH(SMALL(IF((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>=65)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<=122)=0,"",ROW(INDIRECT("1:"&LEN(A1)))),15),IF((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>=65)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<=122)=0,"",ROW(INDIRECT("1:"&LEN(A1)))),0))&RIGHT(A1,4)

ctrl+shift+enter

you can look to the file too

Wanna say thank, Click Star

OK so I applied some logic to this code..

i changed all instances of A1 to A3 as in the file you have A2 set up already.

Do I just copy and paste the code to cell B3? or A3?

Thanks

Hi

I want to propose an alternative soln.

Please Login or Register  to view this content.

The above removes some of the text from the filename but the first No 4 in the formula

Ensures that a minimum 4 characters of filename are kept.

Change the 4 to whatever value you think is appropriate for you.

Thank you so much fr the help so far.

I have a cheeky question..

is there a way to have the code in one column and then to save time, have a script to increase the A1 variable by in sequential order upto a value of say 15,00?

I ask as I have 13,291 rows to edit and one at a time is going to take too long...

Thanks in advance
Rob

Hi thanks for the reply.

What is a macro? and how do i use them/it?

Thanks
Rob

Rob

Which formula are you using?

Let me have a copy and I will write a macro to do the Job for you.

Originally Posted by mehmetcik

Rob

Which formula are you using?

Let me have a copy and I will write a macro to do the Job for you.

Hi,

I am using this in the header (row 1)

Formula:

Please Login or Register  to view this content.

row 2 onwards I will use the following as it fixes any errors..

Formula:

Please Login or Register  to view this content.

I really got to get into the swing of excel.

Thanks

Hi all,

i appreciate all the help so far thank you so much.

I have just thought of a potential issue..

How do i then copy the contents of the column with the new file names in over to my spreadsheet?

here is a copy of the full spreadsheet and it it column N that needs to stay as the v_products_image but the content needs to be the edited (minus 15 characters long)

Complete Full.csv

The above URL is to the file I have on dropbox.

Thanks
Rob

Thank you so much for your help everyone.

I saved my spreadsheet as macro enabled but can not open it in VBE.

When I navigate through my computer I can see the file and it has a yellow ! . I think there was an error.

Thanks
Rob

OK I managed to get it to work.

However, although i really do appreciate the work and effort you folks have put into this for me, there is an issue.

I need it to remove the last 15 characters no matter what, but keep the extension (.jpg/.gif/.png).

An example below..

name of renamed image

scanic-raygun-80r-.jpg

same image renamed using the above code...

scanic-raygun-80r.jpg

Did you put in any special 'rules' to the code in post #24 ?

Thanks
Rob

hI all,

Thanks to the power of youtube i found an easy way to use the concatenate function.

Thank you all for your help.