Category Archives: Excel

Delimiters

A delimiter is a sequence of one or more characters used to specify the boundary between separate, independent regions in plain text or other data streams. An example of a delimiter is the comma character, which acts as a field delimiter in a sequence of comma-separated values.

Common delimiters:

  • Tab
  • Colon
  • Semicolon
  • Comma
  • Space
  • Pipe
  • Hyphen

Delimiters in Excel

Excel uses delimiters in the Text to Columns function.  The options are tab (    ),  semicolon (;), comma (,), space ( ), or other which is an input field for a single custom character.

Intro paragraph from Wikipedia https://en.wikipedia.org/wiki/Delimiter

 

Alphabetical 50 US States & Abbreviations List

An online, alphabetical list of the 50 US State names, postal code (2 letter abbreviations), and AP style abbreviations is convenient to have around.  Teachers, students, business people, and developers can find this handy.  Make lists in spreadsheets, menus, arrays, drop-down lists or combination boxes.

Alphabetical 50 US States & Abbreviations

Uppercase Name Name Postal Code Abbreviation
ALABAMA Alabama AL Ala.
ALASKA Alaska AK Alaska
ARIZONA Arizona AZ Ariz.
ARKANSAS Arkansas AR Ark.
CALIFORNIA California CA Calif.
COLORADO Colorado CO Colo.
CONNECTICUT Connecticut CT Conn.
DELAWARE Delaware DE Del.
FLORIDA Florida FL Fla.
GEORGIA Georgia GA Ga.
HAWAII Hawaii HI Hawaii
IDAHO Idaho ID Idaho
ILLINOIS Illinois IL Ill.
INDIANA Indiana IN Ind.
IOWA Iowa IA Iowa
KANSAS Kansas KS Kan.
KENTUCKY Kentucky KY Ky.
LOUISIANA Louisiana LA La.
MAINE Maine ME Maine
MARYLAND Maryland MD Md.
MASSACHUSETTS Massachusetts MA Mass.
MICHIGAN Michigan MI Mich.
MINNESOTA Minnesota MN Minn.
MISSISSIPPI Mississippi MS Miss.
MISSOURI Missouri MO Mo.
MONTANA Montana MT Mont.
NEBRASKA Nebraska NE Neb.
NEVADA Nevada NV Nev.
NEW HAMPSHIRE New Hampshire NH N.H.
NEW JERSEY New Jersey NJ N.J.
NEW MEXICO New Mexico NM N.M.
NEW YORK New York NY N.Y.
NORTH CAROLINA North Carolina NC N.C.
NORTH DAKOTA North Dakota ND N.D.
OHIO Ohio OH Ohio
OKLAHOMA Oklahoma OK Okla.
OREGON Oregon OR Ore.
PENNSYLVANIA Pennsylvania PA Pa.
RHODE ISLAND Rhode Island RI R.I.
SOUTH CAROLINA South Carolina SC S.C.
SOUTH DAKOTA South Dakota SD S.D.
TENNESSEE Tennessee TN Tenn.
TEXAS Texas TX Texas
UTAH Utah UT Utah
VERMONT Vermont VT Vt.
VIRGINIA Virginia VA Va.
WASHINGTON Washington WA Wash.
WEST VIRGINIA West Virginia WV W.Va.
WISCONSIN Wisconsin WI Wis.
WYOMING Wyoming WY Wyo.

Download

creative-commons-atribution-88x31 This list is available under a Creative Commons Attribution license (CC BY 3.0)

Download “US States List” sot-us-states.csv – Downloaded 11 times – 2 KB

Excel Delete Blank Rows while Preserving Data

You could use Excel’s “Remove Duplicates” feature, but any duplicate data that is supposed to be there will also get blown away.

To assuredly remove blank rows without removing duplicate data (and maintaining the original order), follow these steps:

  1. Locate or insert a blank column
  2. Optionally name the column “Order” or leave it blank if there are no headers
  3. Type the number “1” in the first row of data
  4. Fill down to the last row of data
  5. Sort by an actual data field
  6. Now the blank rows will be grouped together, so delete the blank rows.
  7. Re-sort by the column of numbers from Smallest to Largest.
  8. Optionally delete the column of numbers

Need to undo this operation? Here’s how to Insert a Blank Row Between Every Row in Excel

Related how-to’s using the functions mentioned here:

Excel create a GETURL function

  1. Open the workbook
  2. Enter into the VBA editor (Press Alt+F11)
  3. Insert a new module (Insert > Module)
  4. Copy and Paste the Excel user defined function below
  5. Exit out of VBA (Press Alt+Q) the function will be saved
  6. Use this syntax for this custom Excel function: =GETURL(cell)

By the way

  • VBA stands for Visual Basic for Applications

References

Excel: Insert blank row between every row

There is no inbuilt way in Excel to insert a blank row between every existing row, but it is achievable without doing it manually.

The process is to create a column with repeated numerals (1 to n, where n is the number of rows with data) equal to twice the number of rows of data (2n) and sort by that column so blank lines appear between each data row.  Here’s how…

  1. Locate or insert a blank column
  2. Optionally name the column “Order” or leave it blank if there are no headers
  3. Type the number “1” in the first row of data
  4. Fill down to the last row of data
  5. Select and Copy all the numbers just created in that column
  6. Paste the selection below the data into the first blank row
  7. Sort by the column of numbers from Smallest to Largest.
  8. Optionally delete the column of numbers

Tip: if you need multiple blank rows between, repeat step 6 as necessary

Related:

Excel month name, abbreviation from date

In Excel the Text formatting formula (=TEXT()) is capible of re-formatting Excel’s built in date storage format.

Type Date Example Result Formula
Full Month Name 1/25/2016 January =TEXT(B2, “mmmm”)
Abbreviated Month 1/25/2016 Jan =TEXT(B3, “mmm”)
Two digit month number 1/25/2016 01 =TEXT(B4, “mm”)
Single/double month number 1/25/2016 1 =TEXT(B5, “m”)

Related search:

  • excel text month name

Resources: