Thoughts on…
It can be a big help if your data in Excel is easy to read (avoiding errors in calculations and or data analysis). I know there are more ways than one to achieve that goal, in this article I’ll show you some code that handles the solution without using conditional formatting. It’s a rough and simple solution…
Continue reading “Improving readability of worksheet data in Excel”
Sometimes one has to do a lot of mouse clicking… By repetitive clicking and small movements of your hands and other joints you could end up with some nasty RSI (Repetitive Strain Injury) and that ain’t worth it!!! So beat RSI (or your enemy while gaming) by using VBA and Excel to automate some excessive mouse clicks!
Continue reading “Prevent RSI and automate mouse clicks by using excel”
Personally I’ve been using Excel for storing phone numbers for almost twenty years now. Every now and then I don’t recognize the phone number so I need to search in my Excel sheet looking up who called me. After ten years and 500 phone numbers plus, the information stored in the Excel sheet is ‘poluted’ with hyphens and spaces. So in this article I’ll try to explain how to clean up messy telephone numbers by removing spaces and separators in Excel?
Continue reading “Cleaning phone numbers in Excel or OO Spreadsheet”
In this period of the year most of us are starting to make plans for next year, Excel is an incedibly powerfull tool that can help you visualize your plans. Using Excel’s conditional formatting you can colorize the weekends and make them standout more than the other days of the week or vice versa. This article is an How to colorize the weekends in Excel using conditional formatting.
To colorize the weekends in Excel using conditional formatting, based upon the date in column B, you can use the following formula:
| =IF(OR(WEEKDAY($B2)=1;WEEKDAY($B2)=7);1;0) |
To create a rule using conditional formatting:
Suggestions for improving this article are welcome, please let me know and drop me a line.
I received an e-mail from Marc, in which he asked me whether it is possible to split numbers and text in Excel. The answer to the solution is based upon determining the first position of a number in a string, you can do that by using the following formula.
| =MIN(FIND({0;1;2;3;4;5;6;7;8;9};A1&{0;1;2;3;4;5;6;7;8;9})) |
The next trick is to determine the text within a string:
A101 will become A when using the following formula
| =LEFT(A1;(MIN(FIND({0;1;2;3;4;5;6;7;8;9};A1&{0;1;2;3;4;5;6;7;8;9}))-1)) |
101A will become A when using the following formula
| =RIGHT(A2;(MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9})))) |
To determine the numbers within a string:
A101 will become 101 when using the following formula
| =RIGHT(A1;LEN(A1)-MIN(FIND({0;1;2;3;4;5;6;7;8;9};A1&{0;1;2;3;4;5;6;7;8;9}))+1) |
101A will become 101 when using the following formula
| =LEFT(A2;LEN(A2)-MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9}))) |
You might want to convert the text string to numbers by using the following formula.
| =VALUE() |
A101 will become numeric value 101 when using the following formula
| =VALUE(RIGHT(A1;LEN(A1)-MIN(FIND({0;1;2;3;4;5;6;7;8;9};A1&{0;1;2;3;4;5;6;7;8;9}))+1)) |
101A will become numeric value 101 when using the following formula
| =VALUE(LEFT(A2;LEN(A2)-MIN(FIND({0;1;2;3;4;5;6;7;8;9};A2&{0;1;2;3;4;5;6;7;8;9})))) |
Suggestions for improving this article are welcome, please let me know and drop me a line.