Need a LEN and/or CONCATENATE Guru

[Guest]

I am trying to sum the LEN function in columns of data. The data may be well
over 100 cells long. For example, for a column of 4 cells they may contain
45,1,21,25. The answer I want here is 7 (the total number of characters). The
logical formula I would LIKE to use would be SUM(LEN(A1:A4)), but that does
not work since LEN does not like ":". The other way I have tried is to
CONCATENATE the cells, then use LEN. This works if I hold the CONTROL hold
down while selecting each individual cell to get the formula
LEN(CONCATENATE(A1,A2,A3,A4)), and gives me the desired result of 7. Did a
mention my data may be hundreds of cells long? If I hold CONTROL down and
scroll the length of the list, I get LEN(CONCATENATE(A1:A4)), and CONCATENATE
does not like ":" either. Maybe another Data or Statistical formula will
work? HELP!!!

 

[Guest]

Create a helper column which calculates the number of characters in each
cell, and then sum those numbers.

Example: Assume you have a column of numbers, A1:A1000. Enter =LEN(A1) in
cell B1 and filling down to B1000 will give you the number of characters in
each cell from A1:A1000. Then =SUM(B1:B1000) gives you your answer.

Dave

 

[Sandy Mann]

Enter you first formula =SUM(LEN(A1:A4)) as an array formual by pressing and
holding down the Control + Shift keys while you press Enter

If you do it correctly Excel will put { } around the formula.

An alternative is

=SUMPRODUCT(LEN(A1:A5))

entered normally
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

Actually, a more efficient method than my earlier response is:
=SUMPRODUCT(LEN(A1:A1000))

Dave

 

[PCLIVE]

You may consider using a helper column.

Example: In B1, use a Len formula and copy down as needed.
=LEN(A1)

Then Sum from there.

=SUM(B:B)


HTH,
Paul

 

[Guest]

Thank you very much, Dave. I actually was already using your first method,
but having it in the next column was distracting from the raw data when I was
presenting it to others, not to mention blowing up my worksheet that already
had 10 columns of raw data. So I had moved the LEN function column to below
my data A1:A1000, and did SUM(A1005:A2004), then boldened the key row. I
still would have to keep answering questions like, "What is that data down
there?". My next step was going to move my helper columns to a different
worksheet. Anyway, your solution is very much appreciated.

 

[PCLIVE]

Just move your helper column way off to the right. You can also hide the
column. Right-click it and select Hide.

Good luck.
Paul

 

[Guest]

The SUMPRODUCT function perfect solution for my problem, but I had not used
HIDE before. Another tool in the bag that may be useful in the future. Thanks.

 

[Harlan Grove]

I am trying to sum the LEN function in columns of data. The data
may be well over 100 cells long. For example, for a column of 4
cells they may contain 45,1,21,25. The answer I want here is 7
(the total number of characters). The logical formula I would LIKE
to use would be SUM(LEN(A1:A4)), but that does not work since LEN
does not like ":". . . .

What version of Excel are you using? With the following in A1:A10,

1
10
1186
26
16
28
11
1
1
1

the formula

=SUMPRODUCT(LEN(A1:A10))

returns the correct result 18 on my PC. LEN, like ALL other functions,
has no syntactic trouble processing multiple cell range reference
arguments. Are you certain there were no typos in your formula,
perhaps using semicolon rather than colon?

Anyway, if these were all positive integer values and you just can't
get LEN to work, there's always

=SUMPRODUCT(INT(LOG10(YourRange))+1)