How to Split the Number From the Street Address in Excel
Splitting the number from the street addresses in Excel is something that you do with a feature called flash fill. Split the number from the street addresses in Excel with help from a Microsoft Certified Applications Specialist in this free video clip.
Expert: Jesica Garrou Filmmaker: Patrick Russell
Series Description: Microsoft Excel is one of the most powerful spreadsheet and document creation tools in existence. Get tips on Microsoft Excel with help from a Microsoft Certified Applications Specialist in this free video series.
MS Excel has many built in functions which we can use in our formula. To see all the functions by category choose Formulas Tab » Insert Function. Then Insert function Dialog appears from which we can choose function.
Functions by categories
Let us see some of the built in functions in MS Excel.
Text Functions
LOWER : Converts all characters in a supplied text string to lower case
UPPER : Converts all characters in a supplied text string to upper case
TRIM : Removes duplicate spaces, and spaces at the start and end of a text string
CONCATENATE : Joins together two or more text strings
LEFT : Returns a specified number of characters from the start of a supplied text string
MID : Returns a specified number of characters from the middle of a supplied text string
RIGHT : Returns a specified number of characters from the end of a supplied text string
LEN : Returns the length of a supplied text string.
FIND : Returns the position of a supplied character or text string from within a supplied text string (case-sensitive)
Date & Time
DATE : Returns a date, from a user-supplied year, month and day
TIME : Returns a time, from a user-supplied hour, minute and second
DATEVALUE : Converts a text string showing a date, to an integer that represents the date in Excel's date-time code
TIMEVALUE : Converts a text string showing a time, to a decimal that represents the time in Excel
NOW : Returns the current date & time
TODAY : Returns today's date
Statistical
MAX : Returns the largest value from a list of supplied numbers
MIN : Returns the smallest value from a list of supplied numbers
AVERAGE : Returns the Average of a list of supplied numbers
COUNT: Returns the number of numerical values in a supplied set of cells or values
COUNTIF : Returns the number of cells (of a supplied range), that satisfy a given criteria
SUM : Returns the sum of a supplied list of numbers
Logical
AND : Tests a number of user-defined conditions and returns TRUE if ALL of the conditions evaluate to TRUE, or FALSE otherwise
OR : Tests a number of user-defined conditions and returns TRUE if ANY of the conditions evaluate to TRUE, or FALSE otherwise
NOT : Returns a logical value that is the opposite of a user supplied logical value or expression i.e. returns FALSE is the supplied argument is TRUE and returns TRUE if the supplied argument is FALSE)
Math & Trig
ABS : Returns the absolute value (ie. the modulus) of a supplied number
SIGN : Returns the sign (+1, -1 or 0) of a supplied number
SQRT : Returns the positive square root of a given number
MOD : Returns the remainder from a division between two supplied numbers
Learn all about Excel's lookup & reference functions such as the VLOOKUP, HLOOKUP, MATCH, INDEX and CHOOSE function.
VLookup
The VLOOKUP (Vertical lookup) function looks for a value in the leftmost column of a table, and then returns a value in the same row from another column you specify.
1. Insert the VLOOKUP function shown below.
Explanation: the VLOOKUP function looks for the ID (104) in the leftmost column of the range $E$4:$G$7 and returns the value in the same row from the third column (third argument is set to 3). The fourth argument is set to FALSE to return an exact match or a #N/A error if not found.
2. Drag the VLOOKUP function in cell B2 down to cell B11.
Note: when we drag the VLOOKUP function down, the absolute reference ($E$4:$G$7) stays the same, while the relative reference (A2) changes to A3, A4, A5, etc.
HLookup
In a similar way, you can use the HLOOKUP (Horizontal lookup) function.
Match
The MATCH function returns the position of a value in a given range.
Note: Yellow found at position 3 in the range E4:E7. The third argument is optional. Set this argument to 0 to return the position of the value that is exactly equal to lookup_value (A2) or a #N/A error if not found.
Index
The INDEX function returns a specific value in a two-dimensional or one-dimensional range.
Note: 92 found at the intersection of row 3 and column 2 in the range E4:F7.
Note: 97 found at position 3 in the range E4:E7.
Choose
The CHOOSE function returns a value from a list of values, based on a position number.
In this posting, we will discuss how to calculate the age or working period with Excel in the format "YY Year, MM month, DD Day". So the output produced eg "20 Years, 6 Months, 12 Days."
Function DATEDIF
The main concept for calculating age or age working with Excel is comparing two dates, namely the date of birth or date of the first day of work with the current date. Excel functions or formulas to calculate the age or the age of the work that is DATEDIF (read: date dif) number of years, months and days between two dates is to use DATEDIF ().
Syntax:
DATEDIF (start_date, end_date, unit)
start_date is the earliest date in this case is the date of birth or date First Day of Work
end_date in this case, end date is the date that we can now replace with TODAY () or NOW ()
unit is the type of information required if the unit in the year (Y), month (M), day (D), Month in the same year (YM), or day of the same month (MD).
In this example, date of birth or date of the first day of work can be kept constant or fixed, while the Current Date or Date of First Working Day can be made relative to the function TODAY () or NOW (). The unit used for this purpose is Y Year YM MD Month and Day.
Excel formula to calculate the age or working-period in cell C6 is as follows:
=DATEDIF(B2,NOW(),"Y")&" years, "&DATEDIF(B2,NOW(),"YM")&" months, "&DATEDIF(B2,NOW(),"MD")&" days." Results:
This chapter helps you understand array formulas in Excel. Single cell array formulas perform multiple calculations in one cell.
Without Array Formula
Without using an array formula, we would execute the following steps to find the greatest progress.
1. First, we would calculate the progress of each student.
2. Next, we would use the MAX function to find the greatest progress.
With Array Formula
We don't need to store the range in column D. Excel can store this range in its memory. A range stored in Excel's memory is called an array constant.
1. We already know that we can find the progress of the first student by using the formula below.
2. To find the greatest progress (don't be overwhelmed), we add the MAX function, replace C2 with C2:C6 and B2 with B2:B6.
3. Finish by pressing CTRL + SHIFT + ENTER.
Note: The formula bar indicates that this is an array formula by enclosing it in curly braces {}. Do not type these yourself. They will disappear when you edit the formula.
Explanation: The range (array constant) is stored in Excel's memory, not in an range. The array constant looks as follows:
{19;33;63;48;13}
This array constant is used as an argument for the MAX function, giving a result of 63.
F9 Key
When working with array formulas, you can have a look at these array constants yourself.
1. Select C2:C6-B2:B6 in the formula.
2. Press F9.
That looks good. Elements in a vertical array constant are separated by semicolons. Elements in a horizontal array constant are separated by commas.
Microsoft Excel is an amazing piece of software, and even regular users might not be getting as much out of it as they can. Improve your Excel efficiency and proficiency with these basic shortcuts and functions that absolutely everyone needs to know.
1. Jump from worksheet to worksheet with Ctrl + PgDn and Ctrl + PgUp
2. Jump to the end of a data range or the next data range with Ctrl + Arrow
Of course you can move from cell to cell with arrow keys. But if you want to get around faster, hold down the Ctrl key and hit the arrow keys to get farther:
3. Add the Shift key to select data
Ctrl + Shift +Arrow will extend the current selection to the last nonblank cell in that direction:
4. Double click to copy down
To copy a formula or value down the length of your data set, you don’t need to hold and drag the mouse all the way down. Just double click the tiny box at the bottom right-hand corner of the cell:
5. Use shortcuts to quickly format values
For a number with two decimal points, use Ctrl + Shift + !. For dollars use Ctrl + Shift + $. For percentages it’s Ctrl + Shift + %. The last two should be pretty easy to remember:
6. Lock cells with F4
When copying formulas in Excel, sometimes you want your input cells to move with your formulas BUT SOMETIMES YOU DON’T. When you want to lock one of your inputs you need to put dollar signs before the column letter and row number. Typing in the dollar signs is insane and a huge waste of time. Instead, after you select your cell, hit F4 to insert the dollar signs and lock the cell. If you continue to hit the F4 key, it will cycle through different options: lock cell, lock row number, lock column letter, no lock.
7. Summarize data with CountIF and SumIF
CountIF will count the number of times a value appears in a selected range. The first input is the range of values you want to count in. The second input is the criteria, or particular value, you are looking for. Below we are counting the number of stories in column B written by the selected author:
COUNTIF(range,criteria)
SumIF will add up values in a range when the value in a corresponding range matches your criteria. Here we want to count the total number of views for each author. Our sum range is different from the range with the authors’ names, but the two ranges are the same size. We are adding up the number of views in column E when the author name in column B matches the selected name.
SUMIF(range,criteria,sum range)
8. Pull out the exact data you want with VLOOKUP
VLOOKUP looks for a value in the leftmost column of a data range and will return any value to the right of it. Here we have a list of law schools with school rankings in the first column. We want to use VLOOKUP to create a list of the top 5 ranked schools.
The first input is the lookup value. Here we use the ranking we want to find. The second input is the data range that contains the values we are looking up in the leftmost column and the information we’re trying to get in the columns to the right. The third input is the column number of the value you want to return.
We want the school name, and this is in the second column of our data range. The last input tells Excel if you want an exact match or an approximate match. For an exact match write FALSE or 0.
9. Use & to combine text strings
Here we have a column of first names and last names. We can create a column with full names by using &. In Excel, & joins together two or more pieces of text. Don’t forget to put a space between the names. Your formula will look like this =[First Name]&” “&[Last Name]. You can mix cell references with actual text as long as the text you want to include is surrounded by quotes:
10. Clean up text with LEFT, RIGHT and LEN
These text formulas are great for cleaning up data. Here we have state abbreviations combined with state names with a dash in between. We can use the LEFT function to return the state abbreviation. LEFT grabs a specified number of characters from the start of a text string. The first input is the text string. The second input is the number of characters you want. In our case, we want the first two characters:
LEFT(text string, number of characters)
If you want to pull the names of the states out of this text string you have to use the RIGHT function. RIGHT grabs a number of characters from the right end of a text string.
But how many characters on the right do you want? All but three, since the state names all come after the state’s two-letter abbreviation and a dash. This is where LEN comes in handy. LEN will count the number of characters or length of the text string.
Now you can use a combination of RIGHT and LEN to pull out the state names. Since we want all but the first three characters, we take the length of our string, subtract 3, and pull that many characters from the right end of the string:
RIGHT(text string,number of characters)
11. Generate random values with RAND
You can use RAND() function to generate a random value between 0 and 1. D0 not include any inputs, just leave the parentheses empty. New random values will be generated every time the workbook recalculates. You can force it to recalculate by hitting F9. But be careful. It also recalculates when you make other changes to the workbook:
In this article you’ll learn, how to calculate number of days, weeks, months and years between 2 dates in Microsoft Excel.
To calculate the same, we’ll use INT, TODAY and MOD or we can use DATEDIF functions.
INT function, will get the whole number without decimal
MOD function, will divide the number by a divisor
TODAY function, will help us get the current date
DATEDIF function, will calculate difference between each pair of dates
Let’s take an example,
We have 2 dates,
Cell A1 containing 1st date and
Cell A2 containing 2nd date
To calculate the difference between years, use DATEDIF function as shown in the following formula:
Select the cell A3 and write the formula =DATEDIF (A1,A2,”Y”)
This function will return the value in years
To calculate the difference in months, use the DATEDIF function as shown in the following formula:
Select the cell A4 and write the formula =DATEDIF (A1,A2,”M”)
This function will return the value in months
To calculate the difference between days, use the DATEDIF function as shown in the following formula:
Select the cell A5 and write the formula =DATEDIF (A1,A2,”D”)
This function will return the value in days
OR
Use the “YEAR”, “MONTH”, “AND” and “DAY” functions as shown in the following formula:-
Select the cell A3 and write the formula to calculate the years
=YEAR(A2)-YEAR(A1)-(MONTH(A2)/12)
This function will return the no. of years in between 2 dates
Use the DATEDIF function to calculate the number of days over years:-
Select the cell A4 and write the formula to calculate the years
=DATEDIF(A1,A2,”y”)
This function will return the no. of years in between 2 dates
PS: A lot of site, avoid calculating date in Excel using DATEDIF function. The reason is “bugs”. DATEDIF functions don’t have any documentation in Excel Help file.
But, Microsoft is continuously implying this feature / formula in all new version.
In case if you also want to avoid DATEDIF function, you can use manual calculation. Like below,
=INT((TODAY()-A1)/365.25) & ” years , ” & INT(MOD((TODAY()-A1)/365.25,1)*12) & ” months and ” & INT(MOD((TODAY()-A1)/30.4375,1)*30.4375) & ” days”.
It will give day difference in Year Month and in days. You can use A2 in case of today, where A2 is the greater day that A1 and gives you Elapsed time between these 2 dates.
Have you ever had two sets of data on two different spreadsheets that you want to combine into a single spreadsheet?
For example, you might have a list of people's names next to their email addresses in one spreadsheet, and a list of those same people's email addresses next to their company names in the other -- but you want the names, email addresses, and company names of those people to appear in one place.
I have to combine data sets like this a lot -- and when I do, the VLOOKUP is my go-to formula. Before you use the formula, though, be absolutely sure that you have at least one column that appearsidentically in both places. Scour your data sets to make sure the column of data you're using to combine your information is exactly the same, including no extra spaces.
The formula: =VLOOKUP(lookup value, table array, column number, [range lookup])
The formula with variables from our example below: =VLOOKUP(C2,Sheet2!A:B,2,FALSE)
In this formula, there are several variables. The following is true when you want to combine information in Sheet 1 and Sheet 2 onto Sheet 1.
Lookup Value: This is the identical value you have in both spreadsheets. Choose the first value in your first spreadsheet. In the example that follows, this means the first email address on the list, or cell 2 (C2).
Table Array: The range of columns on Sheet 2 you're going to pull your data from, including the column of data identical to your lookup value (in our example, email addresses) in Sheet 1 as well as the column of data you're trying to copy to Sheet 1. In our example, this is "Sheet2!A:B." "A" means Column A in Sheet 2, which is the column in Sheet 2 where the data identical to our lookup value (email) in Sheet 1 is listed. The "B" means Column B, which contains the information that's only available in Sheet 2 that you want to translate to Sheet 1.
Column Number: If the table array (the range of columns you just indicated) this tells Excel which column the new data you want to copy to Sheet 1 is located in. In our example, this would be the column that "House" is located in. "House" is the second column in our range of columns (table array), so our column number is 2. [Note: Your range can be more than two columns. For example, if there are three columns on Sheet 2 -- Email, Age, and House -- and you still want to bring House onto Sheet 1, you can still use a VLOOKUP. You just need to change the "2" to a "3" so it pulls back the value in the third column: =VLOOKUP(C2:Sheet2!A:C,3,false).]
Range Lookup: Use FALSE to ensure you pull in only exact value matches.
In the example below, Sheet 1 and Sheet 2 contain lists describing different information about the same people, and the common thread between the two is their email addresses. Let's say we want to combine both datasets so that all the house information from Sheet 2 translates over to Sheet 1.
So when we type in the formula =VLOOKUP(C2,Sheet2!A:B,2,FALSE), we bring all the house data into Sheet 1.
Keep in mind that VLOOKUP will only pull back values from the second sheet that are to the right of the column containing your identical data. This can lead to some limitations, which is why some people prefer to use the INDEX and MATCH functions instead.
Like VLOOKUP, the INDEX and MATCH functions pull in data from another dataset into one central location. Here are the main differences:
VLOOKUP is a much simpler formula. If you're working with large data sets that would require thousands of lookups, then using the INDEX MATCH function will significantly decrease load time in Excel.
INDEX MATCH formulas work right-to-left, whereas VLOOKUP formulas only work as a left-to-right lookup. In other words, if you need to do a lookup that has a lookup column to the right of the results column, then you'd have to rearrange those columns in order to do a VLOOKUP. This can be tedious with large datasets and/or lead to errors.
So if I want to combine information in Sheet 1 and Sheet 2 onto Sheet 1, but the column values in Sheets 1 and 2 aren't the same, then to do a VLOOKUP, I would need to switch around my columns. In this case, I'd choose to do an INDEX MATCH instead. Let's look at an example. Let's say Sheet 1 contains a list of people's names and their Hogwarts email addresses, and Sheet 2 contains a list of people's email addresses and the Patronus that each student has. (For the non-Harry Potter fans out there, every witch or wizard has an animal guardian called a "Patronus" associated with him or her.) The information that lives in both sheets is the column containing email addresses, but this email address column is in different column numbers on each sheet. I'd use the INDEX MATCH formula instead of VLOOKUP so I wouldn't have to switch any columns around. So what's the formula, then? The INDEX MATCH formula is actually the MATCH formula nested inside the INDEX formula. You'll see I differentiated the MATCH formula using a different color here. The formula: =INDEX(table array, MATCH formula) This becomes: =INDEX(table array, MATCH (lookup_value, lookup_array)) The formula with variables from our example below:=INDEX(Sheet2!A:A,(MATCH(Sheet1!C:C,Sheet2!C:C,0))) Here are the variables:
Table Array: The range of columns on Sheet 2 containing the new data you want to bring over to Sheet 1. In our example, "A" means Column A, which contains the "Patronus" information for each person.
Lookup Value: This is the column in Sheet 1 that contains identical values in both spreadsheets. In the example that follows, this means the "email" column on Sheet 1, which is Column C. So: Sheet1!C:C.
Lookup Array: This is the column in Sheet 2 that contains identical values in both spreadsheets. In the example that follows, this refers to the "email" column on Sheet 2, which happens to also be Column C. So: Sheet2!C:C.
Once you have your variables straight, type in the INDEX MATCH formula in the top-most cell of the blank Patronus column on Sheet 1, where you want the combined information to live
Instead of manually counting how often a certain value or number appears, let Excel do the work for you. With the COUNTIF function, Excel can count the number of times a word or number appears in any range of cells. For example, let's say I want to count the number of times the word "Gryffindor" appears in my data set. The formula: =COUNTIF(range, criteria) The formula with variables from our example below:=COUNTIF(D:D,"Gryffindor") In this formula, there are several variables:
Range: The range that we want the formula to cover. In this case, since we're only focusing on one column, we use "D:D" to indicate that the first and last column are both D. If I were looking at columns C and D, I would use "C:D."
Criteria: Whatever number or piece of text you want Excel to count. Only use quotation marks if you want the result to be text instead of a number. In our example, the criteria is "Gryffindor."
Simply typing in the COUNTIF formula in any cell and pressing "Enter" will show me how many times the word "Gryffindor" appears in the dataset.
