+ Reply to Thread
Results 1 to 9 of 9

How to count total character in cell to be number?

  1. #1
    Registered User
    Join Date
    08-31-2012
    Location
    Bangkok, Thailand
    MS-Off Ver
    Excel 2007
    Posts
    17

    Question How to count total character in cell to be number?

    Hi...

    May I ask how to count total character in a cell to be shown in number ?

    I have attached file of excel for the question.

    Attachment 199712

    As I have to set item code for accounting software. The max. total character of the software is 20 digits.

    Hence, I'd like to know if my code setting is more than 20 digits or not?

    Please anyone kindly help me solve this problem.

    PS: I'm using Ms.Excel 2010 now

    Thanks in advance
    LEE
    Attached Files Attached Files
    Last edited by wrblee; 12-12-2012 at 05:31 AM. Reason: change attached file

  2. #2
    Forum Contributor dogberry's Avatar
    Join Date
    07-15-2012
    Location
    Wales, UK
    MS-Off Ver
    Excel 2010
    Posts
    624

    Re: How to count total character in cell to be number?

    Hi

    Use your LEN function that will do it. Always try and post a sample worksheet we cant edit a picture. Hope it works



    Chris
    Click my star if I helped Thanks

  3. #3
    Registered User
    Join Date
    08-31-2012
    Location
    Bangkok, Thailand
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: How to count total character in cell to be number?

    Hi..dogberry,

    Please explain how to use LEN function? I've never use it before.

    Besides, I've attached excel file in 1st post.

    Anyway, I've re posted excel file again in case you can edit my file.

    how to count each txt to be number.xlsx

    Hope you can open it.

    THANKS a lot for your kindness help!

  4. #4
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: How to count total character in cell to be number?

    hi wrblee. as Chris mentioned, you can use LEN like this:
    =IF(H4="","",LEN(H4))

    you could also consider a validation in column H, so they dont exceed 20 characters in the first place. select H4:H14, go to Data -> Data Validation -> Allow: Custom -> Formula:
    =LEN(H4)<=20
    you can even put an error message in the "Error Alert" tab

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  5. #5
    Forum Contributor
    Join Date
    08-23-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    163

    Re: How to count total character in cell to be number?

    Hi,

    As rightly suggested by Chris use LEN function.

    =LEN(H11)
    Last edited by [email protected]; 12-12-2012 at 05:45 AM.

  6. #6
    Forum Contributor dogberry's Avatar
    Join Date
    07-15-2012
    Location
    Wales, UK
    MS-Off Ver
    Excel 2010
    Posts
    624

    Re: How to count total character in cell to be number?

    Hi

    Amended file attached with basic len function have a read through the other posts to see how you can amend it to your requirements. If you dont want the decimal place counted as in your last entry you will have to get rid of them


    Regards

    Chris
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    08-31-2012
    Location
    Bangkok, Thailand
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: How to count total character in cell to be number?

    Quote Originally Posted by benishiryo View Post
    hi wrblee. as Chris mentioned, you can use LEN like this:
    =IF(H4="","",LEN(H4))

    you could also consider a validation in column H, so they dont exceed 20 characters in the first place. select H4:H14, go to Data -> Data Validation -> Allow: Custom -> Formula:

    you can even put an error message in the "Error Alert" tab

    Dear benishiryo,

    Your comment for Error alert is very interesting!!

    However, I've tried to do as your advise. But why there is no error alert for column more than 20?

    Could you please kindly edit in my excel file ?

    I've amended excel file by adding text > 20.

    how to set error alert for counting.xlsx

    THANKS!!!

  8. #8
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: How to count total character in cell to be number?

    you're very welcome. the validation is meant for pre-entry. meaning it limits users to key something specified by you. so it's supposed to be in Column H. but i just realised column H is a combination of A:F. if A:E is always a total of 6 characters, you can do the validation in column F instead. your formula shouldnt be a range like what you did. just select the cells you want to apply to (maybe F8:F15). then do the formula as such:
    =LEN(F8)<=14

    or if A:E is not always 6 characters, but column F is usually the cell to change, then:
    =LEN(F8)+LEN(A8&B8&C8&D8&E8)<=20

    added 2 sheets to illustrate. you can try to key in characters to exceed 20, and it'll give an error
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    08-31-2012
    Location
    Bangkok, Thailand
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: How to count total character in cell to be number?

    Oh...benishiryo,

    THANKS A lot for your answer and showing me in excel file. I've got it!!!

    Also thanks everyone who replied to this thread ^^

    Got it all!!!

    THANKS.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1