To import, I often use the folder DOS command "printit.txt" (see how to do this here), which is great.
I drop the contents of that export into Word, and use a find (double space) and replace (^t; tab), then cut and paste the information from Word into Excel. Once in Excel, I delete the times column, sort by date to get those which have not yet made it into my spreadsheet at the top, then I am nearly ready to add in those new items to my resources spreadsheet.
Except I have the file size in numbers firmly stuck to the beginning of each file name, which would leave me with lots manual tidying up, were it not for a couple of handy Excel functions.
To get rid of the first word, I simply key "=RIGHT(C2,LEN(C2)-FIND(" ",C2))" into the column next to the file name I want to get rid of the first word (ie, a solid text string without spaces).
=RIGHT(C2,LEN(C2)-FIND(" ",C2))
Et voilĂ ! I now have the raw file name, which I can paste out into my resources spreadsheet. Problem solved.
If, however, I wanted to remove the LAST word, I would instead use the trim function, so kindly created and supplied by ExtendExcel (2009):
=LEFT(TRIM(C2),FIND("~",SUBSTITUTE(C2," ","~",LEN(TRIM(C2))-LEN(SUBSTITUTE(TRIM(C2)," ",""))))-1)
So easy when you know how.
Sam
- Reference: ExtendExcel (2009). How to remove first / last word from text string in cell? Retrieved 9 May 2017 from https://www.extendoffice.com/documents/excel/2698-excel-remove-first-last-word.html
No comments :
Post a Comment
Thanks for your feedback. The elves will post it shortly.