By Joey Hodges, MBA
AcceleratingCFO Consultant
There is a lot of talk about using ChatGPT to write Excel formulas and countless articles touting the work that ChatGPT can do without entering Excel formulas. For example, this YouTube video states that you can 10x your Excel skills by using ChatGPT (https://youtu.be/JYtZ2zsdE_s ). Amazing what ChatGPT can do, right?
The problem is that you need to be able to ask the Chat GPT questions in a very specific manner with tremendous detail. I am a reasonably intelligent guy who has been using Excel since before Lotus 123. If you don’t know what Lotus 123 is, just Google it. I honestly found it very difficult to phrase questions just right with sufficient detail to get ChatGPT to write anything but the simplest formulas.
Now speaking of Google, let’s revisit productivity hacks. I’ve been using Google for over a decade to find some very powerful formulas that I can tweak to perform operations in my spreadsheets. Using this process, I am able to produce spreadsheets that make me look really smart, but I’m just capitalizing on Google. It’s like software developers using snippets of code in several projects. A software engineer friend has 100s of pieces of code that perform different operations that she just copies to a new project when she needs to perform that operation.
Let’s take a look at an example:
Type “excel numbers only from string” into Google. You will get several responses.
The first one that comes up for me involves VBA, and I don’t want to use VBA so I keep looking. The ExtendOffice choice, “How to extract numbers only from text string in Excel,” looks interesting. It shows a few different methods. The first method provides a formula “=SUMPRODUCT(MID(0&A5, LARGE(INDEX(ISNUMBER(–MID(A5, ROW(INDIRECT(“1:”&LEN(A5))), 1)) * ROW(INDIRECT(“1:”&LEN(A5))), 0), ROW(INDIRECT(“1:”&LEN(A5))))+1, 1) * 10^ROW(INDIRECT(“1:”&LEN(A5)))/10)”.
You can copy/paste this formula into your spreadsheet, change the A5 cell reference to the corresponding cell range in your spreadsheet, and Bingo! You can now extract just the numbers from a string of text.
Now go show off this formula and your brilliance. We want to hear what other gems you find on Google that save you time in Excel. And if you have figured out how to use ChatGPT to save time with Excel, we want to hear that too!
To get more in depth about this project, contact Joey at joey@acceleratingcfo.com.
