counting characters in a cell

R

ram

I have the following formula in cell A1 =10+10+10
i would like cell B1 to have a formula that counts the number of + signs in
cell A1. In this example it would return 2

I would like cell C1 to have a formula that counts the numbers in cell A1.
In this case it would be 3

Thanks in advance for your help
 
R

RagDyeR

Caveat -


This should be used in XL02 or later.
Earlier versions may crash when copying this type of formula containing cell
to other WBs.


Start my creating a 'named' formula that will return the actual formula from
A1;


Click in B1, then,
<Insert> <Name> <Define>,
In the "Names In Workbook" box, enter a short name, say
frmla
for formula.
In the "Refers To" box, replace whatever's there with this formula:
=GET.CELL(6,A1)
Then <OK>


What you have now is a 'relative' *named formula* that when entered in *any*
cell, will return the contents of the cell (text, data, formulas) from the
*previous* column.


So, if you enter

=frmla

in B1, you'll see the formula displayed from A1 (=10+10+10)

So, *now*, enter this in B1:

=LEN(frmla)-LEN(SUBSTITUTE(frmla,"+",""))

And this in C1:

=B1+1
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================



I have the following formula in cell A1 =10+10+10
i would like cell B1 to have a formula that counts the number of + signs in
cell A1. In this example it would return 2

I would like cell C1 to have a formula that counts the numbers in cell A1.
In this case it would be 3

Thanks in advance for your help
 
Top