Good formula for extracting text

=TRIM(MID(SUBSTITUTE(A2,[text_to_find],REPT(" ",999)),999,999))

Advertisements

Split text in Excel with a formula

Ridiculous formula for splitting text in Excel

=MID(D$1,FIND("@@@",SUBSTITUTE(CHAR(10)&D$1,CHAR(10),"@@@",ROW(A1))),FIND("@@@",SUBSTITUTE(CHAR(10)&D$1&CHAR(10),CHAR(10),"@@@",ROW(A2)))-FIND("@@@",SUBSTITUTE(CHAR(10)&D$1,CHAR(10),"@@@",ROW(A1)))-1)

Powershell – Send Mail through O365

$cred = Get-Credential
$to = "somebody@domain.com"
$from = "me@domain.com"
$smtp = "smtp.office365.com"
$port = "587"
$body = "<p>This can be <bold>HTML formatted</bold> with a bit of creative tagging.</p>"
$subject = "Look! I can send mail from Powershell"
Send-MailMessage -SmtpServer $smtp -Port $port -UseSsl -Credential $cred -From $from -To $to -Subject $subject -Body $body -BodyAsHtml -Verbose

O365 Skype for Business – Import 3rd Party Conference Credentials

Connect to O365

$cred = Get-Credential
$session = NewCsOnlineSession -Credential $cred -Verbose
Import-PSSession $session

Next, assign the CSV containing the user list to a variable

$accts = Import-Csv "C:\temp\users.csv"

Loop through the accounts in the CSV file and assign the credentials to the users


foreach ($user in $accts) { Set-CsUserAcp -Identity $user.Acct -TollNumber $user.TollNumber -TollFreeNumbers $user.TollFreeNumber -ParticipantPasscode $user.Passcode -Domain "lync.pgi.com" -Name "PGi" -URL "http://www.pgi.com/lynconline/localnums.php" }

If you do not know your Domain or URL, compare with an existing assignment

$user = Get-CsUserAcp
$user.AcpInfo