I am having difficulty removing the .JPG extension from the file name of my imported pictures. The Pictures are actually students pictures saved with the student's ID Numbers.

Example SLT-098-00034.jpg  ,  SLT-088-11123.jpg

I am trying to extract the real ID Numbers from the File names which should be in this format  SLT/098/33322

Accomplishment So Far..

Using the Substitute function,  Substitute (text; searchstring; replacestring)  , I have been able to convert the file name from SLT-098-00034.jpg  to SLT/098/00034.jpg

Now, I want to remove the .jpg extension, but can't achieve that using the Substitute function..  Please is there a better way to achieve this?

26 minutes ago, shevyshevy said:

Now, I want to remove the .jpg extension, but can't achieve that using the Substitute function.

Sure you can. You can substitute ".jpg" with nothing.

You could also use the Left() function to remove the last 4 characters.

 

Edited May 24, 20169 yr by comment

Thanks for your reply.. Here is what I did.. I created two fields, ID1 and ID2,   On ID1, i used the Substitute function Substitute (File_Name; "-"; "/") that gave me the first result with the ".jpg" still attached

I now used the substitute function on ID2 as follows Substitute (ID1; ".JPG"; "") .. I was hoping that its gonna remove the final ".jpg" extension so that the ID Number comes out clean.. But it didnt..

As for the Left() function, guess I am really familiar with it. Please how can use it to achieve this?

22 minutes ago, shevyshevy said:

I now used the substitute function on ID2 as follows Substitute (ID1; ".JPG"; "") .. I was hoping that its gonna remove the final ".jpg" extension

Substitute() is one of the few functions that's case-sensitive, so Substitute ( "filename.jpg" ; ".JPG" ; "" ) will not do anything.

 

22 minutes ago, shevyshevy said:

I created two fields, ID1 and ID2,  

That's not necessary. You can nest multiple substitutes within a single Substitute() function call:

Substitute ( Filename ; [ "-" ; "/" ] ; [ ".jpg" ; "" ] ) 

will return "SLT/098/00034" when Filename contains "SLT-098-00034.jpg".

 

22 minutes ago, shevyshevy said:

As for the Left() function, guess I am really familiar with it. Please how can use it to achieve this?

If all your file names have the same length, then:

Left ( Substitute ( Filename ;  "-" ; "/" ) ; 13 )

would do the same thing. If they're not, then you could use:

Left ( Substitute ( Filename ;  "-" ; "/" ) ; Length ( Filename ) - 4 )

to remove the last 4 characters, no matter what the length is.

 

Edited May 25, 20169 yr by comment

Thanks a million..