By Neil Shorney, Director, Naturally Sales Ltd.
When we have an untidy data last for postal mailings, it can really affect the customer's
perception of us as a company: missing capitals, too many capitals or poorly laid-out addresses can all make mailing labels look untidy. At the beginning of September, we looked at the TRIM function to remove erroneous spaces from words. Today we will look at three functions to correct the case of letters:
UPPER(ref) - this will change all characters to upper case
LOWER(ref) - this will change all characters to lower case
PROPER(ref) - capitalises the first character of each word
Using a combination of these functions, we can tidy our data for professional-looking mailings.
Wednesday, 28 August 2013
Tuesday, 20 August 2013
Identifying cells by text length
By Neil Shorney, Director, Naturally Sales Ltd.
I received this email from a recent student on our live e-learning recently, and thought it would make a useful topic for an e-tip:
“I work a lot with mailing lists, and problems arise when the company and address fields contain more than 40 characters. Is there a way to indicate these fields when they contain too many characters in such a way that I can filter on this information? Right now I am simply eyeballing the list and making changes but I may receive lists with 10,000 entries!”
I suspect this might not be a unique problem, and there are two ways we can look at this problem, depending on where the data comes from. If the data already exists in the spreadsheet, then we can use a formula to indicate those cells which match certain criteria in order to sort by these cells; in this case those which contain more than 40 characters.
I'd recommend typing the formula into a new column, by which you can then filter to highlight those records you're identifying. To highlight cells which contain more than a certain number of characters, you can use the LEN function (short for ""length""). It works like this:
=LEN(cell_ref)
You can combine this with an IF statement to highlight certain conditions. For example, if you want to know whether C14 has more than 40 characters, you could type the following formula into, for example, D14:
=IF(LEN(C14)>40,""Too long"","""")
...which would give a blank cell if less than 40, or say ""Too long"" if greater than 40.
Why not try this with other criteria?
The next step is to remove those characters after the fortieth in a cell. That's a topic for next time, then in the following e-tip we'll look at how to prevent users being able to enter more than 40 characters in the first place!
I received this email from a recent student on our live e-learning recently, and thought it would make a useful topic for an e-tip:
“I work a lot with mailing lists, and problems arise when the company and address fields contain more than 40 characters. Is there a way to indicate these fields when they contain too many characters in such a way that I can filter on this information? Right now I am simply eyeballing the list and making changes but I may receive lists with 10,000 entries!”
I suspect this might not be a unique problem, and there are two ways we can look at this problem, depending on where the data comes from. If the data already exists in the spreadsheet, then we can use a formula to indicate those cells which match certain criteria in order to sort by these cells; in this case those which contain more than 40 characters.
I'd recommend typing the formula into a new column, by which you can then filter to highlight those records you're identifying. To highlight cells which contain more than a certain number of characters, you can use the LEN function (short for ""length""). It works like this:
=LEN(cell_ref)
You can combine this with an IF statement to highlight certain conditions. For example, if you want to know whether C14 has more than 40 characters, you could type the following formula into, for example, D14:
=IF(LEN(C14)>40,""Too long"","""")
...which would give a blank cell if less than 40, or say ""Too long"" if greater than 40.
Why not try this with other criteria?
The next step is to remove those characters after the fortieth in a cell. That's a topic for next time, then in the following e-tip we'll look at how to prevent users being able to enter more than 40 characters in the first place!
Monday, 5 August 2013
Rounding Numbers
By Neil Shorney, Director, Naturally Sales Ltd.
Excel has a number of ways to round numbers. Today we'll look at three of these methods, and we'll re-visit the topic in a future edition.
The first we'll look at is the ROUND function. This rounds a decimal to the number of digits specified, and is constructed as follows: =ROUND(number, no. of decimals). For example, =ROUND(3.141592654,3) would give 3.142. Remember, of course, that these numbers don't have to be built into the formulas - they can be cell references containing numbers instead.
Maybe, though, you specifically want to round up or down. The formulas for these are constructed in the same way. The ROUNDUP function will round up to the number of decimals specified and ROUNDDOWN will round down in the same way.
As always, if you'd like further help with these, send an email to excel-help@nsales.co.uk and we'll get back to you.
Excel has a number of ways to round numbers. Today we'll look at three of these methods, and we'll re-visit the topic in a future edition.
The first we'll look at is the ROUND function. This rounds a decimal to the number of digits specified, and is constructed as follows: =ROUND(number, no. of decimals). For example, =ROUND(3.141592654,3) would give 3.142. Remember, of course, that these numbers don't have to be built into the formulas - they can be cell references containing numbers instead.
Maybe, though, you specifically want to round up or down. The formulas for these are constructed in the same way. The ROUNDUP function will round up to the number of decimals specified and ROUNDDOWN will round down in the same way.
As always, if you'd like further help with these, send an email to excel-help@nsales.co.uk and we'll get back to you.
Tuesday, 23 July 2013
Filling data down multiple sheets
By Neil Shorney, Director, Naturally Sales Ltd.
There are three ways to copy data and formulae down a spreadsheet.
For small amounts of data you can use a copy and paste commands.
Filling down a number of cells within one worksheet the easiest way is to drag the content.
In Excel 2003 and Excel 2007, this will automatically fill the data as far as the lowest data in any column to the left. In Excel 2010 this will fill as far as the lowest data anywhere in the spreadsheet. This slight difference in behaviour can be tricky when upgrading to 2010.
We look at more Excel shortcuts in Introduction to Microsoft Excel.
There are three ways to copy data and formulae down a spreadsheet.
For small amounts of data you can use a copy and paste commands.
Filling down a number of cells within one worksheet the easiest way is to drag the content.
- Select the cell you want to copy.
- At the bottom right corner of the cell there will be a small black square. Hover the most over the square until the cursor becomes a black cross.
- Click the left mouse button and drag the content as far as you wish.
In Excel 2003 and Excel 2007, this will automatically fill the data as far as the lowest data in any column to the left. In Excel 2010 this will fill as far as the lowest data anywhere in the spreadsheet. This slight difference in behaviour can be tricky when upgrading to 2010.
We look at more Excel shortcuts in Introduction to Microsoft Excel.
Monday, 8 July 2013
Finding text within a cell part 2: the FIND function
By Neil Shorney, Director, Naturally Sales Ltd.
| Continuing our theme of finding text within a cell, we’ll look now at the FIND function. The FIND function allows us to see where in a cell’s text a certain string is found. For example, if we look for the letter “t” within the word “Date”, it would return a “3”, because “t” is the third letter of “date”. It’s constructed like this: =FIND(text,within_cell), or =FIND(“t”,A3) where A3 contains the word “Date” If Excel is unable to find the text you’re looking for, it will return an error message. In a future e-tip, we’ll look at how we can combine the FIND and IFERROR functions to see whether or not certain cells contain certain words. |
Tuesday, 2 July 2013
Finding text within a cell: the IFERROR function
By Neil Shorney, Director, Naturally Sales Ltd.
Many people are familiar with using VLOOKUP, INDEX/MATCH or similar lookup formulas to find cell values from a list. However, when we start trying to find a text string within a cell, or to see whether a cell contains certain text or not, things become more difficult.
Unfortunately, Excel doesn't give us a formula to ask it to find cells which contain certain text, along with other text, in the cell body. However, there are techniques we can use. This will be a 3-part e-tip as we address this useful topic. The first part is to learn the IFERROR function.
The IFERROR functions allows us to substitute an error value in a cell for something else of our choosing, and it works by wrapping around the formula you're creating which may (or may not) return an error value. If works as follows:
=IFERROR(formula,value_if_error)
for example:
=IFERROR(SUM(A1:A10),0)
...which will return the sum of A1:A10 unless that sum is an error value, in which case it will display 0 instead of the error.
Struggling to see how this will help you find certain text within a cell? Look out for part 2 coming in a fortnight's time as we take this a stage further.
Many people are familiar with using VLOOKUP, INDEX/MATCH or similar lookup formulas to find cell values from a list. However, when we start trying to find a text string within a cell, or to see whether a cell contains certain text or not, things become more difficult.
Unfortunately, Excel doesn't give us a formula to ask it to find cells which contain certain text, along with other text, in the cell body. However, there are techniques we can use. This will be a 3-part e-tip as we address this useful topic. The first part is to learn the IFERROR function.
The IFERROR functions allows us to substitute an error value in a cell for something else of our choosing, and it works by wrapping around the formula you're creating which may (or may not) return an error value. If works as follows:
=IFERROR(formula,value_if_error)
for example:
=IFERROR(SUM(A1:A10),0)
...which will return the sum of A1:A10 unless that sum is an error value, in which case it will display 0 instead of the error.
Struggling to see how this will help you find certain text within a cell? Look out for part 2 coming in a fortnight's time as we take this a stage further.
Monday, 24 June 2013
Fixing errors in data sets
By Neil Shorney, Director, Naturally Sales Ltd.
In large data sets, particularly those downloaded from an external database, it's common to find errors which need tidying. Often these errors aren't errors at all, but simply the way Excel displays numbers within the spreadsheet layout. Today, we'll look at 2 such "errors" which have come up in real examples from recent students on our classes.
Error 1: the hash-tag error: ########
This is a common sight in downloaded spreadsheet data, and one which is easily dealt with. The problem here is not an error, but simply a number (including dates, currencies, percentages, etc.) which is too big for the cell. To view the cell content correctly, resize the column by double-clicking the mouse in the column headers between the one you're in and the one after (i.e. if your error is in column C, double-click between C and D) and the column will expand to fit the longest data.
Error 2: the E error: 5.35E+08
Another common sight, this "error" particularly occurs where you have a long number in a cell with more than 6 characters. It does not occur on dates or currencies, and the cause is the same as the previous example - the number is too big for the cell, yet Excel is attempting to give you a little more information about the number, showing how long the number is (after the +) and the first few digits (535). Much of the time, the method described in section 1 (above) will also work here. Sometimes, however, an extra step may be required. To display properly, the cells should be formatted as "numbers", whereas the default is "general". Before resizing the column, highlight the cells, right-click and format as a number (you may need to adjust the number of decimal places).
In large data sets, particularly those downloaded from an external database, it's common to find errors which need tidying. Often these errors aren't errors at all, but simply the way Excel displays numbers within the spreadsheet layout. Today, we'll look at 2 such "errors" which have come up in real examples from recent students on our classes.
Error 1: the hash-tag error: ########
This is a common sight in downloaded spreadsheet data, and one which is easily dealt with. The problem here is not an error, but simply a number (including dates, currencies, percentages, etc.) which is too big for the cell. To view the cell content correctly, resize the column by double-clicking the mouse in the column headers between the one you're in and the one after (i.e. if your error is in column C, double-click between C and D) and the column will expand to fit the longest data.
Error 2: the E error: 5.35E+08
Another common sight, this "error" particularly occurs where you have a long number in a cell with more than 6 characters. It does not occur on dates or currencies, and the cause is the same as the previous example - the number is too big for the cell, yet Excel is attempting to give you a little more information about the number, showing how long the number is (after the +) and the first few digits (535). Much of the time, the method described in section 1 (above) will also work here. Sometimes, however, an extra step may be required. To display properly, the cells should be formatted as "numbers", whereas the default is "general". Before resizing the column, highlight the cells, right-click and format as a number (you may need to adjust the number of decimal places).
Subscribe to:
Posts (Atom)