Formatting Spreadsheet Cells with Unit of Measure

I'm working with data imported from a GPS unit. The Altitude field is reported by a value with the unit of measure tacked on the end (below).



The extra characters after the numeric value need to be removed if this value is going to be used in a database or a spreadsheet.  As of now it's being stored as a text value and is not as useful for calculations, summarizations, or sorting.

The general idea is to use only a certain amount of text from the left side of the Altitude values, however there are various lengths of numbers. "11" and "-4" are both composed of two characters, but "2" is made up of one character.

The best course of action is to remove those last three characters from each value so it can be treated as an integer (or other numeric value).  The best way to do this is to combine two simple and powerful functions into a spreadsheet formula.

Function	Syntax	Description
Length	=LEN(text)	Returns the length of a string including spaces. Text is the text (or cell) whose length is to be determined.
Left	=LEFT(text, number)	Returns the first character or characters in a text string. Text is the text (or cell) where the initial partial words are to be determined. Number (optional) is the number of characters for the start text. If this parameter is not defined, one character is returned.

Length example:
The length function returns the number of characters that are present in a cell





Now that we know how many characters are found in each individual record, we can create a formula that will pull just the specific numbers we want using the Left function.

Combining with the Left function:
The following formulas return only a certain number of the Left most characters from the Altitude field.  That number is calculated by subtracting 3 (a single space and "ft") from the total length of the Altitude field.





Sometimes Google Docs treats this text field as a number already, so the formula doesn't need to include the -3 (i.e. simply testing the =LEN(C2) formula returns 2 instead of 5).




The Right function is very similar to the Left function.  The Mid function, on the other hand, is used to report strings that do not begin on the ends of a cell, but rather when information begins closer to the center or more than one character away from the left most position. Check out the "Decimal Degrees to Degrees, Minutes, Seconds (DD to DMS):" section of my post on Angular Unit Conversion for an example.