Page 1 of 4 123 ... LastLast
Results 1 to 10 of 37

Thread: Check if a cell contains same digits

  1. #1
    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 Check if a cell contains same digits

    Hi,

    I am looking for a way to verify if a cell say A1 contains same digits like 1111, 2222, etc.

    The Len will always be 4.

    Thanks in advance
    There Is Always A Better Way

  2. #2
    Board Regular
    Join Date
    Apr 2019
    Posts
    80
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check if a cell contains same digits

    Probably there is lots for solution, I've got something like this:
    A
    B
    1
    1111
    TRUE
    2
    1233
    FALSE
    3
    2222
    TRUE
    4
    2452
    FALSE
    5
    8888
    TRUE


    A
    B
    1
    1111
    =SUM(CODE(MID(A1,ROW($1:$4),1)))/4=CODE(LEFT(A1,1))
    2
    1233
    =SUM(CODE(MID(A2,ROW($1:$4),1)))/4=CODE(LEFT(A2,1))


    There is array formula in B1 so have to be accept with Ctrl+Shift+Enter ({} should appear) and drag it down.
    Last edited by KOKOSEK; Aug 19th, 2019 at 05:27 AM.

  3. #3
    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

    Okay thanks but I need a VBA code for this.
    There Is Always A Better Way

  4. #4
    Board Regular
    Join Date
    Apr 2003
    Location
    England
    Posts
    5,890
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Check if a cell contains same digits

    A non array formula

    =SUBSTITUTE(A1,LEFT(A1,1),"")=""
    To post screen shots download the MrExcel HTML Maker
    Look here for other options Recommended Add-ins and Links

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

    Default Re: Check if a cell contains same digits

    Quote Originally Posted by KOKOSEK View Post
    Probably there is lots for solution,
    True, here are a couple more.

    Same digits

    ABC
    11111TRUETRUE
    21233FALSEFALSE
    32222TRUETRUE
    42452FALSEFALSE
    58888TRUETRUE

    Spreadsheet Formulas
    CellFormula
    B1=SUBSTITUTE(A1,LEFT(A1),"")=""
    C1=A1&""=REPT(LEFT(A1),4)


    Excel tables to the web >> Excel Jeanie HTML 4
    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

  6. #6
    Board Regular
    Join Date
    Apr 2019
    Posts
    80
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check if a cell contains same digits

    You did not mention about VBA in original post or thread title?

    Into sheet code:

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        MsgBox Evaluate("=SUM(CODE(MID(" & Target.Value & ",ROW($1:$4),1)))/4=CODE(LEFT(" & Target.Value & ",1))")
    End Sub
    Last edited by KOKOSEK; Aug 19th, 2019 at 05:40 AM.

  7. #7
    Board Regular
    Join Date
    Dec 2018
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check if a cell contains same digits

    Hi, something like this?

    Code:
    Option Explicit
    
    
    Option Explicit
    
    
    Sub Worksheet_Change(ByVal Target As Range)
    Dim numberInQuestion As String, firstDigitInQuestion As Long, i As Long, nextDigitInQuestion As Long, isItANumber As Boolean
    If Target.Address = "$A$1" Then
    numberInQuestion = Range("A1").Value
    isItANumber = IsNumeric(numberInQuestion)
    firstDigitInQuestion = Left(numberInQuestion, 1)
    If isItANumber = True Then
    For i = 1 To Len(numberInQuestion)
    nextDigitInQuestion = Mid(numberInQuestion, i, 1)
    If firstDigitInQuestion <> nextDigitInQuestion Then
    MsgBox "numbers are different"
    End If
    Next i
    End If
    End If
    End Sub
    
    
    Sub init()
    Dim rng As Range
    Set rng = Range("A1")
    Call Worksheet_Change(rng)
    End Sub
    Last edited by Lavina; Aug 19th, 2019 at 05:39 AM.

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

    Default Re: Check if a cell contains same digits

    Quote Originally Posted by kelly mort View Post
    Okay thanks but I need a VBA code for this.
    Something like
    Code:
    MsgBox Replace(Range("A1").Value, Left(Range("A1").Value, 1), "") = vbNullString
    or
    Code:
    MsgBox Range("A1").Text = String(4, Left(Range("A1").Text, 1))
    Last edited by Peter_SSs; Aug 19th, 2019 at 05:43 AM.
    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

  9. #9
    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

    Cool cool!!! Thank you all

    I have tested all the above. They are working fine.
    @Peter_SSs
    your last code is very cute. Thanks
    There Is Always A Better Way

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

    Default Re: Check if a cell contains same digits

    You're welcome. Plenty of choice.
    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

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
  •