Count number of numbers in a single cell
I want to count the number of numbers in a single cell.
Example:
In cell A1, I have the following addition:
=12+45+23+51+10 which totals (and shows) 141.
In cell B1, I would like the see how many numbers have been added together, which means that there should be 5 (12 is a number, 45 another one, etc... and all together, there are 5 numbers in cell A1).
I know it seems to be a ridiculous question, but I scanned all the platforms for this issue and did not find any suitable solution. Tried all the LEN and LEN SUBSTITUTE alternatives out there, but somehow it does not work.
Thank you upfront for your help. Optimal solution would be a excel formula, alternatively VBA would also work.
Excel 2013 has a new function Formulatext() which returns the, well, formula text. Using that the Len() and Substitute() approach works.
=LEN(FORMULATEXT(A1))-LEN(SUBSTITUTE(FORMULATEXT(A1),"+",""))+1
- List only workdays in given dynamic month
- How to read SharePoint Online (Office365) Excel files into Python specifically pandas with Work or School Account?
- Difference between .Delete and .Clear in Excel VBA?
- Merging cells in Excel using Apache POI
- Having issues not including duplicate words randomly populating 3 columns in Excel
- Excel: Filter a sheet from value in another sheet
- Merge two cells keeping/adding formatting
- Output all cell colors from a range in Excel using Office Scripts
- Using EPPlus with a MemoryStream
- Creating an office script where it looks at the first column in each sheet and inserts a column based on the value in the first column
- Putting a concatenate inside of a count if
- GetMyFeesEstimate API Amazon vba
- "The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns" Error
- Excel fill numbers within a range
- VBA isString vs isNumeric
- How to use a cell range to color another one?
- Macro Help: Workbook to New Doc Copying Only the Cell Values
- Date format issue when I try to find the first occurrence of names with associated dates
- Excel VBA Autofilter Values Not Equal To Asterisk (*)
- How do I check If autosave is on and then switch it back on if it is not?
- Driving Excel from Python in Windows
- VBA Excel Cannot open external workbook
- TypeError while using Openpyxl to read file
- Excel VBA to hide rows if more than 10 blanks
- I have a list of lat/long points of interest, and lat/longs for public trans how to find addresses with have all three public trans?
- Excel: how to search by two rows and one column
- Excel to Word VBA The formatting does not apply to the correct text
- Select some adjacent and non-adjacent column ranges and save to pdf as if they were all adjacent
- How do I convert a single vertical row into multiple row array in Excel?
- Excel - Check existing digit count, if <10 then add prefix "100" to make it 10 digit