Search code examples
excelexcel-formulaexcel-2010

How to format the entire column to show the string stored partially

rookie here. I have been looking for the solution to this for some time but I either don't know the keywords that search for the solution or I just don't understand how it works.

I am working with an excel spreadsheet with a few columns of data, one of which looks like this:

C:\Users\Desktop\figure\2012fig1-1.png
C:\Users\Desktop\figure\2012fig1-2.png
C:\Users\Desktop\figure\2012fig2-1.png
C:\Users\Desktop\figure\2012fig2-2.png
C:\Users\Desktop\figure\2013fig1-1.png

Now, I want the columns to just show the latter part, so that it becomes:

2012fig1-1.png
2012fig1-2.png
2012fig2-1.png
2012fig2-2.png
2013fig1-1.png

Is there a way to edit the entire column, to delete the former part, or to make the cells display the latter part? I am not sure which way works and which works the best. Would anyone be kind enough to help me out with this, please?

Solution

  • Use below formula.

    =TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",100)),100))

    If you have most recent release of Microsoft-365 then could try.

    =TEXTAFTER(A1,"\",-1)

    enter image description here