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.
Subscribe to:
Posts (Atom)