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).

"Phil Hageman" wrote:

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

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


"Phil Hageman" wrote:

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

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

"Phil Hageman" wrote:

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

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

"drhalter" wrote:

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

"Phil Hageman" wrote:

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

cellLength = Len(ActiveCell)

"Phil Hageman" wrote:

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

"drhalter" wrote:

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

"Phil Hageman" wrote:

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

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

--
cscor
-----------------------------------------------------------------------
cscorp's Profile: http://www.excelforum.com/member.php...fo&userid=2401
View this thread: http://www.excelforum.com/showthread.php?threadid=37633

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

"cscorp" wrote:


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 Function


--
cscorp
------------------------------------------------------------------------
cscorp's Profile: http://www.excelforum.com/member.php...o&userid=24015
View this thread: http://www.excelforum.com/showthread...hreadid=376338

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

"Phil Hageman" wrote:

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

"cscorp" wrote:


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 Function


--
cscorp
------------------------------------------------------------------------
cscorp's Profile: http://www.excelforum.com/member.php...o&userid=24015
View this thread: http://www.excelforum.com/showthread...hreadid=376338

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

"Malriem" wrote:

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

"Phil Hageman" wrote:

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

"cscorp" wrote:


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 Function


--
cscorp
------------------------------------------------------------------------
cscorp's Profile: http://www.excelforum.com/member.php...o&userid=24015
View this thread: http://www.excelforum.com/showthread...hreadid=376338