Excel: how to remove multiple file paths from text
With data in A1
, try:
=CONCAT(FILTERXML("<t><s>"&SUBSTITUTE(A1,"/","</s><s>")&"</s></t>","//s[substring(., string-length(.) - 3) = '.jpg' or substring(., string-length(.) - 4) = '.jpg,']"))
If you run Excel 2019, you'd need to CSE this formula. If one has Excel O365, you don't need to CSE, but there is also a shorter version using LET()
:
=LET(X,FILTERXML("<t><s>"&SUBSTITUTE(A1,"/","</s><s>")&"</s></t>","//s"),CONCAT(IF(ISNUMBER(FIND(".jpg",X)),X,"")))
For those interested, a link to the Q&A mentioned in my comment below.
Give this small User Defined Function a try:
Option Explicit
Public Function NoPath(sIn As String) As String
Dim arr, i As Long, v As String, L As Long
Dim j As Long
arr = Split(sIn, ",")
For i = LBound(arr) To UBound(arr)
v = arr(i)
L = Len(v)
For j = L To 1 Step -1
If Mid(v, j, 1) = "/" Then
arr(i) = Mid(v, j + 1)
Exit For
End If
Next j
Next i
NoPath = Join(arr, ",")
End Function
User Defined Functions (UDFs) are very easy to install and use:
- ALT-F11 brings up the VBE window
- ALT-I ALT-M opens a fresh module
- paste the stuff in and close the VBE window
If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx
To remove the UDF:
- bring up the VBE window as above
- clear the code out
- close the VBE window
To use the UDF from Excel:
=NoPath(A1)
To learn more about macros in general, see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
and
http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx
and for specifics on UDFs, see:
http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx
Macros must be enabled for this to work!