I have Google Sheets where I summarize my spendings based on categories.

For example "Online shopping" - =100 52 35 841

We can agree on there being 4 terms in this formula. That symbolize that I've made 4 transactions.

Ex.2 =18 45 124 1045-16

I got 16 back (maybe I made a part-return or got paid for something) This time it is 5 terms/transactions.

With the LEN SUBSTITUTE function Google can "count" the amount of integers in that cell.

In ex. 2 that would return 13. (1,8,4,5...)

I want to count every term in the summery. So that the return is 5 for ex. 2 and 4 for example 1.

When that is possible it should be possible to count the amount of numbers with decimal representation. I.e. =25,45 109,05-34,5 should result in 3 transactions.

Also =12 should result in 1 transaction.

CodePudding user response：

try:

=INDEX(IFERROR(1/(1/LEN(REGEXREPLACE(A1:A, "[0-9\.,=]", ))) 1))

update:

=IFERROR(1/(1/LEN(REGEXREPLACE(FORMULATEXT(A15), "["" a-z0-9\.,=]", ))) 1)

CodePudding user response：

If the only transactions are or -, it's just the count of those symbols plus one.

Suppose your original strings were in A2:A. Place the following in B2 of an an otherwise empty B2:B (or in the Row-2 cell of any other open column):

=ArrayFormula(IF(A2:A="",,LEN(REGEXREPLACE(A2:A,"[^ -]","")) 1))

This one formula will process all entries in the range A2:A.

If you just want to process one cell instead of a contiguous range, you can use this formula, replacing A2 with your individual cell reference:

=LEN(REGEXREPLACE(A2,"[^ -]","")) 1

Either formula is just using REGEX to replace every character except or - and then returning the LENgth of the remaining string plus one.

CodePudding user response：

I found a custom solution based on @player0 answer, help and comments!

=IFERROR(1/(1/(1 LEN(REGEXREPLACE(FORMULATEXT(A1), "["" 0-9\.,=]", )))))

The 1 LEN() (instead of 1/(1/LEN()) 1) is required to solve the problem where I only had one value. i.e. =25 to return 1 instead of nothing.