+ Reply to Thread
Results 1 to 14 of 14

counting letters as numbers in many different cells ?

  1. #1
    Registered User
    Join Date
    05-02-2018
    Location
    tasikmalaya, indonesia
    MS-Off Ver
    2007
    Posts
    14

    Question counting letters as numbers in many different cells ?

    I tried to count all the letters that appear in M16 - DH16 one by one vertically since i cant do it aautomatically.
    Starting from Cell m16 - m29 I use the Txt2score formula in Vba macro to convert to number and then display the result in cell m30 and m31.
    I' tried = Txt2Score (M16: M29) But it does not work. it can only be used for one cell ex : txt2score(m16) .
    Is there another formula to calculate it properly? its ok vba formula.
    cell m31 for single digit only now :





    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by jeccobeard; 05-03-2018 at 03:45 AM.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,997

    Re: counting letters as numbers in many different cells ?

    This .????

    Please Login or Register  to view this content.
    =txt2score(M16:m29)

    Do not understand difference between M30 and M31
    Last edited by JohnTopley; 05-02-2018 at 12:02 PM.

  3. #3
    Registered User
    Join Date
    05-02-2018
    Location
    tasikmalaya, indonesia
    MS-Off Ver
    2007
    Posts
    14

    Re: counting letters as numbers in many different cells ?

    that is formula im using to convert text to number value. but it can count one cell only.
    cell m30 does not reduce to single digits. the result according to the value obtainedlike the formula = SUM (M16: M29)
    in cell m31 we reduce the letter into a single digit except the number that generates the karmic debt value: 11, 13, 14, 16, 19
    Last edited by jeffreybrown; 05-03-2018 at 02:40 PM. Reason: Removed full quote!

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,997

    Re: counting letters as numbers in many different cells ?

    The code I posted sums the range M16:M29:

    Please Login or Register  to view this content.
    in cell m31 we reduce the letter into a single digit except the number that generates the karmic debt value: 11, 13, 14, 16, 19
    How?
    Last edited by JohnTopley; 05-03-2018 at 02:36 AM.

  5. #5
    Registered User
    Join Date
    05-02-2018
    Location
    tasikmalaya, indonesia
    MS-Off Ver
    2007
    Posts
    14

    Re: counting letters as numbers in many different cells ?

    my mistake my formula cant reduce it into 11, 13, 16, 19 if number is same.
    and count only two digit letter
    =IF(LEFT(N30,1)+MID(N30,2,1)>=11,LEFT(N30,1)+MID(N30,2,1),IF(LEFT(N30,1)+MID(N30,2,1)=10,1,IF(LEFT(N30,1)+MID(N30,2,1)<10,LEFT(N30,1)+MID(N30,2,1),0)))
    if result
    maybe we cant reduce it ?
    so i decide cell M31 to reduce all number into single digit only.
    Last edited by jeffreybrown; 05-03-2018 at 02:40 PM. Reason: Removed full quote!

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,997

    Re: counting letters as numbers in many different cells ?

    Trying interpret your M31 logic:

    if the value is not a "karma" number the result is the sum of the digits?

    =IF(LEFT(N30,1)+MID(N30,2,1)>=11,LEFT(N30,1)+MID(N30,2,1),IF(LEFT(N30,1)+MID(N30,2,1)=10,1,IF(LEFT(N30,1)+MID(N30,2,1)<10,LEFT(N30,1)+MID(N30,2,1),0)))

    Not clear why you are doing the MID tests.

    If M30 =22, what is M31? If M30=25, what is M31?


    If M30 is any of 11, 13, 14, 16, 19, then M31=M30?
    Last edited by JohnTopley; 05-03-2018 at 04:18 AM.

  7. #7
    Registered User
    Join Date
    05-02-2018
    Location
    tasikmalaya, indonesia
    MS-Off Ver
    2007
    Posts
    14

    Re: counting letters as numbers in many different cells ?

    yeah actually i dont convert 11, 13, 14,16 19 if m30 = 11, 13, 16, 19
    If M30 =22, what is M31? its 4
    If M30=25, what is M31? 7
    it looks like what I'm trying to do in cell m30 is convert the letters into numbers, then count the letters in the cells I want to count in cell m30 after that reduce it into single digit number in m31 except 11, 13, 14, 16.19.
    if there are numbers in cell m30 that produce values 11, 13, 14, 16, 19
    then we turn it into numbers 11 or 13, 14, 16, 19 according to the value it produces.
    example 11, 38, 47, 119 are considered as 11
    3 + 8 = 11, 1 + 1 + 9 = 11
    68 is considered 14
    6 + 8 = 14 etc
    but if the result in m30 is already 11, 13, 14, 16, 19 we do not need to change it.
    Last edited by jeffreybrown; 05-03-2018 at 02:40 PM. Reason: Removed full quote!

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,997

    Re: counting letters as numbers in many different cells ?

    Thank you: probably more VBA.

  9. #9
    Registered User
    Join Date
    05-02-2018
    Location
    tasikmalaya, indonesia
    MS-Off Ver
    2007
    Posts
    14

    Re: counting letters as numbers in many different cells ?

    Quote Originally Posted by JohnTopley View Post
    Thank you: probably more VBA.
    its ok
    thank u

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,997

    Re: counting letters as numbers in many different cells ?

    Please Login or Register  to view this content.
    =Knumber(M30)

    Sheet3 has test samples for numbers from 1 to 150 as a check
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    05-02-2018
    Location
    tasikmalaya, indonesia
    MS-Off Ver
    2007
    Posts
    14

    Re: counting letters as numbers in many different cells ?

    thank u
    so this formula only for count m30-dh30) and cant change letter m16-m29 into a number then count the result ?
    ex if we sum =formula(m16-29)
    J A B ( m 16- m19) = total = 4 in m30

    =knumber(m16:m29) not working.

    2. so i can add more number in spreadsheet 3 to improve this formula?
    ex : A154 = 151 so in cell bell 154 =knumber(A154)
    Last edited by jeffreybrown; 05-03-2018 at 02:39 PM. Reason: Removed full quote!

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,997

    Re: counting letters as numbers in many different cells ?

    The first macro (TXT2SCORE(M16:M29) creates the value in M30: the latest macro (Knumber(M30) ) creates the value in M31: you need BOTH functions.

    Please read the instructions carefully.

    in Post #10 i said

    =Knumber(M30)
    so in M31

    =Knumber(M30)

    copy across to DH

    Sheet3 had EXAMPLES only so you can check it is working. It does not affect the formula - merely "tests" it,

    IF you do NOT need row 30 then it the two macros could be combined to give the result in row 31.

    OR

    we calculate Rows 30 and 31 when we have a new entry in L16:L29, removing the functions and replacing by subroutines.
    Last edited by JohnTopley; 05-03-2018 at 03:38 PM.

  13. #13
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: counting letters as numbers in many different cells ?

    Please do not quote whole posts -- it's just clutter.

    I you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

    For normal conversational replies, try using the QUICK REPLY box below.
    HTH
    Regards, Jeff

  14. #14
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,997

    Re: counting letters as numbers in many different cells ?

    See attached which now runs from the Change-Event macro

    Please Login or Register  to view this content.
    new code in RED

    Change_Event code in sheets "Test" and "Sheet1"
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Counting cells because of 4 same letters in the cell
    By dontbugme1 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 08-26-2015, 02:11 PM
  2. Replies: 8
    Last Post: 07-23-2014, 09:12 AM
  3. [SOLVED] Help Counting Cells w/first 3 letters...
    By CRichardsDSX in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-31-2012, 09:47 AM
  4. Replies: 4
    Last Post: 02-04-2012, 01:46 PM
  5. Counting Letters & numbers
    By MCPP in forum Excel General
    Replies: 0
    Last Post: 04-12-2011, 12:20 PM
  6. counting letters and numbers
    By egidijus in forum Excel General
    Replies: 1
    Last Post: 11-24-2008, 03:49 AM
  7. Counting occurance of letters or numbers
    By csfrolich in forum Excel General
    Replies: 9
    Last Post: 03-25-2005, 01:06 PM

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