Another Blank Cell Issue in Excel
Excel has plenty of issues when it comes to correctly handling blank cells. Here is a solution to one of those issues.
(more…)Excel SEARCH and Wildcards
Excel has recently added three regex functions to handle text patterns, but did you know the old SEARCH function can use wildcards?
(more…)Shortcut to Delete a Word
This technique works best in Word, but you can also use it in Excel. There is a keyboard shortcut to delete the current word. The shortcut is Ctrl + Backspace. When creating a formula in Excel that shortcut has another use that I covered in the blog post below. Note: in Excel you may now need to use Ctrl + Shift + Backspace. Whichever works - it can be useful. https://a4accounting.com.au/keyboard-shortcut-to-return-to-the-formula/ When you are in a text box (or in…
Bullet Points in Excel
Bullet points are more a Word or PowerPoint feature, but you can use them in Excel. There are a few tricks required to use them though.
(more…)Updating Sentences in Excel with New Techniques
I wrote a blog post back in 2012 and showed how to use checkboxes to build a paragraph based on sentences you select. I thought I would update it with recent new techniques.
(more…)Excel Text Number Custom Function
If you need to use numbers in a text string you typically need to use either the TEXT function, the DOLLAR or the FIXED function. To make it easier we can create our own function.
(more…)Creating Sentences in Excel
If you want to automate journal descriptions or create sentences the TEXTJOIN function is your friend. It can combine words and insert the spaces for you.
(more…)Creating A Text String of Lowercase And Uppercase Letters
As a follow on from last week’s post you may want to create a text string of all of the lowercase and uppercase letters. Again we can combine some Excel functions to achieve this.
(more…)Listing Alphabetic Letters in Excel
If you need a listing of the letters of the alphabet you can combine a couple of functions to provide the list.
(more…)SUMIFS Magic Solution
A client recently had a problem. He was chasing a formula that identified if a text string contained one of three words. He wanted to base an allocation on finding those three words. SUMIFS offers a solution.
(more…)Solving a Conditional Summing Text Problem in Excel
I was checking out an old Excel book Excel Outside The Box by long time Excel MVP Bob Umlas and noticed he used the N function in his SUMPRODUCT functions. I then realised why. It converts text to a zero. That gets around an issue with adding up ranges that contains text, thanks again Bob.
(more…)Custom Function to Reverse a String in Excel
When I started learning Python, I saw it had a Reverse function and thought, “I could do that in Excel.”
(more…)TEXTBETWEEN function in Excel
Excel has new TEXTBEFORE and TEXTAFTER functions. It doesn’t have a TEXTBETWEEN function. Let’s make one.
(more…)Extract Text Before a Number in Excel
Excel’s new TEXTBEFORE function simplifies extracting text from the left. In this example I share how to extract all the text before a number in a code.
(more…)Adding up Text Numbers In Excel Another Technique
If you have a list of numbers that are a text numbers or a combination of text numbers with real numbers there is a technique I covered in this blog post to add them up. But if the range also contains text then the technique won’t work. There is the work around. The solutions below work in the subscription version of Excel. Check the comments section below for a solution for all versions.
(more…)Dynamic Arrays and a Book Index
Years back when I wrote my Excel book, I had to create an index for the book. I shared the file I used including the macro in this post. Recently I thought dynamic arrays could do much of the work for this.
(more…)Financial Year Month in a Pivot Table
I wrote an article years ago explaining how to use a related table to handle financial years in Excel Pivot Tables. You can read the article here. If you only want the months in financial year order you can just add an extra column to your table.
(more…)Treating text as zero in Excel
Let’s say you are getting inputs you can’t control and in some cases you get text and others you get numbers. You want the numbers, but you need to treat text as zero. Here’s the easy way to do that.
(more…)Summing Text Numbers in Excel Updated
Fix dd.mm.yy date format
On a recent Webinar I was asked a question about an unusual date structure that was imported. The structure dd.mm.yy was not recognised by Excel as a date. Here is formula that fixes it. Below is an example of the date issue. The formula in cell B2 is =SUBSTITUTE(A2,".","/")*1 As you can see the dates in column A are left aligned. That is a clue that they are not recognised as dates in Excel. Dates are right aligned. The SUBSTITUTE…
Another plural technique for Excel
Recently I found an interesting way to handle plurals in Jordan Goldmeier’s book on dashboards. (more…)
Putting Emphasis on Words in Excel
Did you know that you can format individual words and letters differently within an Excel cell or text box? (more…)
Extracting initials in Excel
If you have a system that uses initials to identify people then being able to extract initials from a first name and last name combination can be handy. A formula can automate the process and there is also a quick, manual way to do it. (more…)
Handling plurals with the IF function
Sometimes when creating text you need to handle plurals correctly. The IF function makes it easy. (more…)
Free Excel Webinar Recording – Text Functions explained and demonstrated
My free Excel webinar for May 2018 covered Text functions. Download the materials using the button below and watch the video. You know how well Excel handles numbers, but not everyone knows that Excel has built-in functions and features to work with text as well. This session covers Excel’s text functions and features, in it you will learn the different techniques to split text techniques to extract text from text how to easily join text techniques for tweaking text for…
PivotTable Combinations Trick
I did a recent post on using Power Query to create an all combinations list. I found another technique on chandoo.org to create that list using a PivotTable. (more…)
Converting Text Numbers and Reversing Signs in Excel
Converting multiple text numbers into real numbers or reversing the sign on multiple numbers is easy in Excel if you know how to use Paste Special. (more…)
Converting a Tricky Date Format
I had a question on another post on how to convert Nov 21, 2014 into a date Excel recognises. The solution involves six functions working together.
Converting a number to a text number in Excel
If you need to convert a number into a text number within a formula there are a couple of ways to achieve this, but one way is a lot easier.
The Handy Functions in Excel – LEFT and RIGHT
The LEFT and RIGHT functions are great for extracting leading or trailing characters from a text string. Did you know their default setting is handy too? (more…)