Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Location
    Seattle, WA
    Posts
    4

    Unanswered: remove character in excel 2000 column

    HELP
    I have 24,000 rows that have zip codes in them and the zip start with a ' in fron of it. eg. '98001
    I need to remove the '
    what do I do? I know very little about excel, do I need to do a function or create a module?
    Thanks in advance

  2. #2
    Join Date
    Nov 2003
    Posts
    9

    Re: remove character in excel 2000 column

    Originally posted by markbarbieri
    HELP
    I have 24,000 rows that have zip codes in them and the zip start with a ' in fron of it. eg. '98001
    I need to remove the '
    what do I do? I know very little about excel, do I need to do a function or create a module?
    Thanks in advance
    Hullo. I'd recommend using a second column, to hold the new zips. Say you've got the zips in A. In B, use this formula
    Code:
    =RIGHT(a1,5)
    . Copy that down the length of your source, and that will give you a "stripped" zip code. Then, if you'd like, you can Copy->Paste Special->Values over the old info in A.

    HTH

    P

  3. #3
    Join Date
    Jan 2004
    Location
    Seattle, WA
    Posts
    4

    Re: remove character in excel 2000 column

    That didn't work, when I paste special > values it puts the ' back in.



    Originally posted by Philem
    Hullo. I'd recommend using a second column, to hold the new zips. Say you've got the zips in A. In B, use this formula
    Code:
    =RIGHT(a1,5)
    . Copy that down the length of your source, and that will give you a "stripped" zip code. Then, if you'd like, you can Copy->Paste Special->Values over the old info in A.

    HTH

    P

  4. #4
    Join Date
    Dec 2003
    Location
    Östersund Sweden
    Posts
    60
    Hi,

    If this is a one-time action only then I suggest to do the following:

    1. Sleect the column by clicking on the column-head (grey-cell)
    2. Choose the command Edit | Replace...
    3. Select the tab Replace
    4. Int eh field for Find what enter '
    5. Leave the field for Replace with empty.
    6. Click on the OK-button.
    7. Done!

    Let us know the outcome of above.

    Kind regards,
    Dennis
    Kind regards,
    Dennis

  5. #5
    Join Date
    Jan 2004
    Location
    Seattle, WA
    Posts
    4

    Angry The ' in excel is impossible to get rid of

    First thing I tried......, it's like magic, it doesn't work on ' in front of letters or numbers. Try it, put '97001 in a cell and then try and remove it with replace or even try the other suggestion. Nothing is working. Please don't tell me I have to manually delete the ' in every cell.
    Any Microsoft Excel programmers out there that can help? Any masters of Excel out there?? God???




    Originally posted by Xl-Dennis
    Hi,

    If this is a one-time action only then I suggest to do the following:

    1. Sleect the column by clicking on the column-head (grey-cell)
    2. Choose the command Edit | Replace...
    3. Select the tab Replace
    4. Int eh field for Find what enter '
    5. Leave the field for Replace with empty.
    6. Click on the OK-button.
    7. Done!

    Let us know the outcome of above.

    Kind regards,
    Dennis

  6. #6
    Join Date
    Dec 2003
    Location
    Östersund Sweden
    Posts
    60
    Hi,

    I dont think God has something to do with it and I believe it does not require any XL-Master but anyway here we go:

    Code:
    Option Explicit
    
    Sub Delete_Char()
    Dim wsSheet As Worksheet
    Dim rnData As Range
    Dim vaData As Variant
    Dim i As Long
    
    Set wsSheet = ActiveSheet
    
    With wsSheet
        'Here You change it to Your specific range
        Set rnData = .Range(.Range("A2"), .Range("A65536").End(xlUp))
    End With
    
    vaData = rnData.Value
    
    For i = LBound(vaData) To UBound(vaData)
        vaData(i, 1) = Right(vaData(i, 1), vaData(i, 1) - 1)
    Next i
    
    rnData = vaData
        
    End Sub
    At least it works in my enviroment.

    Kind regards,
    Dennnis
    Kind regards,
    Dennis

  7. #7
    Join Date
    Jan 2004
    Location
    Seattle, WA
    Posts
    4

    Talking Yeah, it worked

    Thanks Dennis! Just in time to, this puppy is due tomorrow.





    Originally posted by Xl-Dennis
    Hi,

    I dont think God has something to do with it and I believe it does not require any XL-Master but anyway here we go:

    Code:
    Option Explicit
    
    Sub Delete_Char()
    Dim wsSheet As Worksheet
    Dim rnData As Range
    Dim vaData As Variant
    Dim i As Long
    
    Set wsSheet = ActiveSheet
    
    With wsSheet
        'Here You change it to Your specific range
        Set rnData = .Range(.Range("A2"), .Range("A65536").End(xlUp))
    End With
    
    vaData = rnData.Value
    
    For i = LBound(vaData) To UBound(vaData)
        vaData(i, 1) = Right(vaData(i, 1), vaData(i, 1) - 1)
    Next i
    
    rnData = vaData
        
    End Sub
    At least it works in my enviroment.

    Kind regards,
    Dennnis

Posting Permissions

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