welcome my followers to this quiz , you need to write a function to get the file extension through either a VBA function or inbuilt excel function
| FIleName | . |
| Book1.Doc | |
| Book.book1.xls | |
| book.book1.book1.xlsx | |
| book.book.book.book1.xlsm |
Hope you will post a excellent function :)
5 comments:
If the file names are listed in cell A1 and downwards, you can write this fill down this formula:
=RIGHT(A1,LEN(A1)-FIND("@",SUBSTITUTE(A1,".","@",LEN(A1)-LEN(SUBSTITUTE(A1,".","")))))
This VBA function would the same thing:
Function FileExtension(MyFileName As String)
Dim DotLocation As Integer
DotLocation = InStrRev(MyFileName, ".", -1, vbTextCompare)
FileExtension = Right(MyFileName, Len(MyFileName) - DotLocation)
End Function
Public Function getextension(filename As String) As String
For i = Len(filename) To 2 Step -1
c = Mid(filename, i, 1)
If c = "." Then
pos = i + 1
End If
Next
getextension = Mid(filename, pos, (Len(filename) + 1 - pos))
End Function
'Function to get filetitle from full path
Public Function getfiletitle(filename As String) As String
For i = Len(filename) To 2 Step -1
c = Mid(filename, i, 1)
If c = "\" Then
pos = i + 1
End If
Next
getfiletitle = Mid(filename, pos, (Len(filename) + 1 - pos))
End Function
This is My Function get the same..
=TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",LEN(A1))),LEN(A1)))
=REPLACE(RIGHT(A1,5),1,SEARCH(".",RIGHT(A1,5)),"")
is my solution.
Hi Prakash,
your formula will return an error if "A1" does not contain any value.
well nice solution
Post a Comment