Excel is great at splitting text across multiple columns. For basic text separation (e.g. putting first and last name in separate columns) the LEFT and RIGHT functions work fine. But these functions fall short when applied to data that follow an inconsistent pattern. In the below example, the errant inclusion of a middle name wrecks havoc with our formula results.
Using the SUBSTITUTE function one can identify and replace the last space in the cell with a uniquely identifying character (e.g., an underscore). This character can then be used as a delimiter to separate Lastname from full name.
SUBSTITUTE: replace character(s) within a cell with specified character(s)
= SUBSTITUTE ( text, old text, new text, instance)
Note, instance is optional; when omitted, every occurrence of old text is replaced with the new.
Linking the worksheet name to a cell in the spreadsheet is easily accomplished using Excel’s CELL function. Once joined by the MID and SEARCH functions, you need only change the sheet’s name and the linked cell will update to match.
Displaying the Sheet Name in a Cell
Type (or copy and paste) the following formula into a cell
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
Using the Dashboard touchpad, access the Control Menu. The vehicle controls for the Model S will display.
Hold the button down for 3 seconds. A Please Enter Access Code prompt will appear.
Enter 007 and tap OK.
Re-access the Controls Menu and tap the Suspension Tab. The Lotus Espirit displays (from the Bond classic The Spy Who Loved Me).
Set Depth option to 20,000 leagues (thanks, Jules Verne) and enjoy.
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.