Excel Tips & Tricks
I use Excel a lot. Though not in the way that most people will use it. Excel is a very powerful tool when it comes to text manipulation and working in IT I often have to wade through, process, manipulate, squeeze, expand or turn upside various huge text or csv files. So here are a few Excel tips and tricks that I have come across and thought were worth noting.
Change vertical data to horizontal data:
=OFFSET($B$3,(ROW(A1)*24-24)+(COLUMN(A1)-1),0)
Trim (remove) extra spaces from a string:
TRIM(B10)
Find Path part of a filepath in cell P2:
=MID(P2,1,(FIND("*",SUBSTITUTE(P2,"\","*",LEN(P2)-LEN(SUBSTITUTE(P2,"\",""))))-1))
Find file name only ni path in Code Text cell P2
=MID(P2,FIND("*",SUBSTITUTE(P2,"\","*",LEN(P2)-LEN(SUBSTITUTE(P2,"\",""))))+1,LEN(P2))
Replace “M:\Office” with “\\everest\data\office” – is case sensitive!!
=IF(E2="HO",SUBSTITUTE(M2,"M:\Office","\\everest\data\office"),M2)
Search for certian text within a cell Q2, in this cse search for the contents of B2:
=IF(ISNUMBER(SEARCH((B2&"\"),Q2)),"Y","N")
Convert a time in h:m:s format to decimal minutes only:
=((HOUR(D3)*60)+MINUTE(D3)+(SECOND(D3)/60))
Convert a time in Seconds to h:m:s format:
=TEXT(J4/86400,CHOOSE(MATCH(J4,{0,60,3600},1),":ss","m:ss","[h]:mm:ss"))
In: Excel · Tagged with: convert, excel, replace, search, time