Sub Обновить_пути()
  thisdir = ThisWorkbook.Path ' расположение текущего файла
  Application.DisplayAlerts = False
  Set c = Cells.Find("Dropbox", LookIn:=xlFormulas)
  If Not c Is Nothing Then
     firstAddress = c.Address
     Do
        dirr = Cells(c.Row, c.Column).Formula
        If InStr(dirr, "xls") <> 0 Then
           dirr = Split(dirr, "\")
           dirr2 = ""
           For Each d In dirr
               If InStr(d, "xls") = 0 Then
                  dirr2 = dirr2 & d & "\"
               End If
           Next
           dirr2 = Replace(dirr2, "='", "")
           f = CStr(Cells(c.Row, c.Column).Formula)
           f2 = Replace(f, dirr2, thisdir & "\")
           Cells(c.Row, c.Column).Formula = f2
        End If
        Set c = Cells.FindNext(c)
        If c Is Nothing Then
           Exit Do
        End If
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
    Application.DisplayAlerts = True
End Sub

Sub Путь()
i = Application.ActiveWorkbook.Path
Range("B1") = i
End Sub

=ЛЕВСИМВ(ЯЧЕЙКА("filename"); НАЙТИ("[";ЯЧЕЙКА("filename"))-1)