Vba to count number of letters in a cell

nicolewh

Well-known Member
Joined
Aug 7, 2009
Messages
554
I would like to have a VBA code (msgbox) which counts the number of alphabetic characters (letters) in a cell. Any suggestions?

example:
Code:
Msgbox CountLetters(Range("A1"))
Thank you!

Nicole
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Hi Nicole
This will count the number of letters in a cell excluding spaces.
Code:
Sub test()
Range("A2").Formula = "=LEN(SUBSTITUTE(A1,"" "",""""))"
MsgBox "There are " & Range("A2").Value & "  letters in the cell"
End Sub
 
Upvote 0
I'm assuming you want to distinguish betweeen letters and other characters like numbers, symbols etc.

So for the message box, you could use:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> CountLetters()<br>    <SPAN style="color:#00007F">With</SPAN> CreateObject("VBScript.RegExp")<br>        .Pattern = "[^A-Za-z]"<br>        .Global = <SPAN style="color:#00007F">True</SPAN><br>        MsgBox Len(.Replace(Range("A1").Value, ""))<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


Or if you wanted to use a function in your sheet, you could use:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> RX <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CreateRX()<br><SPAN style="color:#00007F">Set</SPAN> RX = CreateObject("VBScript.RegExp")<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Function</SPAN> LetterCount(<SPAN style="color:#00007F">ByVal</SPAN> s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> RX <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> CreateRX<br>    <SPAN style="color:#00007F">With</SPAN> RX<br>        .Pattern = "[^A-Za-z]"<br>        .Global = <SPAN style="color:#00007F">True</SPAN><br>        LetterCount = Len(.Replace(s, ""))<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

and then in your sheet use the formula:
=LetterCount(A1)
 
Upvote 0
I have a similar need but its little different. I do receive invoices from my vendors. They send me an invoice in PDF formate. I am using able2extract software to conver the PDF to excel. It is working great but most of the times my vendors enters the items/Case in the description field. Like the example below:

"Bajri Rotla 11 oz.(24)"

I would like to place a "," in between the last character and begining of the first number. In the above case it will be after "a" (in Rotla) and before "1" in (11).

Is there a way to write this code in excel?? This would be a great help!! If needed I would be more than happy to upload some test data.

Thanks!!
 
Upvote 0
IMO, this is different enough and of course, your own question. I would respectfully suggest starting your own thread. Include the info you have already, as well as what column and the name of the sheet that the data to be changed/coerced if in.
 
Upvote 0
IMO, this is different enough and of course, your own question. I would respectfully suggest starting your own thread. Include the info you have already, as well as what column and the name of the sheet that the data to be changed/coerced if in.

Sorry... Will post it in a new thread. I did not mean to hijack this thread. Sorry!!
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top