Consulting

Results 1 to 9 of 9

Thread: Solved: Help with replace [a-z][A-Z][0-9]

  1. #1
    VBAX Newbie
    Joined
    Jan 2011
    Posts
    2
    Location

    Solved: Help with replace [a-z][A-Z][0-9]

    After searching through the forum I couldn't find any leads for what I want to do. I figure there's a way to do this without going to REGEX.

    I would like to replace the non-alphabetical and non-numerical characters (those other than [a-z][A-Z][0-9]) with "" for the contents of cell A1.

    Can anyone point me to a previous post or provide some code to get me going? I am using Excel 2007.

    Thanks in advance.

  2. #2
    VBAX Tutor
    Joined
    Nov 2006
    Location
    North East Pennsylvania, USA
    Posts
    203
    Location
    egood,

    Welcome to the VBA Express forum.

    To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

    The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

    To attach your workbook scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.


    Have a great day,
    Stan

  3. #3
    VBAX Contributor
    Joined
    May 2010
    Location
    Sydney, NSW, Australia
    Posts
    170
    Location
    This should work:

    [vba]
    Sub RemoveExtendedChars()
    For X = 1 To 47
    Range("A1").Formula = Replace(Range("A1").Text, Chr(X), "")
    Next
    For X = 58 To 64
    Range("A1").Formula = Replace(Range("A1").Text, Chr(X), "")
    Next
    For X = 91 To 255
    Range("A1").Formula = Replace(Range("A1").Text, Chr(X), "")
    Next
    End Sub
    [/vba]

  4. #4
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,027
    Location
    Try:
    [VBA]Sub RemoveExtendedChars()
    Dim oCel As Range
    'In the Unicode character set, there are additional nonprinting
    'characters (values 127, 129, 141, 143, 144, and 157). By itself,
    'the Clean function does not remove these additional nonprinting characters.
    For Each oCel In Selection
    oCel.Formula = WorksheetFunction.Clean(oCel.Formula)
    Next
    End Sub[/VBA]
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  5. #5
    VBAX Contributor
    Joined
    May 2010
    Location
    Sydney, NSW, Australia
    Posts
    170
    Location
    Quote Originally Posted by macropod
    Try:
    [vba]Sub RemoveExtendedChars()
    Dim oCel As Range
    'In the Unicode character set, there are additional nonprinting
    'characters (values 127, 129, 141, 143, 144, and 157). By itself,
    'the Clean function does not remove these additional nonprinting characters.
    For Each oCel In Selection
    oCel.Formula = WorksheetFunction.Clean(oCel.Formula)
    Next
    End Sub[/vba]
    Nice one, I never knew about the Clean function

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    But how does that get rid of characters such as \ ? # etc.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,027
    Location
    Hi xld,

    It wouldn't - but is that actually what the OP actually wants to do? I'll grant that the problem description suggests so, though, and that even spaces, commas & periods would be deleted. Deleting periods could have intersting effects with decimal values!
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    Exactly, he said ... I would like to replace the non-alphabetical and non-numerical characters (those other than [a-z][A-Z][0-9]) ..., so why he is happy with something that doesn't do that?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,027
    Location
    Mine is not to reason why ...
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

Posting Permissions

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