If period "." located in string, return "File", else return "DIR"

[Jason Campbell]

Dear all,

I have been tasked with reviewing network directories and files to etsablish when the directories and files were last accessed. This being in an effort to remove redundant directories and data that is no longer being used.

I have used the command line to extract the required data using the DIR /s /ta command to list the root directory & subdirectories, showing the date and time those directories and files were last accessed.

I've then taken the data and pasted it into a spreadsheet. The returned data is all placed within one cell for each date, time, DIR marker and DIR name or file name, as per below:


[TABLE="width: 624"]
<tbody>[TR]
[TD]25/04/2016 19:42 <DIR> archive
[/TD]
[/TR]
[TR]
[TD]18/09/2015 22:59 6,915,884 ABC_BB_2015-16_v7_singles.pdf

I have managed to extract the date, time, and directory name into thier own cells or each record. However, I have noted that I need to distinguish between a directory (<DIR>) and a direcotry file, which has a .??? extension.
[TABLE="width: 656"]
<tbody>[TR]
[TD]Date Last Accessed
[/TD]
[TD]Time
[/TD]
[TD] Type
[/TD]
[TD]Directory Name
[/TD]
[/TR]
[TR]
[TD]15/12/2017
[/TD]
[TD]10:54
[/TD]
[TD] DIR
[/TD]
[TD]2014 Business Plan and Review Matrix
[/TD]
[/TR]
[TR]
[TD]25/04/2016
[/TD]
[TD]19:10
[/TD]
[TD] DIR
[/TD]
[TD]2015 Business Plan and Review Matrix
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

1. 
   
   The formula that I have used in the 'Type' field is as follows:
   =TRIM(IF(MID(B22,SEARCH("<",B22)+1,3)="DIR","DIR",IF(RIGHT(B22,4)=".","File")))
   
   This works fine at identifying the <DIR> part of the text string, however returns the #VALUE ! error when presented with a cell string as follow:
   18/09/2015 22:59 6,915,884 ABC_BB_2015-16_v7_singles.pdf
   
   Can anyone give me some pointers on how to get excel to recognise the "." and return "File" in that instance?
   
   Many thanks in advance.
   
   Jason

 

[PatOBrien198]

=IF(ISERROR(FIND(".",B22)),"DIR","File")