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

c.clavin

Board Regular
Joined
Mar 22, 2011
Messages
130
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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 :)
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
How about:

New Pace Report All DealersV5.1.xlsm
ABC
1712ASD345
2A123BG3
39993
4
Sheet1
Cell Formulas
RangeFormula
B1:B3B1=COUNT(-MID(A1,ROW($1:$99),1))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Hi, try this;
Excel Formula:
=ArrayFormula(SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9,0},))))
Excel Formula:
=LEN(REGEXREPLACE(A1,"\D",""))
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,226,693
Messages
6,192,466
Members
453,726
Latest member
JoeH57

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top