Excel Text 2 of 4: Change Case

Changing case is easily accomplished using Excel’s text functions.

Text 2-UpperCase10fps
Click to enlarge image
UPPER convert to upper case =UPPER(“Kanye”) returns KANYE
LOWER convert to lowercase =LOWER(“KIM”) returns kim
PROPER convert 1st  letter of each word to uppercase =PROPER(“north west”) returns North West

Convert Text to Uppercase

  1. In a blank cell enter =UPPER( text ), where text refers to the cell containing text to reformat. A copy is created in uppercase.
  2. Select the newly created copy and press CTRL+C (Copy).
  3. Select original cell.
  4. RIGHT+CLICK and select PASTE VALUES.

For 45 second video, click here:

Cheers!

hɔuᴉnb

Additional reading:

Excel Text Functions 1 of 4: =LEFT() and =RIGHT()

For 60 second video, click here:

During the next series of posts I will demonstrate some commonly used Text functions. In the illustrated example we populate a CustomerCode column by combining

  • the first letter of the first name,
  • the first 4 letters of the last name, and
  • the last 4 digits of the phone number

=LEFT

Returns the leftmost character(s) of a cell, as specified by the optional Num_chars argument.

LEFT Right Functions_left 4d
click image to view at full resolution

 

Using the LEFT function to copy the leftmost characters of a cell

  1. Select cell to display the result.
  2. On the Formulas tab, in the Function Library group, click Text drop-down and select LEFT.
  3. In Text field select or type cell containing text.
  4. In the Num_chars field enter the number of characters to copy. When left blank the result is only the leftmost character.
  5. Click OK.

Concatenate using ‘&’

Use the Ampersand (&) to concatenate (combine) text. For example, with “North” in A1 and “West” in B1, the formula =A1&B1 returns “NorthWest”

=RIGHT

Returns the rightmost character(s) of a cell, as specified by the optional Num_chars argument.

Using the RIGHT function to copy rightmost character(s) of a cell

  1. Select cell to display the result.
  2. On the Formulas tab, in the Function Library group, click Text drop-down and select RIGHT.
  3. In Text field select or type cell containing text.
  4. In the Num_chars field enter number of characters to copy. When left blank the result is only the rightmost character.
  5. Click OK.

Cheers!

hɔuᴉnb

Additional reading:

Comments and questions are always welcome!

PDF Bookmarks Made Easy

Here’s a quick and easy method to create a bookmarks in Acrobat Professional and Nuance PDF Pro.

CreateBookmark491a

Creating a Bookmark

  1. Using the Select tool Select, select the text to bookmark.
  2. Press CTRL + B.

The bookmark reference is created from the selected text. CLICK + DRAG the reference to arrange, demote, or promote the bookmark.

Cheers!

hɔuᴉnb

Related posts:

Comments and questions are always welcome!

Tesla; the S is for Submersible

Tesla Model S Submarine Easter Egg

 

This week I had an opportunity to ride in a Tesla. The driver commented on how it was the first car to make him feel like an 18 year old behind the wheel. Here's another example of how Tesla is bringing the fun.

Spring is (finally) here and in tribute to the season I present an early Easter egg, courtesy of Tesla.

Disclaimer: The author makes no claim that your model will be watertight, nor that it will be equipped with SONAR, subsurface-to-air missiles or Barbara Bach.

Tesla Model S 007 Easter Egg

  1. Using the Dashboard touchpad, access the Control Menu. The vehicle controls for the Model S will display.
  2. Hold theTESLA t button down for 3 seconds. A Please Enter Access Code prompt will appear.
  3. Enter 007 and tap OK.
  4. Re-access the Controls Menu and tap the Suspension Tab. The Lotus Espirit displays (from the Bond classic The Spy Who Loved Me).

    TModelS_Dashboard
    Lotus Espirit with Submersible settings displayed

Set Depth option to 20,000 leagues (thanks, Jules Verne) and enjoy.

Additional reading…

Cheers!

hɔuᴉnb

Vessyl: It knows what you’ve been drinking

Any idea how much sugar is in that juice drink?

The Vessyl drinking cup can detect and log your fluid intake.

