Help for file name rename [SOLVED]

[bhasoriya]

How to rename 0112021317_007_T.jpg to 0112021317_007_F.png?


In details: I have file name list in excel. I want to rename the file names. left sided 15 characters stays as it is but rename only last 5 digits.


Help me please..

 

[shrivallabha]

Hi,


If you are looking for formula then assuming you have data in column A, row 2:

=REPLACE(A2,LEN(A1)-4,5,"F.png")


And if you are looking for VBA code then perhaps like this:

[pre]

Public Sub Rename()
Dim r As Range
Const csPath  As String = "C:Temp" 'Set appropriate path here
For Each r In Range("A1:A100") 'Set range reference as appropriate
If Len(r.Value) > 0 Then
Name csPath & r.Value As _
csPath & Evaluate("=REPLACE(" & r.Address & ",LEN(" & r.Address & ")-4,5,""F.png"")")
End If
Next
End Sub

[/pre]

 

[bhasoriya]

Thanks Shrivallabha..

Thank you so much dear..

 

[bhasoriya]

Hey guys. sorry.. need more help.

want to add here if condition but stuck..

=IF(RIGHT(B3,5="N.jpg"),REPLACE(B3,LEN(B3)-4,5,"F.png"),"")


Please guide me..

 

[sgmpatnaik]

@bhasoriya


Hi


as a blind shot i did this


say your Data in B3 as Hello N.Jpg


=IFERROR(REPLACE(B3,FIND("N.jpg",B3,5),5,"F.png"),"")


the result will display as Hello F.png


if the Data in B3 as Hello P.jpg


then the result will come as blank


Hope it will solve your problem other wise please inform us


Thanks


SP

 

[shrivallabha]

You have written a formula yourself. Is it not working as required? Please provide some details of your requirement.

 

[NARAYANK991]

Hi Bhasoriya ,


If it is a formula you are looking for , try this :


=IF(RIGHT(A1,5)="N.jpg",REPLACE(A1,LEN(A1)-4,5,"F.png"),A1)


Copy this down.


Narayan

 

[bhasoriya]

Narayank991, sgmpatnaik & shrivallabha.

Thanks for responding..


all formulas are working fine, but when i am adding more criteria.. it is showing #value! error.


Have tried as: =IFERROR(REPLACE(A1,FIND("T.jpg",A1,5),5,"F.png"),"")*(IFERROR(REPLACE(A1,FIND("TB.jpg",A1,5),5,"B.png"),""))


Result should be as below:


0112021317_007_T.jpg => 0112021317_007_F.png

0112021317_007_TB.jpg => 0112021317_007_B.png


If last _T.jpg then T.jpg will convert in F.png

If last _TB.jpg then TB.jpg will convert in B.png

 

[NARAYANK991]

Hi Bhasoriya ,


You can add any number of criteria , as long as it is done correctly !


=IF(RIGHT(A1,5)="N.jpg",REPLACE(A1,LEN(A1)-4,5,"F.png"),IF(RIGHT(A1,5)="T.jpg",REPLACE(A1,LEN(A1)-4,5,"F.png"),IF(RIGHT(A1,6)="TB.jpg",REPLACE(A1,LEN(A1)-5,6,"B.png"))))


Narayan

 

[bhasoriya]

Thanks NARAYANK991..

This is exactly same as i am expecting..


Thanks a lot..