Thread: Count for Sms application

We have an application in our office which will send SMS directly from excel. For that excel sheet I require coding. Though I am aware this can be done by formula I would prefer macro for this.

MY 3 REQUIREMENTS:-

1) What ever I type in A:A (A1, A2, A3 and so on..) I should get count of characters in B:B (B1, B2 , B3 and so on...)

2) Would prefer live running count in B:B

3) C:C should show me the number of Sms it will take.

0 - 140 = 1 sms
141 - 280 = 2 sms
281 - 420 = 3 sms
421 - 560 = 4 sms and so on...(ie max characters for 1 sms is 140 characters)

Can anyone help me out with that..!!

Is there a particular reason why you want a macro to create the cell contents? Why re-invent the wheel when you can do what you want with two very simple formulae? Especially the automatic updating when values change are a no-brainer with formulae, whereas with macros, you'd have to constantly repeat running the macro (event based).

Assuming that you have column titles in row 1, use

B2 =LEN(A2)
C2 =CEILING(B2/140,1)

Copy down.

You can not hide the formula and display the result. The only thing you can do is lock the cells, then protect the sheet and specify that only unlocked cells can be selected. This way the user will not be able to click the cell and will not be able to see the formula.

Hello meet2mayank,

You can hide the formula from being displayed in cell, but the formula will atill be displayed in the formula bar. To hide the formula in the cell...

1. On the menu go to Tools
2. Select Options... This will display the Options dialog.
3. Click the View tab
4. Look for the check box labeled Formulas. If it it checked then click it to clear it.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save