0

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:

=LEN(A*current_row*)

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

5
  • @moses But this is a software question, not a programming question. Aug 28, 2013 at 0:26
  • As an aside: note that formulas are changed automatically when you drag them to another cell.
    – Arjan
    Aug 28, 2013 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!
    – user201262
    Aug 28, 2013 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, 2013 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, 2013 at 20:51

4 Answers 4

3

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.

5
  • 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, 2013 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. Aug 28, 2013 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”). Aug 28, 2013 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). Aug 30, 2013 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. Aug 30, 2013 at 1:10
1

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() )
1

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.

1

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.

1
  • 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. Aug 30, 2013 at 1:17

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .