dcsimg
Results 1 to 9 of 9
  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    257

    Right 4 characters from field

    Hi...I have a 7 digit code in column A and I want the last 4 characters only. I can add another column and use the formula -right(A",4) but is there a way to just strip out the first 3 characters in column A without having to add another column first. Thanks

  2. #2
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,478

    Re: Right 4 characters from field

    Code:
    Sub last4()
        Dim ws As Worksheet
        Dim lr As Long
        Dim j As Long
        
        Set ws = ActiveSheet
        With ws
            lr = .Range("a1").End(xlDown).Row
            For j = 1 To lr
                .Range("a" & j).Value = Right(.Range("a" & j).Value, 4)
            Next j
        End With
    End Sub

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    257

    Re: Right 4 characters from field

    Thanks so much. I've slightly amended the code as in column A there are some blank fields before the next code appears.

    Code:
    Sub last4()
        Dim ws As Worksheet
        Dim lr As Long
        Dim j As Long
        
         
        
        Set ws = ActiveSheet
        With ws
           lastrow = Range("a" & Rows.Count).End(xlUp).Row
            lr = .Range("a2" & lastrow).End(xlDown).Row
                  For j = 2 To lr
                .Range("a" & j).Value = Right(.Range("a" & j).Value, 4)
               
            Next j
        End With
    End Sub
    The code works fine but I think it loops through all the rows as it takes a while to run. Is it possible to refine the code? Thanks

  4. #4
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,478

    Re: Right 4 characters from field

    You changed the code I provided, with the effect being that it's trying to process down to the last row of the sheet. Did it not work as I originally posted?

    Also, you probably want to add:

    Code:
    application.ScreenUpdating=False
    somewhere near the beginning.

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    257

    Re: Right 4 characters from field

    Hi the original code you provided stopped at the first change in code where there were blank fields before. For example


    xxx1234



    xxxx4567





    xxxx7890


    So the code stopped at 4567.

  6. #6
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,478

    Re: Right 4 characters from field

    Ok, didn't realize there were blank cells in column A. Maybe zip and attach a sample.

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    257

    Re: Right 4 characters from field

    Please find attached sample fileRight*****ple.zip
    Last edited by fusion001; Jun 7th, 2018 at 07:15 AM.

  8. #8
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,478

    Re: Right 4 characters from field

    Code:
    Sub last4()
        Dim ws As Worksheet
        Dim lr As Long
        Dim j As Long
        
        Set ws = ActiveSheet
        With ws
            lr = .Range("a" & Rows.Count).End(xlUp).Row
            For j = 1 To lr
                If .Range("a" & j).Value <> "" Then
                    .Range("a" & j).Value = Right(.Range("a" & j).Value, 4)
                End If
            Next j
        End With
    End Sub

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    257

    Re: Right 4 characters from field

    That works great. Thanks so much.

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width


×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.