Excel Length Formula
The Excel Length formula is a useful formula that can be used in many different circumstances. I’ll be showing a few of the useful examples of it below. But first let’s take a look at the formula itself:
LEN(text)
where text is any string you want to find the length of. This formula is really easy to use, and the hardest part may be remembering that to use this formula it is LEN(text) and not LENGTH(text), as you will receive an error if you try to use it this way.
EXAMPLES
Example 1: =LEN(“Bird”) will return 4, since there are 4 characters in the string “Bird”
Example 2: =LEN(123456) and =LEN(“123456″) will both return 6, as the formula sees both of these as strings and not the first one as a number.
Example 3: =LEN(“Excel Help”)will return 10, because in the string there are 9 letters and a space, which is included in the count for this formula.
Match Formula in Excel
The Match Formula in Excel is an easy way to have excel look through an array of values and return the relative position if the match is found. The difference between this and the lookup functions (eg. Vlookup) is it return the position of the item and not the item itself. Let’s go ahead and take a look at the formula:
Match(lookup_value, lookup_array, match_type)
where lookup_value is the value you are looking up against the array, lookup_array is the range of values you are trying to match against, and match_type tells the formula how it should do the match. Match_type can be one of three values:
1. -1 (largest value less than or equal to lookup_value, array must be in ascending order),
2. 0 (exact match of the lookup_value, array does not need to be ordered),
3. 1 (smallest value greater than or equal to lookup_value, array must be in descending order).
Rank Formula in Excel
The Rank Formula that is built into Excel can be a very useful way of speeding up the ranking of several items. Let’s take a look at the formula and see how this can help us out:
=rank(number, ref, order)
where number is the number whose rank you want to find; ref is a reference to the range of numbers you want to rank against (non-numeric values in the range are ignored), and order refers to the way you want to ranking to appear – 0 or blank for descending and a non-zero number for ascending.
Hyperlink Formula in Excel
I’m going to add this section to the Microsoft Excel Help section (menu on the left) of the website. An excel user contacted me and questioned how to create a hyperlink inside of excel using the value in another cell to as part of the link. The Formula looks like this:
HYPERLINK(link_location,friendly_name)
where link_location is where you want the hyperlink to go to and friendly_name is how the link will appear on your sheet.
For example, if you have a file on your desktop it would look something like this:
Hyperlink(“C:\Documents and Settings\Admin1\Desktop\Example.xls”,”Example Link”)
Today() Formula in Excel
A common excel formula that can come in handy in many projects with Excel is the Today() Function. The Today() Function is used to return the serial number of the current date. By formatting the cell where the formula is used, it will return the Month, Day and Year of today. One of the easiest functions in Excel to use, here is the formula:
=Today()
Since today is June 5, 2007, the formula will return 6/5/07 since it is automatically formatted in date format. If you were to change the formatting to a number, the formula would return the serial number 39238 (If you are wondering what this number stands for, it is the number of days from January 1, 1900 – Tomorrow the serial number returned would be 39239)
There are many places in your projects you will be using the Today() function, from showing the date your spreadsheets were last updated to calculated how many days from today a deadline is. Once you learn to use this function regularly it will be a function you can’t live without.
Over the next few posts, I will be adding many example of how to use the numerous date and time functions built into excel. Please contact us with any questions or suggestions you may have.
** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.
