Skip to main content link. Accesskey S Skip to tab navigation link. Accesskey 1 Skip to sub navigation. Accesskey 2
  • Browse Blogs
  • My Blog
  • My Updates

+Tags Get help with tags?

  • View as cloud  | list

+ Similar Blogs

photo

Lotus Nut

111 Entries |  Chris Whisonant
Updated 
RatingsRatings 23     CommentsComments 157
photo

CrashTestChix

99 Entries |  Marie L Scott
Updated 
RatingsRatings 13     CommentsComments 226
photo

Life is too s...

33 Entries |  Barbara Skedel
Updated 
RatingsRatings 3     CommentsComments 56
photo

Beyond The Ye...

247 Entries |  Peter Presnell
Updated 
RatingsRatings 13     CommentsComments 396
photo

Uh Clem's Adm...

54 Entries |  Chris Mobley
Updated 
RatingsRatings 8     CommentsComments 55

+ Archive

+ Blog Authors  

Port 1352

1 - 2 of 2
  • Previous
  • Next
  • Page   1

More fun with excel

David Brown |   | Comments (0)  |  Visits (300)
 
I'm running some server decommission reports today and needed a quick way to sort the results by directory.
 
When you have nested layers of directories, this can be a bit of a challenge because Excel evaluates text from left to right. So, when you want to extract an application's path from the filename, and that application is more than one folder deep, getting the path is not as simple as extracting everything to the left of the first backslash.
 
There are some cool methods for solving this with VBA in excel, but I wanted to create a stand alone solution.
 
Everything could be solved in a single formula- and you could/should also include "if error" checking to substitute the "#error" you'll receive if you reference an application path in the "root" of data- but, for simplicity of explanation, I've broken it down into two steps.
 
Let's assume you've run your decommission report and have opened the results in excel:
 
image  
 
 
 
 
 
 
 
Here is how you want to separate the data for easy sorting later:
image  
 
 
 
 
 
 
 
Here are the formulas for B2 and C2:
 B2 =RIGHT(A2;LEN(A2)-FIND(CHAR(1);SUBSTITUTE(A2;"\";CHAR(1);LEN(A2)-LEN(SUBSTITUTE(A2;"\";"")))))
 C2 =LEFT(A2;(LEN(A2)-LEN(B2))) 
 
The B2 formula finds the right-most backslash and substitutes everything to the left, including the backslash itself, with null.  Now you have the filename.
 
Now that you can calculate the filename's length (everything to the right of the last backslash), you can use that value to lop off that many characters from the length of your full path.  This is what the formula in C2.
 
Now you can sort the results by Directory and then Filename to ensure you have everything in tidy buckets for your replica creation requests.
 
 
 No RatingsRatings 0

Where is the "Fuller Access Adminstration" button?

David Brown |   | Comments (2)  |  Visits (350)
 
What is wrong with this picture?
image







Please, Sir, I want some more.
 No RatingsRatings 0

  • Previous
  • Next
Jump to page of 1
Feed for Blog Entries | Feed for Blog Comments
Skip to main content link. Accesskey S
IBM Lotus Connections Help Tools About