Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am trying to use this function =LEN() to count the characters of 1000+ cells, but I can't find a way of just copy-paste it and make it efficient. If I write the cell id manually =LEN(A1) it works pretty fine, but I want to write something like the following, so I can copy-paste it a thousand times: (Now, bare with me, I've never used excel before and I know next to nothing of its syntax)

=LEN(A*current_row*)

I just want to get the current row, I don't care for the current column. Thanks very much.

Oh, and just in case you know a different way to count the characters of all the different cells on a row and having the results not summed in a big number, please, I'm all ears and eyes.

share|improve this question
    
@moses But this is a software question, not a programming question. –  AthomSfere Aug 28 '13 at 0:26
    
As an aside: note that formulas are changed automatically when you drag them to another cell. –  Arjan Aug 28 '13 at 19:57
    
@Arjan sorry, should have rephrased. Migration is what I meant. I've asked loads of Excel questions there and have always had success at getting thorough answers. But alas, there are some good answers here so I retract my suggestion! –  Moses Aug 28 '13 at 20:00
    
Even then, @moses, just mentioning it often confuses question askers. People who can vote will vote, if they think it's required. No need to comment for them. See also “Belongs on” comments. Let's clean up our comments? –  Arjan Aug 28 '13 at 20:01
1  
Use the fill handle to fill the formula down or to the right: office.microsoft.com/en-us/excel-help/… –  Excellll Aug 28 '13 at 20:51
add comment

4 Answers

How about

=SUM( LEN(row_number:row_number) )

e.g., =SUM(LEN(1:1)) for Row 1.

Excel uses colons to express ranges.  You can experiment with this by typing something like C3:D7 into the “Name Box” (to the left of the Formula Bar):

                                                “C3:D7” in the Name Box

and then pressing Enter.  You will get this result:

                                                Range C3:D7 selected

If you say just C:D, you select all of columns C and D.  Similarly, 3:7 selects rows 3 through 7.  1:1 simply selects row 1.  I believe that the rest of the formula is straightforward –– compute the LEN of each cell in the row, then compute the SUM of those lengths.

This must be entered as an array formula, which means you must press Ctrl+Shift+Enter when you enter it, and it will then appear in the formula bar with curly braces around it, like {=SUM(LEN(1:1))} –– but don’t type the braces yourself.

share|improve this answer
    
1) Is row_number : row_number a typo, or you mean that I should write the row number two times? Because, sometimes that gives me an error. 2) Do I have to manually enter the number of the row? If so, that defeats the purpose of what I'm trying to do, which is to not have to enter the row numbers by myself. 3) What does this formula does? I don't understand the results it gives me. –  minClisto Aug 28 '13 at 17:48
    
@minClisto: (1) I did mean that you should write the row number twice, separated by a colon. But no spaces –– that’s a formatting error. (2) There’s some part of your question that I don’t understand. Do you want cell A1 to contain the total number of characters of every cell in row 1, including cell A1? That’s a circular reference, and is not going to work. Do you want Sheet2!A1 to contain the total number of characters of every cell in row 1 in Sheet1? Please tell me a little bit more about what you want the answer to look like. –  Scott Aug 28 '13 at 19:34
    
@minClisto: (3a) I’ve added an explanation of the formula to the answer. (3b) Well, I’ve done things like putting =SUM(LEN(1:1)) into cell A2, and it does what I believe you’re asking for. I cannot explain the results you’re getting if you don’t tell me what they are –– especially if you’re getting inconsistent results (“sometimes that gives me an error”). –  Scott Aug 28 '13 at 19:36
    
@minClisto: It’s slowly sinking into my brain that I seriously misunderstood your question. I saw “How to count all characters in a cell of current row in Excel?” and “count the characters of 1000+ cells”, and I thought you wanted to count the total number of characters in a row (essentially LEN(A1)+LEN(B1)+LEN(C1)+…+LEN(AJL1), or whatever your last column is). –  Scott Aug 30 '13 at 1:09
    
@minClisto: On rereading it (and reading your comments, and the other answers), I guess you simply want to count the characters in a cell in the current row, like B1 = LEN(A1), B2 = LEN(A2), … If that’s your question, see my new answer. –  Scott Aug 30 '13 at 1:10
add comment

You can use INDIRECT:

Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.

Assuming the first column:

=LEN( INDIRECT( "A" & ROW() )
share|improve this answer
add comment

Highlight the first cell in your list of data.Format your data as table by going into home tab and format as table. Select any table format. Put a check mark in the box that says "My table has headers" if your data has headers else leave it unchecked.

Now when you enter your formula in adjacent cell of the data it will automatically copy the formula till the end of your table. let say your data is in cells A1 to A10 and your entering formula len(A1) in cell B1, if your data is formatted as table then it should autmatically copy your formula till cell B10.

share|improve this answer
add comment

If you simply want to count the characters in a cell in the current row, like B1 = LEN(A1), B2 = LEN(A2), …, then that’s straightforward.  Just set cell B1 to =LEN(A1), and then select cell B1 and drag/fill it down to row 1000+.  If you don’t like dragging, here are some slightly different ways of doing the same thing.

  • set cell B1 to =LEN(A1), and then select cell B1 and double-click on the square at the lower right corner of the B1 bounding box:

                    the lower right corner of the bounding box

    This extends the value or formula in the current cell down to the first blank cell in the column to the left, so you must do this in Column B, and there must not be any blank cells in Column A.

  • select cell B1, then scroll down and Shift+click cell B1000 (or however far down you want to go).  Then type =LEN(A1) and type Ctrl+Enter.

But this is so simple that I wonder whether I’m still missing some part of the question.

share|improve this answer
    
Yes, this is essentially the same answer that Arjan and Excellll hinted at. No, I’m not copying them; I would have given this answer two days ago (Aug 27) if I had understood the question. –  Scott Aug 30 '13 at 1:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.