Friday, October 28, 2016

Excel - Formatting Phone Numbers

How-to article: How to format any phone number, no matter how badly typed, into a standardized format, complete with optional punctuation and optional default area-codes.  For example, (208)1234567 could reformat to 208.123.4567 or 208-123-4567.

This article was written for Excel 2013, but works with any version of Excel.
It uses Macros - don't panic - this is easy

In an example spreadsheet, a simple formula can strip all punctuation from a phone number, leaving only digits, or it can re-format the number into a preferred style.  The formula can be copied down a large list of numbers. 

Five common styles are supported; this illustrates three of them:

Click for larger view

For example, consider these badly-typed phone numbers:

208-123-4567    (properly punctuated, but you might prefer European periods)
208.123-4567    (mixed punctuation)
(208)123-4567  (parenthesis, no spacing)
208/1234567     (inconsistent punctuation or no punctuation)
   123-4567        (leading spaces, no default area-code)

Other problems might include
  • Missing area-codes
  • International dialing prefixes
  • Phone numbers with extraneous leading "1" or "9" prefixes
  • Extensions

The formula, described below, automatically fixes all punctuation and return a properly-formatted number, in the style you prefer.
  • Can force missing (default) area-codes; converting 7-digit to 10-digits
  • Enforce area-code punctuation with hyphens, periods or parenthesis
  • Punctuate with American-hyphens or European.periods (my favorite)
  • Retains extension information, keying on an " x"
  • Extensions can be numeric or text  (e.g. x789  or x Bob's Cell)
  • Supports international dialing prefixes
  • The formula can be easily moved to other spreadsheets
  • Bonus:  A "StripNonNumeric" function, which can remove all punctuation and letters

How it works

Excel does not have a good way to punctuate phone numbers.  But this article shows how you can invent your own formula  -- "=FormatPhoneNumber"-- which behaves much like any other Excel formula.  The formula can be used in any spreadsheet and is easily transportable. 

The formula takes advantage of Visual Basic and you will be writing a "User Defined Function" - also known as a Macro.  Although this is an advanced topic, the instructions here will guide you through the steps. Novice Excel users can accomplish this.

Because this is a Visual Basic routine, you can change the logic if you don't like how it behaves.  Once you learn this technique, you can write your own routines.

Macro Design

The macro is composed of two major verbs.  As you will see, I like to number them:

A700_FormatPhoneNumber( )
A790_StripNonNumerics ( )

The FormatPhoneNumber routine is a mini-program that takes a cell, removes all punctuation, then re-assembles the phone-number using your preferred formatting style.  While it is doing this, it pays attention to the area-code, dialing prefixes, and extensions. The styles are controlled with on-the-fly parameters.

The A790_StripNonNumerics is a stand-alone function that is used by the formatting logic.  It removes all numeric values from a cell, including internal spaces, punctuation, letters and other characters, leaving only the digits.  This can be used for non-phone-number strings.

The Formula

Imagine cell A1 contains "208/123-45 67"  and you want it re-formatted as "208.123.4567", placing the results in the next cell, B1.  The formula, which begins with an equal-sign, looks like this: 

This formula will not work until the macro-code that supports it is linked into the sheet.  I have made this easy by writing the code and you are welcome to download.  The download has no strings attached; it is free, with no registration or advertisements. 

Steal My Code

Read the rest of this article if you want to know how to write your own function.  This can get technical.  Or you can download my spreadsheet and try this yourself.  You are invited to download the Excel spreadsheet and Macro from Keyliner's public GDrive - 2 files:

Download Steps:

1.  Prep:  Using Windows File Explorer, create a new folder to hold the optional, but recommended Macro file.  Use any location desired.  I recommend the following:

Create folder C:\Data
Create sub-folder:  C:\Data\Source
Create sub-folder: C:\Data\Source\CommonVB

2.  From the link below, open the keyliner GDrive folder.

Download the spreadsheet, (which contains both sample data, formulas and a fully-tested and ready-to-use macro).  Note this is an "xlsm" (an Excel Macro Sheet).  Save to any location, such as C:\Data\Wksheets or "Documents"

Keyliner Excel Phone Number Formatting Download

Two files are in the folder.  "Other-mouse-click" and download to your local workstation.

3.  Optionally download the Macro (A700_Format.bas) as a separate (second) file and save to the recommended CommonVB folder, created in Step 1.  The Macro is needed if you want to use these techniques in your own spreadsheets.  I recommend downloading this file.

Note: This is a ".bas" Visual Basic Excel Macro. 

Quick Testing:

A.  On your workstation, launch Excel.

B.  Open the downloaded sheet, "C:\Data\Wksheets\PhoneNumberMacros.xlsm"
      If prompted to enable macro, approve.

      You may need to enable Macros.

      -Select File, Options, Trust Center, Trust Center Settings
      -In the Macro Settings, consider enabling all Macros
       Or add a trusted location, where you saved this particular sheet.

       Admittedly, Microsoft is nervous, especially if you download macros
       from the Internet.  You should be cautious of content you do not trust.
      If you do not trust this content, review the code at the end of this article
      and write your own version of this macro.

C.  In the sample sheet, in the first tan-colored, cell A12,
      type a test phone number of your choosing and press Enter. 

     All example phone numbers in that row re-calculate and re-format, using various options.

D. If curious, view the code and change the Default AreaCode:
     Anywhere within the sheet, Press Alt-F11.  This opens the Visual Basic Editor
     On the Left-Nav, expand Modules
     Double-click A700_Format
     Review code

     Press ctrl-F (find).  Search for "*Change Default AreaCode*" (no quotes)
     Change "208" to your local AreaCode (quotes required)    

 E.  Once your default AreaCode has been set,

      On the left-nav tree, "other-mouse-click" A700_Format
      Chose "Export File"
      Tunnel to "C:\Data\Source\CommonVB"
      Export the file as "A700_Format.bas", overwriting your previous download

      You have just changed the macro.
       Click "X" and close the Visual Basic Editor

       In cell A12, Type a new test phone number, without an AreaCode (e.g. 8884567).
       Press Enter.  Notice the new default areacode is applied.

F.  Close and save the example sheet, which preserves the new default areacode.
     Note the sheet must be saved as an .xlsm (macro sheet). 
     (File, Save-As; change the type to a Macro sheet)

Linking A700_Format to any sheet

To use the new formatting formula in your own sheet, follow these steps.

1.  From any new or existing spreadsheet, press Alt-F11. 
     This opens Visual Basic.

2.  On the left-nav, locate the spreadsheet's name.  You may have more than one spreadsheet open and all will be reflected in the list.  Locate by the worksheet's name.  In this example, see "Book1".  (To make this example simpler, it is best to close all other spreadsheets.)

2.  "Other-mouse-click" the sheet's name.  Choose "Import Files"
     Browse to the location where A700_Format.bas was saved

     For example:  C:\Data\Source\CommonVB\A700_Format.bas

3.  Close the Visual Basic Editor ("X") and return to your worksheet.
     The macro is linked and is ready-to-use.  No need to compile.

4.  In any cell, say A1, type an un-formatted phone number:

     For example: "8884567"  (phone number, no areal-code)

5.  In cell B1, type this formula and press Enter

     =A700_FormatPhoneNumber(A1,"-",true, false)

     A1 = the "bad" phone number
     "-" = the punctuation you would like to use.  I prefer "." (periods) instead of dashes
     true = force default areacode when missing.  This is the word "true"
     false = use parenthesis around the areacode (ugly and unnecessary clutter)

     All four parameters are required.  Use commas to separate.  Parenthesis and quotes required.

Results: Note the phone number is properly punctuated, and a default AreaCode was applied.

If your default AreaCode is incorrect, see "Quick Testing", above.  Those steps change the .bas file.  Then re-export, and re-import into a new spreadsheet to confirm.

Features of Interest

  • Missing area-codes can optionally be populated with a default area-code.  See boolforceAreaCode = true - which is the first "true/false" in the formula in step 5.  See the steps above for how to change the default AreaCode

    AreaCode punctuation (parenthesis) is controlled with the second "true/false".  Use false to disable area-code parenthesis.
  • If a phone number contains a trailing " x" (lower-cased space-x, as in "extension), all text afterwards is preserved.  "208.888.4567 x107"    or   "208.888.4567 x Office"
  • If international dialing, use a "+" prefix, as in "44+208.888.4567.  I do not have a lot of experience with international dialing.  I welcome examples and suggestions on this.
  • If this is a 7-digit or 10-digit number that begins with a "1" or a "9", this routine removes those prefixes.  For example, 1-208-888-4567  becomes 208-888-4567.
  • If the phone number is short,  e.g. "4567", it is not formatted and is assumed to be a local extension.  Similarly, if the number is too long or not recognized as a phone number, it returns, as-typed, without modification.
  • If the phone number begins with an asterisk (*), the number is left exactly-as-typed, with no formatting.
  • A sister formula, "A790_StripNonNumerics" can remove all non-numeric values from any cell, leaving only digits.  "(208) 888-4567" becomes "2088884567" and a part number "ABX5000-102" becomes "5000102".


If you are skilled in Visual Basic Macros, below is the source code.  Copy and paste into any new module.  This is good, tight code, with nothing sneaky.  I welcome suggestions and enhancements.

Option Explicit

Public Function A700_FormatPhoneNumber(ByVal strpassedCell As String, strpunctuation As String, boolforceAreaCode As Boolean, boolrequireParens As Boolean) As String
  'Returns a formatted phone number, regardless of how badly-typed

  'VBA Macro: Tested in Excel 2013
  'Should work in all versions of Excel, old and new

  'Macro by Tim Wolf (c)2016
  'Article describing:

  'Macro is free to use in all private and commercial needs

  'Required: In this script, search for "*Change Default AreaCode*" and change to your preference

  'Use: =A700_FormatPhoneNumber("(208-123.4567 x107 Bob Smith's Phone",".",true,false)
  '   Returns string: 208.123.4567 x107 Bob Smith's Phone"
  '   "123-4567" returns "208.123.4567"

  'where: strpunctuation is typically a hyphen "-" or a "."
  '    boolForceAreaCode - adds default area-code, if missing.
  '              Area-code (here as "208") is hard-coded in the macro; change as needed
  '              Use word "true" or "false"
  '    boolrequireParens - Adds parenthesis to all area-codes e.g. "(208) 123-4567", "(703) 123-4567"
  '              Use word "true" or "false"

  Dim strdefaultAreaCode As String
  Dim strfrontside As String
  Dim strbackside As String
  Dim strformatted As String

  Dim boolinternationalDetected As Boolean
  Dim boolextensionDetected As Boolean
  Dim strinternationalPrefix As String
  Dim strextension As String

  Dim ihpos As Integer

  boolinternationalDetected = False
  boolextensionDetected = False

  'Early-exit: If the phone number begins with an asterisk - leave-as-typed, unmodified
  If Left(Trim(strpassedCell), 1) = "*" Then
   strpassedCell = Trim(strpassedCell)
   'Lob-off the asterisk
   A700_FormatPhoneNumber = Mid(strpassedCell, 2, 999)
   Exit Function
  End If

  '**Change Default AreaCode** Change from "208" to "your preference" (quotes required)
  strdefaultAreaCode = "208"

  If boolrequireParens = True And boolforceAreaCode = True Then
   strdefaultAreaCode = "(" & strdefaultAreaCode & ")"
  End If

  'Look for international prefixes (e.g. 44+455-123-4567, where a plus is required)
  strfrontside = Left(strpassedCell, 4)
  ihpos = InStr(1, strfrontside, "+")
  If ihpos > 0 Then
   'Siphon-off the prefix
   boolinternationalDetected = True
   strinternationalPrefix = Trim(Left(strpassedCell, ihpos - 1))

   'Reset passed cell, truncating the international prefix
   strpassedCell = Mid(strpassedCell, ihpos + 1, 999)

  End If

  ihpos = InStr(7, LCase(strpassedCell), " x")  //Look for space-x extensions
  If ihpos > 0 Then
   'Siphon-off the extension, as in 123-4567 x123
   boolextensionDetected = True
   strextension = Trim(Mid(strpassedCell, ihpos + 1))

   'Reset passed cell, truncating the extension
   strpassedCell = Trim(Left(strpassedCell, ihpos - 1))

  End If

  strpassedCell = A790_StripNonNumerics(strpassedCell)  'remove all other characters

  Select Case Len(strpassedCell)
  Case 0 To 6
   'Return the phone number unformatted, with no changes
   strformatted = strpassedCell

  Case 7
   'Standard, non-areacode number 3-4
   strformatted = A701_Format7Digits _
          (strpassedCell, _
          strpunctuation, _
          strdefaultAreaCode, _
          boolforceAreaCode, _

  Case 8
   'Non-standard phone number; check for "1" or "9" prefix
   'discard 1+ (long-distance) or 9+ (outside-line) prefixes
   'e.g. 9123-4567
   '   9-123-4567 (the dash was pre-stripped)

   If Mid(strpassedCell, 1, 1) = "1" Or Mid(strpassedCell, 1, 1) = "9" Then
     'discard prefix and treat as a standard 7-digit phone number
     strpassedCell = Mid(strpassedCell, 2, 99)
     strformatted = A701_Format7Digits _
            (strpassedCell, _
            strpunctuation, _
            strdefaultAreaCode, _
            boolforceAreaCode, _
     'Unsure what to do with this number; return unmodified
     strformatted = strpassedCell
   End If

  Case 9
     'Unsure what to do with this number; illogical; Return unmodified
     strformatted = strpassedCell

  Case 10
     'Standard 10-digit phone number (with area-code): '2081234567 (e.g.208-123-4567)
     strformatted = A702_Format10Digits(strpassedCell, strpunctuation, boolrequireParens)

  Case 11
     'Possible 10-digit phone number with a 1+ or 9+ prefix
     '12081234567 (e.g. 1-208-123-4567)

     If Mid(strpassedCell, 1, 1) = "1" Or Mid(strpassedCell, 1, 1) = "9" Then
      'discard prefix and treat as a standard 7-digit phone number
      strpassedCell = Mid(strpassedCell, 2, 99)

      strformatted = A702_Format10Digits(strpassedCell, strpunctuation, boolrequireParens)
      'return unformatted
      strformatted = strpassedCell
     End If

  Case 12
    'Possible international number; this is not supported; international numbers should use a "+"
    'and the prefix would have been stripped earlier; attempt anyway
    '442081234567  (e.g. 44+208-123-4567)

    If boolinternationalDetected = False Then
      'Could not already have an internation prefix
      'Since this is 12 long, assume the first two digits are the international prefix
      boolinternationalDetected = True
      strinternationalPrefix = Left(strpassedCell, 2)
      strpassedCell = Mid(strpassedCell, 3, 99)
      strformatted = A702_Format10Digits(strpassedCell, strpunctuation, boolrequireParens)
      'An international prefix was already detected; return unmodified
      strformatted = strpassedCell
    End If

  Case Else
    'return unformatted
    strformatted = strpassedCell

  End Select

  'All functions fall through here: Re-assemble

  If boolinternationalDetected = True Then
   strformatted = strinternationalPrefix & "+" & strformatted
  End If

  If boolextensionDetected = True Then
   strformatted = strformatted & " x" & strextension 'exactly as-typed
  End If

  A700_FormatPhoneNumber = strformatted

End Function

Private Function A702_Format10Digits(strpassedValue As String, strpunctuation As String, boolrequireParens As Boolean) As String


  Dim strareaCode As String
  Dim strfrontside As String
  Dim strbackside As String
  Dim strformatted As String

  strareaCode = Left(strpassedValue, 3)
  strfrontside = Mid(strpassedValue, 4, 3)
  strbackside = Mid(strpassedValue, 7, 4)

  If boolrequireParens = True Then
    strareaCode = "(" & strareaCode & ")"
    strformatted = strareaCode & " " & _
           strfrontside & strpunctuation & _
    strformatted = strareaCode & strpunctuation & _
           strfrontside & strpunctuation & _
  End If

  A702_Format10Digits = strformatted

End Function

Private Function A701_Format7Digits(strpassedValue As String, strpunctuation As String, strdefaultAreaCode As String, boolforceAreaCode As Boolean, boolrequireParens As Boolean) As String

  Dim strfrontside As String
  Dim strbackside As String
  Dim strformatted As String

   strfrontside = Mid(strpassedValue, 1, 3)
   strbackside = Mid(strpassedValue, 4, 4)

   If boolforceAreaCode = True Then
     If boolrequireParens = True Then
      'Append areacode plus a cosmetic space: (208) 123.4567
      strformatted = strdefaultAreaCode & " " & _
              strfrontside & strpunctuation & _
      'Append areacode with normal punctuation, no spaces: 208.123.4567
      strformatted = strdefaultAreaCode & strpunctuation & _
              strfrontside & strpunctuation & _
     End If
     'No areacode is required
     strformatted = strfrontside & strpunctuation & _
    End If

   A701_Format7Digits = strformatted

End Function

Public Function A790_StripNonNumerics(ByVal strpassedCell As String) As String

  'Removes all non-numeric values from a string and returns a string of digits
  'Example: =A790_StripNonNumerics("(208)-123.4567 Bob")  Result: "2081234567"

  'VBA Macro: Tested in Excel 2013; will work in all versions of Excel, old and new
  'Macro by Tim Wolf (c)2016
  'Article describing:

  'Macro is free to use in all private and commercial needs

  'Trim all spaces and non-numerics, including decimals
  'This is not as sophisticated as the C# version

  Dim strstripped As String
  Dim i As Integer

  For i = 1 To Len(strpassedCell) Step 1
    Dim strcurrentChar As String
    strcurrentChar = Mid(strpassedCell, i, 1)
    If (IsNumeric(strcurrentChar)) Then
     strstripped = strstripped & strcurrentChar
     'skip this character
    End If
  Next i

  A790_StripNonNumerics = strstripped

End Function

Related Keyliner Articles:
Excel: How to write UDF Functions
Excel: Parsing City-State-Zip
Excel: Parsing First/Last Names
Excel: SuperTrim and other cool functions

Monday, October 24, 2016

Better, Easier, Safer Passwords

Most of our passwords are flawed and not secure.  This article describes a better way, and includes a scheme to help you remember all of your passwords.  This article was originally published on 08/2011 and revised most recently on 2015.08.

We have all been told to make passwords eight characters long, to use a mixture of upper-case and lower, as well as numbers and a special character.  This is not good enough.  With simple software, and a standard PC, brute-force attacks can now easily crack these types of passwords. 

I attended a security training class where the Linked-In database that was leaked a few years ago, was hacked, in real-time, during the class.  Using a lowly, run-of-the-mill laptop, 300,000 real-world encrypted passwords were cracked in two minutes.

The passwords were not just simple words, they had numbers, mixed case, special and replaced characters.  Given another couple of hours, our instructor could have cracked an additional 200,000 passwords, with an 80 to 90% success rate. 

The passwords had one thing in common:  Most were 8 to 10 characters long.  The ones that were not cracked (yet), were longer.

"Through 20 years of effort,
we have successfully trained everyone to use passwords that are hard for humans to remember,
but easy for computers to guess."

This interesting "arsTechnica" article discusses the techniques now being used:

Anatomy of a hack: How crackers ransack passwords like “qeadzcwrsfxv1331” 

Be sure to glance through the comments at the end of the article.

20 Years of Password Nonsense

Straying from a brute-force attack, are there other ways to get a password?  Of course, and these are even more fun. No matter how long, no matter how complex, no password is safe if you give it away through social engineering (fake login pages).

Never, ever click a link from a vendor or bank's email that will take you to their site.  These are easily spoofed.  Instead, open your browser and go to the site yourself.

And passwords are hackable with this method:

This now-famous xkcd comic describes the benefit of a simpler password, using multiple words.  It has circled the Earth about a million times, and although the idea is sound, it is less-than-perfect and does not work well with the dozens of accounts we need to login to:

Click for a larger view, click right-x to return.

Having a multi-word password, with spaces or not, is better than using "password123", but it is prone to attacks using the methods discussed in the ARS article.

But still, a 16 or 20-character password (a password phrase!) will slow them down.
Your passwords should now be this length.

Password Safes and Vaults.  Horrible Idea!

Since you should use different passwords for each different account, how many phrases (correcthorsebatterystaple) can you invent, for all the places you need a password?  You need help

Password Safes store your numerous passwords in some other protected program or vault.  The trouble is, this doesn't work in real life.  Each time you need to login somewhere, you have to unlock the safe, find the account, and type the ugly password.

In practice, this is too cumbersome and you won't use it.

A pattern or scheme (see below), or two-factor are better solutions.  Both are described next.


You may think your data is not that important.  I disagree.  In my case, they could adjust my house thermostat and cause my refrigerator to order more milk.  In all seriousness, with my credentials, they could get to my bank account or Amazon. All of my financial and tax records would be exposed.  I could imagine my address books and my cellular accounts are of interest.  All kinds of mayhem would ensue and the violation would take years to unwind.

With or without 2-step authentication, do these things for better password security:

1. Password length is king.

Longer passwords are better than hard-to-type passwords.

qeadzcwrsfxv -- (12 Chars) is not nearly as secure as
catschasefrogs  -- (14) two more characters really help

2.  Use a memorable password phrase (three or more words)

uSe mIxed case, where the first word is not capitalized.

catsChaseFrogs   (14 characters)

3.  Make one of the words a non-dictionary word. 


4.  Special characters or numbers.   Most sites require special characters and numbers in their passwords.  This does not add  much security, but since such nonsense is often required, make this part of your scheme.  

Special Characters:  I like to use a space or a period because they are easy to type and are as good of a character as any.  After practicing the password a few times, make sure the keystrokes are easy to reach, and in a good pattern.  Re-arrange punctuation, as needed.

cats Chase.Krogs 2aa  (20 characters, including 2 spaces and a period.  Yet easy to remember and type)

I am not recommending numbers and special characters because they increase the password's entropy (available characters).  The real reason is because most sites require them, so you have to use them.  Since you must, adding special characters is an easy way to increase the length. 

Here is the trick:

5. Use a different password for each site -- but use a scheme to help remember. 

The reason:  If one password is compromised, you won't loose everything. But all these different passwords are impossible to remember.  Consider this trick: Use the same base password on each site/program, but add a prefix or suffix, making it unique.

For example:
If your normal password were "aK9doggly.barks"

use "aK9doggly.barks hotm" for your hot mail account.
use "aK9doggly.barks goog" for your google account
use "aK9doggly.barks bank" for your banking account

or "hK9doggly.barksm"  where "h-m" is hot mail
or "gK9doggly.barkso"   where "g-o" (oh) is google (one word)
or "mK9doggly.barksb"    where "m-b is "my bank"

Devise your own scheme, then use it everywhere; make it predictable to you.

If one password were compromised, the hacker's automated programs would attempt that password to login to other systems and would fail because each password is different.  Naturally, a human would see through this, but humans don't look at these things -- they are automated -- and the programs won't know the scheme. 

6.  When available, use 2-step authentication.  Especially on the important accounts.  Make sure your phone is password-protected.  See below.

7.  Dumb sites

For dumb sites, where you could care-less if it were hacked, such as registration sites, forums, etc, use a simpler password and by all means use the same password in all unimportant sites.  I call this an expendable or junk password.  Do not bother using a password scheme.

For example: DumKats.2aa

Two-Factor Authentication

Longer passwords slow down hackers - so much so, they give up.  But if your vendor's database escapes in a hack, all the passwords are at risk - especially the shorter ones. One way to solve this is by adding another layer of security. 

Consider "two-factor authentication."

I have been using Google's 2-step authentication (2-factor, two factor authentication) for many years for my GMail account.  Each time I login, Google sends a text message to my phone (or now with a nifty app).  Then, in a secondary login screen, I type the transmitted random numeric code.  Only then does my login succeed.  The code changes every minute and is unique to my account.

Even if they have my account credentials, they can't login without my phone. 

These companies, and others, support two-factor authentication:

Google (Gmail, Youtube, Blogger, Picassa, etc.)
Microsoft (onenote, Office 365,
Your ISP Account (should support this)
Your domain service provider (most seem to support two-factor)

Most support either an SMS text message or a dedicated app.  Use the app for your most-common, often-used services.  Use text messages for those less-common accounts.

What if I lose my cell phone?  It becomes painful.  Without going into details, Google has a moderately secure, alternate method for logging in. See this article for full details on the 2-step authentication. See also this keyliner article:  Using Google Authenticator

Things that don't work anymore:

Curse sites that require password changes every 90 days.

This goes against convention.  If you have to periodically change passwords, you will invariably pick a shorter password with numeric suffix, such as the month and year.  Sites with longer passwords that do not expire (say 12 or more characters) are safer than sites with 8-character rotating passwords.

Some forward-thinking companies are changing their stance here.  Quit making people change passwords so often, but require longer passwords.  Length is king.

Condemn passwords that are too hard to remember

For example, I have an account with this password (that I am not allowed to change).
"1SanFran$1sc0D91" (16 characters).

Every time I need this stupid password, I have to look it up.  This means I wrote it down.  I am tempted to print.  I hate this password.

Imagine this replacement, at 35 characters:  "sanFrancisco isa 9a"

- this is a much more secure password - just because of its length.  Clearly better than the first.  The complexities in the other password are nonsense.


Passwords need some complexity, but don't go nuts. Your best protection is a long password phrase with two-factor authentication.  In lieu of that, a scheme, such as the one described above, goes a long way to make this manageable.  

Related Articles:
Gmail Protection Steps
SMS Text Message: Your Gmail account has been hacked
Using Google Authenticator - a Google App
Google Documentation - 2 Step Authentication

Tuesday, October 11, 2016

Enable PING in Windows 10

How To: Enable PING in Windows 10

Follow these steps to allow a PC to reply-back to a Ping request.

By default, Windows 10 machines will not reply to a PING request in a Domain or Public Network.  The PING will report "request timed out" -- even though you know the machine is active on the network. 

Windows firewall blocks these requests to protect the workstation from probing and certain denial-of-service attacks.  With this said, it is handy to be able to ping a workstation and you may want this feature enabled.

The restriction is controlled by the Windows Firewall which has three different profiles (or types) of rules:

* Private (the local network or workgroup) - Ping enabled by default; see below
* Domain (typically in a corporate AD environment) - Ping disabled
* Public (the "Internet" - the wide-world) - Ping disabled, not recommended to enable

I recommend enabling PING on Private and Domain networks, but not on Public Networks (where you are probably NAT'ed anyway).


1.  Confirm the network-connection type is "Private"
     In Control-Panel, "Network and Sharing Center"
     Note if your network is "Private"

* If not Private, I do not recommend continuing with this article.   Often/sometimes a laptop's wireless connection was built as "Public" - and PING (will not and should not) work on this type of network.

If your home or office network was configured as "Public" or "Guest", it was likely configured wrong when first built. 

To fix: 
a) Open Windows "Settings" - the gear icon (oddly, "Settings" cannot be found in the Control-Panel)
b) Choose "WiFi,"
c)  then "Manage known networks". 
d) Other-mouse-click your network name, and "Forget". 
e) Rebuild/re-connect to the network, selecting "Private".

2. In Windows Control-Panel, "Windows Firewall", click "Advanced Settings"

Click for a larger view

3.  In Advanced Settings, on left-nav, click "Inbound Rules"

4.  Scroll to "File and Printer Sharing (Echo Request - ICMPv4-IN)"

Note: Three (v4-In) - one for each profile.   "Private" should already be enabled

5.  Locate "File and Printer Sharing (Echo Request - ICMPv4-In -Domain)
     "Other-mouse-click" and choose "Enable Rule"

Click for larger view

6.  Confirm the "Private" version is enabled (green-check-mark).  Then, consider enabling the same two rules for ICMPv6 - Private and Domain, although this is less-than important.

In each Rule's "Advanced Settings", you can chose a checkbox for
[X] Domain,
[X] Private, or
[X] Public. 

Although you can click multiple boxes in the rule, it will conflict with the other two similarly-named rules.  Only work with the [X] Domain (or [X] Private rule, as named.

Close the Firewall control-panel.
From any other machine in the network, PING the computer's name or IP address.  To find the computer's name, see File Explorer (My Computer), Properties.

You are done.

If the PING is still failing, ping the machine's IP Address.  Locate the address with these steps:

a)  From the machine you are trying to ping, open a DOS prompt.
b)  Type this command, no quotes:  "ipconfig"
c)  Note the IPV4 address

d)  Then, from a remote machine on the same network, type this DOS command:

     PING     (example IP Address, yours will be different)

If the PING is still failing, and especially-if the target machine is a wireless, could it be connected to a different wireless network?  For example, your DSL or Cable modem may have a wireless antenna, and that antenna may be on the "other side" of your internal home network.  Older Qwest/ATT DSL Modems were configured in this fashion.  Diagnosing this is beyond the scope of this article.

Comments welcome.