# Help using LEN Function on 5,000 rows without writing formula on every row! (newb)

1. ## Help using LEN Function on 5,000 rows without writing formula on every row! (newb)

Hi guys, I really hope somebody here can help me with the LEN function in excel. I have looked everywhere for the solution but I just can't figure out what it is I need to do.
I am an excel novice so please explain in as basic and concise terms as possible.

My problem is this...

I have an excel sheet of about 5,000 rows in length and one column wide.
Each cell A1 to A5000 contains a line of text.
Approximately 4 of the cells have text which is too long for my purposes*.
How can I enter a formula which gives me the number of characters in each cell/row?

Eg:

Row Column A
...1. Nobby2020example1/excelproblem
...2. Nobby2020example1/excelproblem is difficult to figure out
...3. Nobby2020example1/excelprob
...4. Nobby2020example1/excelproblem/frustrated user

I can find the number of characters in a single cell by doing the forumla =len(a1) and it gives me the number of characters for cell A1.
But I need the number of characters for each cell individually.

What I presume I need is an automated way to put the formula in next to each cell so...

Row Column A ...... Column B
...1.... =len(a1) ...... Nobby2020example1/excelproblem
...2.... =len(a2) ...... Nobby2020example1/excelproblem is difficult to figure out
...3.... =len(a3) ...... Nobby2020example1/excelprob
...4.... =len(a4) ...... Nobby2020example1/excelproblem/frustrated user

But without having to write the formula in every cell.

Hope you can help, I presume the answer is obvious really.

Thanks  Register To Reply

2. ## Re: Help using LEN Function on 5,000 rows without writing formula on every row! (newb

You can catch right, down corner of cell and pull down (formula will appear automatically)
or click twice when you see small black cross in right down corner of cell  Register To Reply

3. ## Re: Help using LEN Function on 5,000 rows without writing formula on every row! (newb

You can type in B1:
=LEN(A1)
Then press Ctrl-C (copy), choose B2:B5000, Ctrl-V (paste)  Register To Reply

4. ## Re: Help using LEN Function on 5,000 rows without writing formula on every row! (newb

Hi

like you started ... in B1 write =len(a1) and hit Enter key ... go back to cell B1 and double click on right-bottom corner and formula will go down to the last row ...

len.jpg

Hope this help ... cheers, Marko  Register To Reply

5. ## Re: Help using LEN Function on 5,000 rows without writing formula on every row! (newb Originally Posted by DiCaver Hi

like you started ... in B1 write =len(a1) and hit Enter key ... go back to cell B1 and double click on right-bottom corner and formula will go down to the last row ...

Attachment 138456

Hope this help ... cheers, Marko

Thank you. This worked perfectly and so simple.

Is it possible to re-order the results by highest length now?  Register To Reply

6. ## Re: Help using LEN Function on 5,000 rows without writing formula on every row! (newb

Oh- and thanks to the other people too.

I found a way to sort the data into order. I would describe it here but now I can't remember what I did.  Register To Reply