Excel Strip out a string

In Excel, when trying to remove a string of charactes, I regularly use the fomula below. When manipulating data from AD, this proves to be very useful, so in the example below, all data is removed before a semi colon within a string:

RIGHT(F433,LEN(F433) - FIND(";",F433))

AD Last Logon time

OK, I have taken this script off someone else's site, but that's because it's so useful.

To run the script save it as .vbs somerwhere on a DC adn run this command:

cscript script.vbs > d:\temp\output.txt

The script details are below:

Option Explicit

Dim objRootDSE, adoConnection, adoCommand, strQuery
Dim adoRecordset, strDNSDomain, objShell, lngBiasKey
Dim lngBias, k, strDN, dtmDate, objDate
Dim strBase, strFilter, strAttributes, lngHigh, lngLow

' Obtain local Time Zone bias from machine registry.
' This bias changes with Daylight Savings Time.
Set objShell = CreateObject("Wscript.Shell")
lngBiasKey = objShell.RegRead("HKLM\System\CurrentControlSet\Control\" _
& "TimeZoneInformation\ActiveTimeBias")
If (UCase(TypeName(lngBiasKey)) = "LONG") Then
lngBias = lngBiasKey
ElseIf (UCase(TypeName(lngBiasKey)) = "VARIANT()") Then
lngBias = 0
For k = 0 To UBound(lngBiasKey)
lngBias = lngBias + (lngBiasKey(k) * 256^k)
Next
End If
Set objShell = Nothing

' Determine DNS domain from RootDSE object.
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")
Set objRootDSE = Nothing

' Use ADO to search Active Directory.
Set adoCommand = CreateObject("ADODB.Command")
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Provider = "ADsDSOObject"
adoConnection.Open "Active Directory Provider"
adoCommand.ActiveConnection = adoConnection

' Search entire domain.
strBase = ""

' Filter on all user objects.
strFilter = "(&(objectCategory=person)(objectClass=user))"

' Comma delimited list of attribute values to retrieve.
strAttributes = "distinguishedName,lastLogonTimeStamp"

' Construct the LDAP syntax query.
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"

' Run the query.
adoCommand.CommandText = strQuery
adoCommand.Properties("Page Size") = 100
adoCommand.Properties("Timeout") = 60
adoCommand.Properties("Cache Results") = False
Set adoRecordset = adoCommand.Execute

' Enumerate resulting recordset.
Do Until adoRecordset.EOF
' Retrieve attribute values for the user.
strDN = adoRecordset.Fields("distinguishedName").Value
' Convert Integer8 value to date/time in current time zone.
On Error Resume Next
Set objDate = adoRecordset.Fields("lastLogonTimeStamp").Value
If (Err.Number <> 0) Then
On Error GoTo 0
dtmDate = #1/1/1601#
Else
On Error GoTo 0
lngHigh = objDate.HighPart
lngLow = objDate.LowPart
If (lngLow < 0) Then
lngHigh = lngHigh + 1
End If
If (lngHigh = 0) And (lngLow = 0) Then
dtmDate = #1/1/1601#
Else
dtmDate = #1/1/1601# + (((lngHigh * (2 ^ 32)) _
+ lngLow)/600000000 - lngBias)/1440
End If
End If
' Display values for the user.
If (dtmDate = #1/1/1601#) Then
Wscript.Echo strDN & ";Never"
Else
Wscript.Echo strDN & ";" & dtmDate
End If
adoRecordset.MoveNext
Loop

' Clean up.
adoRecordset.Close
adoConnection.Close
Set adoConnection = Nothing
Set adoCommand = Nothing
Set adoRecordset = Nothing
Set objDate = Nothing

Excel Strip the first characters from a cell

I always forget this:

=RIGHT(A1,LEN(A1)-2)

In the above example 2 is the number of cells to strip off.

Output AD Information

Server 2003 and 2008 have a tool called CSVDE - this allows for importing and exporting of AD information to and from csv files.

To output files, the syntax below can be used:

csvde -d "OU=spain,OU=Users,OU=MYOffice,DC=My,DC=local" -f x:\output\spain.csv -r objectClass=user

Watch Sport events online

Football, Cricket, Horse Racing.. it's all here and free!

http://www.justin.tv/directory/sports

Resolve Vista Scan and Fix

If you keep getting the scan and fix dialogue with every USB device that you have, then when inserting the device, click continue and do nothing and then do the following:

At the command prompt type the following command.

chkdsk (device drive letter): /f

Accidently moved unfiled notes in OneNote

This has happened to me a few times, but I've found the solution, see here:


Close OneNote and find the UnfiledNotes .one file on your hard drive
with Windows Explorer (the setting in Options gives you the exact path
and filename). Then move it where you want it to be. Reopen OneNote and
adjust the location in Options to point to the file again.

Word Tables with Rounded Corners

How is this done??

Still looking for an answer