Vessyl Drinking Thermos

(This post originally appeared in LinkedIn)

Worried you’re not sufficiently hydrating during the day?
Any idea how much sugar is in that juice drink?

The Vessyl drinking cup (now accepting pre-orders) can detect and log your fluid intake. How smart is it? In addition to H2O this chalice can apparently identify various beverages by name (e.g., Crush soda, Vitamin Water, Tropicana O.J.) and even varieties of Gatorade flavors. The on-cup display shows beverage amount and components (i.e., sugar, caffeine, etc.) and the data is sent to your activity tracking device.

Although I am not sold on the need for this level of monitoring I do appreciate the science behind it. Among the wave of activity trackers flooding our internet shelves this will certainly appeal to some.

For more on the Vessyl click here.

Using Histogram for Data Analysis

Histogram

After completing the J.P. Morgan Corporate Challenge 5K race  I was curious: how many other runners had a similar finish time as I? What an Excel-lent opportunity for a Histogram!

DataAnalysisHistogram

 

A Histogram analyzes values, groups those numbers into bins, (population frequencies) of your choosing, and displays that data in a table or chart. The Histogram tool is part of the Data Analysis Toolpak. It may not initially appear on your ribbon, but is a cinch to install.

Adding the Data Analysis Toolpak

  1. On the Ribbon, click File, then Options.
  2. Click Add-ins.
  3. On the Manage drop-down, select Excel Add-ins and click Go.
  4. Select Analysis Toolpak and click OK.

For a simple Histogram, here’s what you will need:

  • Input Range: cells containing values to be reviewed. The range must be sorted in ascending sequence.
  • Bin Range: cells to act as virtual bins within which Excel will place matching numbers. For example, a teacher grading a test might use Bin values for the test scores she wants to lump together. Bin range is an optional; if left blank, Excel will create Bins.In my example (below) the Bin values are increments of one minute, between 17 and 37 minutes (the fastest and longest finishing times).
BIN
Bin range, using finish times from 17:00 to 37:00
  • Output: Location for the Histogram table. These options include RangeNew Worksheet, and New Workbook.
  • Chart Output: (optional) charts the Histogram table output.

Creating the Histogram Table and Chart

  1. On Data tab of the Ribbon, click Data Analysis. The Data Analysis dialog appears.
  2. Select Histogram and click OK. The  Histogram dialog appears.
  3. Select or enter the Input Range (e.g., E11:E2804) , Bin Range (e.g., K13:K32), and Output range (e.g. M12).,
  4. Check Chart Output.
  5. OK.

To download the sample file click JPM Corp Challenge (SF) Results

Watch this ~4m video to learn more.

 

Cheers!

hɔuᴉnb

 

Related posts:

Comments and questions are always welcome!

Create Names From Selection

Creating Defined Names enables you to take advantage of a host of excel features and shortcuts. This post shows one easy method to create Defined Names from your selection and showcases a nifty method to find where two Defined names intersect.

This tip involves naming cells, also known as Defined Names. Among the many benefits, Defined Names can be used to:

  • quickly navigate large spreadsheets
  • easily define print areas
  • simplify formula entry

In the below table you want to easily reference the data in any given column or row. Since the data is in a table you can easily create a Defined Name, for each column and row, using the Create from Selection command.

CreateNamesIntersect

Creating Defined Names From Selection

  1. Select all the cells in the table.
    Tip: select one cell, then press CTRL + A.
  2. On the Formulas tab, in the Defined Names group, click Create from Selection. The Create Names from Selection dialog appears.
  3. Check Top row and Left column.
  4. OK.

To display the Defined Names, press F3 or click the Name Box drop-down. Defined Names will also appear as you enter formulas, preceded by the name tag iconNametag.

 

PowerTip: The intersection of two Defined Names can be displayed using a formula.

Using Defined Names to Display the Intersecting Value

  1. In a blank cell, type =.
  2. Type the first Defined Name followed by a space.
  3. Type the second Defined Name and press Enter.

Cheers!

hɔuᴉnb

 
Watch this ~1m video to learn more.

Previous post and additional reading: