Microsoft Office Forums - View Single Post - [Solved] Using portions of a filename for a caption.

gmayor · 03-07-2022, 09:52 PM

Quote:

Originally Posted by mathemagician44

I have tried this and 58 different variations trying to figure this out.

There was nothing to figure out. The function I posted provided the caption gets the text in the filename (sname) without the extension or the number at the beginning, from the examples that you provided.

You can call the function where you want to use the caption in your larger macro e.g.

Code:

Sub TestCaption()
Const fName1 As String = "1 Dog is brown. Dog is big.jpg"
Const fName2 As String = "1.2 Cat is black. Cat is little.jpg"
Const fName3 As String = "2 Pencil is a yellow. Pencils are great.jpg"
Dim sCaption As String
    sCaption = GetCaption(fName1)
    MsgBox sCaption
    sCaption = GetCaption(fName2)
    MsgBox sCaption
    sCaption = GetCaption(fName3)
    MsgBox sCaption
End Sub

Function GetCaption(sName As String)
Dim sCaption As String
Dim iPos As Integer
    If IsNumeric(Left(sName, 1)) = True Then
        iPos = InStr(1, sName, " ")
        sCaption = Mid(sName, iPos + 1)
    Else
        sCaption = sName
    End If
    sCaption = Left(sCaption, InStrRev(sCaption, "."))
    GetCaption = sCaption
End Function