Excel 2016 reading last 3 chars of filename into cell
May 5, 2019 4:15 PM   Subscribe

I want to get the last 3 characters (these are always numbers) before the period and extension - and drop them into a cell in the same worksheet.

e.g. smith_smithtown.163.xls - the 163 is my design job number

So I just want a cell to have 163 in it. If it auto-updated that'd be awesome.
I've seen lots of solutions on web but they look very complicated as they all seem to work thru whole filename from the left (or is this the only way it works?? IDK), also very few of them can extract part of the filename.
posted by unearthed to Computers & Internet (8 answers total)
 
Are the period and extension always the same length? If so, you could take the right 7 characters, then take the left 3 of those.
posted by Huffy Puffy at 4:27 PM on May 5, 2019 [2 favorites]


Is there only one period in the string, always?

I keep this page on speed dial because I do this stuff at least weekly. =LEFT(A1,(FIND(".",A1,1)-1)) where A1 is your original string.
posted by Lyn Never at 4:30 PM on May 5, 2019


Sorry, misread. From the same site, different page:

=MID(A2, FIND(",", A2, FIND(",", A2)+1)+1,256) is their base example. For you, you'd do:

=MID(A2,FIND(".",A2,FIND(".",A2)+1)-3,3)

^ I just tested this one and it works. And this way it won't matter if your extension is xlsx or some other not-3-digits.
posted by Lyn Never at 4:41 PM on May 5, 2019 [2 favorites]


An alternate solution in 2016 is to use the "Text to columns" function with "." as your delimiter. It's not as effortless as a formula because it won't change on the fly, but it is really powerful if you're stuck in a variable situation where you don't know how long that number's going to be.
posted by Lyn Never at 4:43 PM on May 5, 2019 [3 favorites]


If it's always that format the simplest construction is (I think?)

=LEFT(RIGHT(A1,7),3)

which means, take the rightmost 7 characters from your text (A1), and then take the leftmost 3.
posted by Just this guy, y'know at 4:44 PM on May 5, 2019


Which is what Huffy Puffy said, on preview.

The risk is that if you end up with a .xlsx or fewer characters or what have you, then you'll get a wrong answer.
Lyn Never's is more robust.
posted by Just this guy, y'know at 4:51 PM on May 5, 2019


I'd go with:


=MID(CELL("filename",A1),SEARCH(".",CELL("filename",A1),SEARCH("[",CELL("filename",A1),1))-3,3)


...which will determine the filename automatically.

(though will also break if there are other full-stops in the filename)
posted by pompomtom at 5:14 PM on May 5, 2019 [1 favorite]


Thanks everyone. Also a good lesson for me in LEFT MID RIGHT which I understand just that little bit more.

At the moment I'll run with pompomtom's version. I changed pompoptom's index from -3 to +1 to get the correct portion. I've run it against different length file names and it still works.
posted by unearthed at 2:02 AM on May 6, 2019


« Older How to socialize with men? What do men do for fun?...   |   Vegetarian friendly restaurants, Juneau, Skagway... Newer »
This thread is closed to new comments.