Count amount of numbers in a cell? Works in Excel not Google Sheets??

[c.clavin]

Hi guys, I've used the formula '=SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9,0},)))' in Excel to count the amount of numbers occurring in a cell, but it doesn't seem to be working in Google Sheets. Is there a workaround that can be used? Thanks

 

[johnnyL]

How about:

New Pace Report All DealersV5.1.xlsm
	A	B	C
1	712ASD34	5
2	A123BG	3
3	999	3
4
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=COUNT(-MID(A1,ROW($1:$99),1))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[veyselemre]

Hi, try this;

=ArrayFormula(SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9,0},))))

=LEN(REGEXREPLACE(A1,"\D",""))

 

[veyselemre]

=LEN(REGEXREPLACE(""&A1,"\D",""))

 

[James006]

@veyselemre

If REGEXREPLACE is an UDF, thanks for posting the Function

 

[c.clavin]

I got it working using something like this;

(SUM(LEN(L6),-LEN(SUBSTITUTE(L6,2,))+SUM(LEN(L6),-LEN(SUBSTITUTE(L6,3,)))+SUM(LEN(L6),-LEN(SUBSTITUTE(L6,4,)))+SUM(LEN(L6),-LEN(SUBSTITUTE(L6,5,)))+SUM(LEN(L6),-LEN(SUBSTITUTE(L6,6,)))+SUM(LEN(L6),-LEN(SUBSTITUTE(L6,7,)))+SUM(LEN(L6),-LEN(SUBSTITUTE(L6,8,)))+SUM(LEN(L6),-LEN(SUBSTITUTE(L6,9,))))))

it definitely could have been easier with the Excel curly braces function.

 

[Fluff]

Did you try what veyselemre suggested in posts#3 & 4?

If REGEXREPLACE is an UDF

It's a Google Sheet function.

 

[James006]

Did you try what veyselemre suggested in posts#3 & 4?


It's a Google Sheet function.

Thanks a lot Fluff

 

[c.clavin]

=LEN(REGEXREPLACE(""&A1,"\D",""))

This is great, working perfectly thank you very much