Serial numbers and ZEROS

Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Serial numbers and ZEROS

  1. #1
    Board Regular
    Join Date
    Nov 2017
    Posts
    116
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question Serial numbers and ZEROS

     
    Hello,

    I have a problem with disapearing zeros..

    my formula so far to copy proper numbers to other sheet is:

    Code:
    Dim cellR As RangeDim lastRowR As Long, r As Long
    
    
    lastRowR = Range("J" & Rows.Count).End(xlUp).row
    r = 2
    
    
    For Each cellR In Sheets(1).Range("J1:J" & lastRowR)
         If Len(cellR) = 9 Or Len(cellR) = 14 Then
            cellR.EntireRow.Copy Sheets("SERIAL").Cells(r, 1)
            r = r + 1
        End If
    Next
    the problem is that the files that I need to work on have values in column J looking like this:

    ="00000000"
    ="029384756"
    ="000776819"
    ="284758933"

    I need to copy entire rows to my new sheet if value in column J has 9 or 14 characters. The formating makes things difficult.

    we can strip those numbers of ="" but than excel is removing leading zeros.

    Anybody know how to bypass this?


    Best Regards
    W.

  2. #2
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    1,655
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Serial numbers and ZEROS

    If you record the number as text it will not drop any character.

    Excel 2010
    A B C
    1 length
    2 02938756 8 Formated as text with ' in front of the number
    3 02938756 7 Formated as number with formatting for leading zero
    Sheet1

    Worksheet Formulas
    Cell Formula
    B2 =LEN(A2)
    B3 =LEN(A3)
    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

  3. #3
    Board Regular
    Join Date
    Nov 2017
    Posts
    116
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Serial numbers and ZEROS

    Can you modify my VBA scrip from first post to auto-do the trick? the problem is that column J has every value like this =" ******** " - within the quotes and with equal sign..

  4. #4
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    1,655
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Serial numbers and ZEROS

    Try

    Code:
    Dim cellR As Range
    Dim lastRowR As Long, r As Long
    
    lastRowR = Range("J" & Rows.Count).End(xlUp).Row
    For x = 2 To lastRowR
        Cells(x, "J") = "'" & Cells(x, "J")
    Next x
    
    r = 2
    For Each cellR In Sheets(1).Range("J1:J" & lastRowR)
         If Len(cellR) = 9 Or Len(cellR) = 14 Then
            cellR.EntireRow.Copy Sheets("SERIAL").Cells(r, 1)
            r = r + 1
        End If
    Next
    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

  5. #5
    Board Regular
    Join Date
    Nov 2017
    Posts
    116
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Serial numbers and ZEROS

    Nice!

    One more request.. Can you make it skip ="000000000" ?

    I do not want "0" nor "000000000".

  6. #6
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    1,655
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Serial numbers and ZEROS

    Try

    Code:
    Dim cellR As Range
    Dim lastRowR As Long, r As Long
    
    lastRowR = Range("J" & Rows.Count).End(xlUp).Row
    For x = 2 To lastRowR
        
        Cells(x, "J") = "'" & Cells(x, "J")
    Next x
    
    r = 2
    For Each cellR In Sheets(1).Range("J1:J" & lastRowR)
         If (Len(cellR) = 9 Or Len(cellR) = 14) And cellR <> 0 Then
            cellR.EntireRow.Copy Sheets("SERIAL").Cells(r, 1)
            r = r + 1
        End If
    Next
    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

  7. #7
    Board Regular
    Join Date
    Nov 2017
    Posts
    116
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Serial numbers and ZEROS

    nope, does not work. But ive work it around deleting 0'os from copied rows.

  8. #8
    Board Regular
    Join Date
    Nov 2017
    Posts
    116
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Serial numbers and ZEROS

    Hi Again!

    Scott T, Your work is great!

    I have another mod to do..

    When my script i looking for numbers (nine or fourteen chars) ="123456789", sometimes there is an 10th, 11th / 15th, 16th char and it looks like this 123456789/P or 987654321/2 or 875639275/+. Can macro look for two last chars and accept them as correct even if its above 9 or 14 char when there is SLASH (/) at 10 or 15?

    Best Regards
    W.

  9. #9
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    1,655
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Serial numbers and ZEROS

    Does this do what you want?

    Code:
    Dim cellR As Range
    Dim lastRowR As Long, r As Long
    lastRowR = Range("J" & Rows.Count).End(xlUp).Row
    For x = 2 To lastRowR
        
        Cells(x, "J") = "'" & Cells(x, "J")
    Next x
    r = 2
    For Each cellR In Sheets(1).Range("J1:J" & lastRowR)
         If (Len(cellR) = 9 Or Len(cellR) = 14 Or Mid(cellR, 10, 1) = "/" Or Mid(cellR, 15, 1) = "/") And cellR <> 0 Then
            cellR.EntireRow.Copy Sheets("SERIAL").Cells(r, 1)
            r = r + 1
        End If
    Next
    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

  10. #10
    Board Regular
    Join Date
    Nov 2017
    Posts
    116
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Serial numbers and ZEROS

      
    Maaaaann....
    Yes. :D

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
  •  

 

 
DMCA.com