Pages

Wednesday 7 June 2017

Removing a first and last word from an Excel text string

I keep a spreadsheet of all my journal articles, along with notes, the file name, and the full bibliographical reference. While I am generally good with ensuring that, as I download items, I load them into my spreadsheet, sometimes circumstances overwhelm me and I end up with a pile of items to catch up on.

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

No comments :

Post a Comment

Thanks for your feedback. The elves will post it shortly.