Get character count across a row of cells in excel 2007

I know by using the formula, =IF(LEN(A1)=1,1,LEN(A1)) , I can get the character count for cell A1, but what if I want the character count of A1 + B1 + C1...etc? I have over 20,000 rows of data and I was hoping I could copy a formula to put at the end of each one to know how many characters there are in each row. I am looking for rows that are over a certain size (so I could copy and paste the results as values in another row and then filter/sort it)
Who is Participating?
barry houdiniConnect With a Mentor Commented:
For the total characters in A1:Z1


regards, barry
BHBMHTAuthor Commented:
PERFECT! Many thanks!
Try the following code.
I am assuming that each row has the same column so you could just populate the 1st row and autofilter all the way down.
Sub Macro1()
Dim i As Integer
Dim j As Integer
Dim str As String

i = ActiveSheet.UsedRange.Columns.Count

For j = 1 To i
str = str & "len(" & Cells(1, j).Address & ")+"

str = Left(str, Len(str) - 1)
str = "=" & str
str = WorksheetFunction.Substitute(str, "$", "")
Cells(1, j).Value = str

End Sub

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.