Page 4 of 4 FirstFirst ... 234
Results 31 to 37 of 37

Thread: Check if a cell contains same digits

  1. #31
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,984
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Check if a cell contains same digits

    Quote Originally Posted by Rick Rothstein View Post
    @Phouc,

    These are both excellent and elegant solutions to the OP's first two questions! Great incite insight into the underlying mathematical structure of the problem!
    I agree.


    Quote Originally Posted by Rick Rothstein View Post
    ... you can use this somewhat compact code line to do your latest check...

    MsgBox [AND(LEFT(A1)=MID(A1,2,1),MID(A1,3,1)=RIGHT(A1),LEFT(A1)<>RIGHT(A1))]
    .. or using Phuoc's ideas and your notation

    Code:
    MsgBox [AND(MOD(LEFT(A1,2),11)=0,MOD(RIGHT(A1,2),11)=0,MOD(A1,1111)>0)]
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  2. #32
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,251
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Check if a cell contains same digits

    Quote Originally Posted by Peter_SSs;5331191.. or using Phuoc's ideas and your notation

    [code
    MsgBox [AND(MOD(LEFT(A1,2),11)=0,MOD(RIGHT(A1,2),11)=0,MOD(A1,1111)>0)][/code]
    Good idea. If I am not mistaken, this can be "simplified" (using that word might be a stretch) to this...
    Code:
    MsgBox [(MOD(LEFT(A1,2),11)+MOD(RIGHT(A1,2),11)=0)*MOD(A1,1111)>0]
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  3. #33
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,984
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Check if a cell contains same digits

    Quote Originally Posted by Rick Rothstein View Post
    "simplified" (using that word might be a stretch)
    Just "changed" I think.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  4. #34
    Board Regular kelly mort's Avatar
    Join Date
    Apr 2017
    Location
    Suhum, Ghana, West Africa
    Posts
    1,510
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check if a cell contains same digits

    Interesting!!!

    I am really learning more and new tricks each single moments. I really appreciate that
    There Is Always A Better Way

  5. #35
    Board Regular kelly mort's Avatar
    Join Date
    Apr 2017
    Location
    Suhum, Ghana, West Africa
    Posts
    1,510
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check if a cell contains same digits

    Quote Originally Posted by Peter_SSs View Post
    I agree.


    .. or using Phuoc's ideas and your notation

    Code:
    MsgBox [AND(MOD(LEFT(A1,2),11)=0,MOD(RIGHT(A1,2),11)=0,MOD(A1,1111)>0)]
    Hi,
    I just got into a trouble :


    I decided to replace the "A1" with a variable from a user input and I start getting a syntax error alerts.

    How do I fix that to take the variable?

    Like
    .....left(MyInput, 2), 11).......
    I thought just taking away the square brackets will do the job yet my thinking was off the grid.
    There Is Always A Better Way

  6. #36
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,251
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Check if a cell contains same digits

    Quote Originally Posted by kelly mort View Post
    I decided to replace the "A1" with a variable from a user input and I start getting a syntax error alerts.

    I thought just taking away the square brackets will do the job yet my thinking was off the grid.
    Do you remember when I said "If you are always checking only cell A1 and no other" in Message #29 ... that was because the square brackets require fixed cell references (basically, it is a shortcut notation for evaluating a formula that one might find in a cell). The square brackets are sort of an alternative method for using the Evaluate function. The Evaluate function, however, can use variables because its argument is a text string which means you can concatenate the variable into text string constants (or use VB string functions to embed the variable into the text argument) and pass that into the Evaluate function. So what Peter suggested in Message #31 could be written like this in order to use a variable instead of a cell reference.
    Code:
    MsgBox Evaluate(Replace("AND(MOD(LEFT(@,2),11)=0,MOD(RIGHT(@,2),11)=0,MOD(@,1111)>0)", "@", YourVariable))
    Note 1: I have assumed your variable's name is YourVariable (change it an necessary)

    Note 2: I used the @ symbol as a stand-in for your variable in order to avoid multiple concatenations and then used VB's Replace function to change those @ symbols to the value stored in the variable.
    Last edited by Rick Rothstein; Aug 25th, 2019 at 03:50 AM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  7. #37
    Board Regular kelly mort's Avatar
    Join Date
    Apr 2017
    Location
    Suhum, Ghana, West Africa
    Posts
    1,510
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check if a cell contains same digits

    Yes I do remember that question.


    It's fixed now.
    Cheers
    There Is Always A Better Way

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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