Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Best way to clean up text (XL 2003)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Loungers

    I have a set of text that is coming from a main frame text editor. Thank God that the text editor is using Unicode. But again I need to strip all these bad characters and dingbats.

    Would a Select Case statement in VBA do the job if I have almost 30,000 terms to clean?
    I am thinking of looping character by character and evaluating it to the CHR(xxx) that I want to remove, which is a lot. Ultimately I want only A-Z and a-z left.

    Or option 2 would be to write a Humongous worksheet formula to substitute "" for each of the unwanted characters. I think this option has to also loop through each character of the string in the cell.

    Or could MS-Word do something with Find - Replace

    So, what would you do?

    Thanks a <img src=/S/money.gif border=0 alt=money width=17 height=15>

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Best way to clean up text (XL 2003)

    You can use the following to automate the Find and Replace (Ctrl+H - same as in MS Word) feature in Excel
    Replace the "abcxyz" below with the actual characters or chr()s you wish to replace in the sheet.
    <pre>Sub MassReplace()
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
    Dim strRep As String, strCharToRep As String, strChars() As String
    Dim i As Integer, j As Integer


    strCharToRep = <font color=red>"abcxyz"</font color=red> 'add characters here

    ReDim strChars(Len(strCharToRep)) 'loads array
    For j = 1 To Len(strCharToRep)
    strChars(j) = Mid(strCharToRep, j, 1)
    Next


    For i = 1 To UBound(strChars)
    strRep = strChars(i)
    Cells.Replace What:=strRep, Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    Next
    Application.ScreenUpdating = True
    Application.Calculation = xlAutomatic
    End Sub
    </pre>


  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Best way to clean up text (XL 2003)

    I forgot to mention that you would probably have to build the strCharToRep = "abcxyz" as"

    strCharToRep = chr(123) & chr (234) & etc.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Best way to clean up text (XL 2003)

    The code below should go through every cell in the current selection and remove all characters except alphabetic, numerals, and normal special characters like spaces, comma, period, etc.

    <code>
    Public Sub CleanText()
    Dim oCell As Range
    Dim I As Integer, iCH As Integer
    Dim strWK As String
    For Each oCell In Selection
    strWK = oCell.Value
    If Len(strWK) > 0 Then
    For I = Len(strWK) To 1 Step -1
    iCH = Asc(Mid(strWK, I, 1))
    If iCH < 32 Or iCH > 122 Then
    Mid(strWK, I, 1) = ""
    End If
    Next I
    End If
    oCell.Value = strWK
    Next oCell
    End Sub
    </code>


    Be sure to try it on a copy of the workbook first to make sure it works like you want.
    Legare Coleman

  5. #5
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Best way to clean up text (XL 2003)

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> mbarron and Legare

    I had reached a similar code snipit because the worksheet formula I started with turned out to be really and truly convoluted. I could not keep up with it, so I wrote a User Function in VBA similar to both of your code.

    Thanks again

    cordially

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  6. #6
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Best way to clean up text (XL 2003)

    I have a spreadsheet which is from an ArcGIS dbf file with a lot of square symbols in it. I used your macro and it worked on all the cells with just sqares in them. Thanks for the code!
    It didn't work with cells with a square at the end; e.g., 352-796-9423'square symbol". Any ideas on clean those up? I tried to copy the symbol and paste to do a search/replace, but it didn't copy. Any suggestions?
    Thanks.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Best way to clean up text (XL 2003)

    Try this variation of Legare's code:
    <code>
    Public Sub CleanText()
    Dim oCell As Range
    Dim I As Integer, iCH As Integer
    Dim strIn As String
    Dim strOut As String
    For Each oCell In Selection
    strIn = oCell.Value
    strOut = ""
    If Len(strIn) > 0 Then
    For I = 1 To Len(strIn)
    iCH = Asc(Mid(strIn, I, 1))
    If iCH > 31 And iCH < 123 Then
    strOut = strOut & Mid(strIn, I, 1)
    End If
    Next I
    End If
    oCell.Value = strOut
    Next oCell
    End Sub</code>

Posting Permissions

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