June 28, 2023

EXCELerating CFO — Another Productivity Booster


Share this

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?

Excellerating CFO — Another Productivity Booster by Joey HodgesThe 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.

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.

Share this

Posts That Might Interest You

This site uses analytics, cookies and/or other 3rd party technologies that may have access to your data, which are used to provide a quality experience. If you do not agree, opt out and we will not load these items, however, necessary cookies to enable basic functions will still load. Visit our Privacy Policy to learn more.
Contact Us:
Email us at info@acceleratingcfo.com for requests involving data we collect. View our Privacy Policy for more info.
Opt In / Out:
To change your opt in settings, please click here to opt out or in. Or, close this popup.