Counting Cell Characters

[Guest]

Is there a macro that will count the number of characters and spaces in a
cell, or merged cell range?

 

[Guest]

Do you mean the length of the text that is in the cell, or the number of
characters and spaces that could fit in the cell? The first you can get
using the LEN() worksheet function. If you mean the second: there is no
simple answer. The amount of text that can fit in a cell depends on several
things, including font used, font size, whether or not you use bold and/or
italics, and even the individual letters used since most fonts are
proportionately spaced (an i is a lot narrower than a W).

 

[Guest]

The function LEN will do that in both VBA and within a cell. It even counts
leading or trailing blanks

 

[Guest]

Phil
You can use the Len(str) code. If your cell is A1 on sheet1 then

Sub Macro1()

Dim cellLength as Integer
cellLength = Len(Sheets("Sheet1").Range("A1"))
Msgbox(cellLength)

End sub

You can do the same thing with strings. For example, you have a string
MyString and you want to know how long it is, you can use MyStringLength =
Len(MyString).

Dave

 

[cscorp]

Function CountSpaces(CellRef)

Dim StrinLen As Integer
Dim EmptySpaces As Integer
Dim I As Integer

CellRef = Trim(CellRef)

StrinLen = Len(CellRef)
For I = 1 To Len(CellRef)
If Mid(CellRef, I, 1) = " " Then EmptySpaces = EmptySpaces + 1
Next
CountSpaces = "String Lengh: " & StrinLen & ", Empty Spaces: "
Chr(10) _
& EmptySpaces


End Functio

 

[Guest]

Dave,

Thanks for your reply. Is there a modification to the code where the macro
acts on what ever cell in whatever worksheet where the cell is current? I
placed this macro in Personal.xls

Thanks, Phil

 

[Guest]

cellLength = Len(ActiveCell)

Dave,

Thanks for your reply. Is there a modification to the code where the macro
acts on what ever cell in whatever worksheet where the cell is current? I
placed this macro in Personal.xls

Thanks, Phil

 

[Guest]

Folks,

What I need is a toolbar button with a macro attached such that were ever I
am in any workbook, by clicking the button, I get a message box with the
number of characters and spaces in the current cell. Aren't functions
typically used in a particular cell?

Thanks, Phil

 

[Guest]

It would be close to the same as the last except:

Dim StrinLen As Integer
Dim EmptySpaces As Integer
Dim I As Integer

CellRef = Trim(ActiveCell.Value)

StrinLen = Len(ActiveCell.Value)
For I = 1 To Len(ActiveCell.Value)
If Mid(CellRef, I, 1) = " " Then EmptySpaces = EmptySpaces + 1
Next
MsgBox "String Lengh: " & StrinLen & Chr(10) & "Empty Spaces: " & EmptySpaces

 

[Guest]

Hi Malriem,

Thanks for your reply - this works exactly as needed. Can the code be
modified to sum the string length and number of characters and add that sum
in the message block?

Thanks, Phil