Recent Comments
Patrick O'Beirne, Ex… on Excel formula to test if a wor… Peter Bartholomew on Excel formula to test if a wor… Rick Rothstein on Excel formula to test if a wor… Patrick O'Beirne, Ex… on Excel formula to test if a wor… Peter Bartholomew on Excel formula to test if a wor…
Category Archives: Excel/VBA
ChatGPT 3.5 Excel script
Everybody seems to want to use AI to create Excel formulas, so here is a way of building an interface to the OpenAI ChatGPT 3.5 model. Leila Gharani has already published a video and the Office Script code to call … Continue reading
Posted in Excel/VBA
Tagged Excel, scripting, A.I., ChatGPT, Officescript, Typescript, openai
Leave a comment
Excel formula to test if a word contains certain letters
Victor Momoh in the Global Excel Summit, to start a discussion on readability, provocatively used MMULT in a formula to determine whether a word contains all five vowels. As Victor said, in real life he’d use a simple SUM:=SUM(1*ISNUMBER(SEARCH(MID(“aeiou”,SEQUENCE(1,5),1),A2))) SEQUENCE generates … Continue reading
Posted in Excel/VBA
7 Comments
WordXLe: Wordle in Excel
An Excel version of the word game Wordle, with helper sheets. Continue reading
Get the real used range of a worksheet
For finding the last used cell of a sheet:
1) The .Find method omits the rows hidden by filtering.
2) Shapes can be placed outside the used range.
3) For reasonably sized sheets, the MAX formula is considerably faster than looping if there are many blank rows below the data. Otherwise, looping comes out about equal.
4) For sheets with more than 45M cells, the looping method is the only one that works.
5) Any data cell found needs to be adjusted for any merged range that it is part of. Continue reading
Posted in Excel/VBA
Leave a comment
Querying Text/CSV files in VBA, method #3: Power Query
The module PowerQueryText.bas contains VBA code to query text/csv/tab delimited files and return tables to worksheets. Continue reading
Querying Text/CSV files. Method #2: ADO Recordset
The module ADOQueryText.bas has the functions to read a delimited text file into a worksheet. It uses an ADODB recordset to perform the query. Continue reading
Querying Text/CSV files. Method #1: Querytable
The legacy Get Data from Text/CSV command in Excel creates a Querytable. This is pretty good for most purposes, except… Continue reading
Reading Unicode file contents as text
This is a short update on my post of Sep 28, 2020 on reading Unicode text in VBA. I am reposting the module as ADOStream.bas Continue reading
Hello (the rest of the) World – reading data in other languages
Have you ever opened a text file in Excel and found that the characters looked weird or corrupted? The Wikipedia entry for mojibake says ‘In German, Buchstabensalat (“letter salad”) is a common term for this phenomenon, and in Spanish, deformación … Continue reading
A look back to Eusprig 2019
The 20th conference of the European Spreadsheet Risk Interest Group was held in Browns of Covent Garden, London on 11-12 July 2019, Thursday 11 July 2019 Developing Excel Thought Leadership David Lyford-Smith, ICAEW, Chartered Accountants’ Hall This described the development … Continue reading
Patrick O'Beirne @ sysmod 