Counting Cell Characters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a macro that will count the number of characters and spaces in a
cell, or merged cell range?
 
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).
 
The function LEN will do that in both VBA and within a cell. It even counts
leading or trailing blanks
 
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
 
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
 
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
 
cellLength = Len(ActiveCell)

Phil Hageman said:
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
 
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
 
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
 
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
 
Back
Top