Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Remove Characters in Cells for Selected Range with VBA

  1. #1
    Join Date
    3rd October 2010
    Location
    US
    Posts
    28

    Remove Characters in Cells for Selected Range with VBA

    Hi, I need to remove periods and/or hyphens from values in cells for a large range using vba.

    Using the substitute function works but it is taking too long because its looking at one cell at a time and I have over 30,000 cells to change.

    Can the substitute function be used on a range of cells instead??

    The values that will need changed do not have periods/hyphens in a consistent location and they can be mixed between numbers or letters and there can be multiple locations.

    Values may look like.

    ex. cell 1 --> ABC2.123-00A
    cell 2 --> 123.456.78-a
    cell 3 --> TX123.45.78A

    Thanks in advance for your help.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    9th September 2009
    Posts
    1,599

    Re: Remove Characters in Cells for Selected Range with VBA

    It may not be faster to do the replace in VBA since it has to operate on individual cells too. However you can try:
    VB:
     
    Option Explicit 
     
    Sub Remove() 
         
        Dim rgxRegExp As Object 
        Dim rngCell As Range, rngRange As Range 
         
        Set rngRange = [COLOR=blue]Sheet1.Range("A1:A5")[/COLOR] 
         
        Set rgxRegExp = CreateObject("VBScript.RegExp") 
        rgxRegExp.Global = True 
        rgxRegExp.Pattern = "\.|-" 
         
        With Application 
            .Calculation = xlCalculationManual 
            .EnableEvents = False 
            .ScreenUpdating = False 
        End With 
         
        For Each rngCell In rngRange.SpecialCells(xlCellTypeConstants) 
            rngCell.Value = rgxRegExp.Replace(rngCell.Value, vbNullString) 
        Next 
         
        With Application 
            .Calculation = xlCalculationAutomatic 
            .EnableEvents = True 
            .ScreenUpdating = True 
        End With 
         
    End Sub 
    
    
    Replace the text in blue with the range you wish to operate on. You could also use:
    VB:
    Set rngRange = Selection 
    
    
    If you prefer to select your range then run the VBA.

    An alternative to try would be:
    VB:
    Sub ReplaceInSelection() 
         
        Selection.Find(What:=".", After:=ActiveCell, LookIn:=xlValues, LookAt _ 
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ 
        False, SearchFormat:=False).Activate 
        Selection.Replace What:=".", Replacement:="", LookAt:=xlPart, _ 
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
        ReplaceFormat:=False 
        Selection.Replace What:="-", Replacement:="", LookAt:=xlPart, _ 
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
        ReplaceFormat:=False 
         
    End Sub 
    
    
    Which may be faster

  3. #3
    Join Date
    3rd October 2010
    Location
    US
    Posts
    28

    Re: Remove Characters in Cells for Selected Range with VBA

    Hi Rob Xaos,

    Thanks for your quick reply. Your last suggestion was awesome. I didn't think about it from that direction because I didn't know you could select an entire range and then perform the replace function. Dude that was great. Thanks so much. This opens up a whole lot of potential things for me.

    I do have one small problem though. Many of my numbers (but not all) start with a zero so I had to format the cells as text to keep the zero's but when I run your code I lose my leading zero.

    ex. 0123.456.789

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,897

    Re: Remove Characters in Cells for Selected Range with VBA

    Using replace is best and it did not remove the zero's for me.

    Anyway here is another method to try:

    VB:
    Sub HTH() 
         
        With Sheet1.UsedRange.Columns(1).Offset(, 1) 
            .FormulaR1C1 = "=SUBSTITUTE(SUBSTITUTE(RC[-1],""-"",""""),""."","""")" 
            .NumberFormat = "@" 
            .Value = .Value 
        End With 
         
    End Sub 
    
    

  5. #5
    Join Date
    9th September 2009
    Posts
    1,599

    Re: Remove Characters in Cells for Selected Range with VBA

    Unfortunately I am not aware of any way of preventing Replace from doing that. Reafidy's solution would be best in that case.

  6. #6
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,897

    Re: Remove Characters in Cells for Selected Range with VBA

    Interesting it must differ based on the version of excel, when I run your replace code excel automatically places an apostrophe in front of the zero. Im using Excel 2010.

  7. #7
    Join Date
    3rd October 2010
    Location
    US
    Posts
    28

    Re: Remove Characters in Cells for Selected Range with VBA

    Rob Xaos,

    I retried your first suggestion block with adjusting "xlCellTypeConstants" to "xlCellTypeVisable" and it worked great and it left the zero's as they were.

    I'm not familiar with "rngRange.SpecialCells(xlCellTypeConstants)". How does it work?

    Hi Reafidy,

    I haven't had a chance to try your suggestion but will momentarily. Thanks for an alternative. I am using Excel 2007 but will be running it also on a machine that has 2000.

    Thanks again Rob Xaos and Reafidy for your help and the quick response.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    3rd October 2010
    Location
    US
    Posts
    28

    Re: Remove Characters in Cells for Selected Range with VBA

    Rob Xaos,

    How would I pass a variable in for "\.|-" ?

    I thought that "\" and "|" were some of characters that were going to be replaced but it doesn't seem to work that way. I may have other characters as well so would they go in like:

    "\.|-|,|~|" ??? , or with variables "\strA|strB|strC|" ???

    VB:
    Set rgxRegExp = CreateObject("VBScript.RegExp") 
    rgxRegExp.Global = True 
    rgxRegExp.Pattern = "\.|-" 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    9th September 2009
    Posts
    1,599

    Re: Remove Characters in Cells for Selected Range with VBA

    That string is part of a VBScript Regular Expression Pattern property. The details for VBScript are at http://msdn.microsoft.com/en-us/libr...(v=VS.85).aspx and for the Pattern property at http://msdn.microsoft.com/en-us/libr...(v=VS.85).aspx

    For "\.|-":
    \ Means the next character is a special character or literal. In this case <period> is a special character for a RegExp pattern so needs to be preceeded by \
    . Normally this means match any single character apart from newline. Since it is preceeded by \ then it means the <period> character
    | Means either or. So x|y means match x or y
    - Is just the <hyphen> character

    So the entire pattern means match <period> or <hyphen>

    So to add more characters you do just add them with | as a separator but don't end with | End with a searchable character. However you need to check as some characters are special to the RegExp Pattern property so they have to be entered by preceeding them with \

  10. #10
    Join Date
    3rd October 2010
    Location
    US
    Posts
    28

    Re: Remove Characters in Cells for Selected Range with VBA

    Rob Xaos,

    Thanks for the explanation on what the characters mean and why. Also thank you giving me the links to get additional information on the VBScript Regular Expression. This will be very helpful.

    I really like knowing how things work and not just using the code. It usually opens many other doors if I understand why.

    Thanks again so much Rob Xaos

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 2 users browsing this thread. (0 members and 2 guests)

Possible Answers

  1. Replies: 11
    Last Post: February 18th, 2014, 23:56
  2. Replies: 5
    Last Post: August 10th, 2009, 04:14
  3. Remove Last X Characters From Cells
    By Gemma86 in forum EXCEL HELP
    Replies: 3
    Last Post: February 10th, 2009, 08:13
  4. Remove Last 2 Characters From Cells
    By Naz121 in forum EXCEL HELP
    Replies: 3
    Last Post: June 17th, 2008, 10:55
  5. Remove 1st X Characters From Text In Range
    By lowtusmaximus in forum EXCEL HELP
    Replies: 7
    Last Post: March 2nd, 2008, 08:30

Bookmarks

Posting Permissions

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