Counting characters within a cell

[Alco Engineer]

In order to help update an existing spreadsheet, I want to be able to count
either the number of numbers in a given cells, or alternatively the number
off addition signs.

i.e.
A1 =125+65+122+35

In A2 I'd like to be able to count either the number of numbers(eg. 4
numbers in this example where 125 is a single number), or the number of +'s
(and then add one) so that I can calculate the delivery fees based on the
number of deliveries (which is what the cell represents)

Changing the spreadsheet to hold all the data in seperate cells while being
easier from this point of view is not an option.

Thanks

 

[Sheeloo]

If A1 contains 125+65+122+35 as text (not a formula =125+65+122+35 showing
cell value as 347) then following in B1 will give the count of + symbol plus
1;
=LEN(A1)-LEN(SUBSTITUTE(A1,"+",""))+1

 

[Alco Engineer]

Thanks Sheeloo, but it isn't a text cell.

Instead the cell is an addition formula (i.e =125+55+36) and displays 216 as
the result.

I need to be able to determine how many numbers have been used to calculate
that result. Is there any way I can do this?

 

[Sheeloo]

You can not do this using a formula. You will have to use a macro...

I can write that for you if you want...
Need more info though...
Can the formula be in any cell of the sheet or only in specific columns/rows?
Do you want the count in the same sheet (where?) or in a separate sheet?
etc. etc...
The macro below will put the formula in A1-A100 in B1-B100 of sheet 2

 

[Alco Engineer]

Hey mate,

Thanks anyway but it was just an attempt to help save some time. I think
that operating the Macro would take more effort than counting and entering
the numbers by hand.

I appreciate you help anyway.

 

[Spiky]

You could also write a UDF instead of a macro. Morefunc already has
one written, you could download and install it. Actually, it would
take 2 of their's, named FORMULATEXT and WORDCOUNT. Or maybe just
FORMULATEXT if you counted all the "+".