Count the number the letters in a cell

[BharathBabu]

Hi,


I have a query with regards to Count the number of letters in a cell

I can use =len() but the answer is other cell


what I should I have to do, I want to get the answer in same cell


For eg:

A1= Never if we put the formula in B1 =len(A1) it gives 5 but the answer in B1 Cell --> No Issues..


But My question is: I need the answer in same cell that is A1


A1= Never

Answer should be in the same cell... that is A1= Never 5, I don't want to get the answer in other cell....

Please advise how to arrive it...

 

[Debraj]

Hi Bharath,


Why "5".. because after adding 5, len of text will change to 5+( 5) = 7, and again.. and again..


So Only 1 option VBA..


however, I would like to suggest you.. =A1 & " do this"


where A1 = NEVER..

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Target = Target & " " & Len(Target)
Application.EnableEvents = True
End Sub

[/pre]
Regards,

Deb

 

[Hui]

BharathBabu


Cells can't self reference as you suggested


You can do something like

="Never"&" "&LEN("Never")

or

=A1&" "&LEN(A1)

where A1 would have the word Never

But you can't put the second formula in A1

 

[smittal]

hi,


Please clarification required!!! =A1&" "&LEN(A1)


in which cell we are using this formula?, because we cant use this thing in A1 itself..(circular reference)

 

[Hui]

Smittal


I'm pretty sure that my last line says you can't put it in A1

So you can put it anywhere else

 

[oldchippy]

Hi,


When Hui... says put it somewhere else, maybe if you type it on Sheet1 cell A1 "Never",then click Sheet2 and put this in A1


=Sheet1!A1&" "&LEN(Sheet1!A1)


Then you will have "Never 5" in cell A1 but on Sheet2

 

[SirJB7]

Hi, BharathBabu!

I'll try to synthesize all previous posts in two words, maybe not those which you'd hope to read but they're true: you can't.

Regards!

 

[James Perry]

Hello Ninjas & members.

Out of curiosity i simply enjoyed trying it all out.

Graćias.