Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Spaces as Symbols (2003 sp2)

    I have a excel sheet where I am performing a query against an Access table. The query is returning the  symbol for some of the spaces in a large text field. I want to remove the symbol and replace it with a space, but I can't figure out how. I tried FIND/REPLACE (simple right?) and it didn't work. Where can I go? Sample below, thanks.

    1162427193chanspPer request I have created the new custom afq numbers instead of old sungard numbers. After moving the new custom afq to prod I have removed the old sungard numbers from clearcase and we are asking the sungard to remove the old numbers from prod asp servers as well now.

    Former New
    Core number Custom number

    r05_104 r05_604
    r06_112 r06_510
    r06_114 r06_614
    r06_167 r06_552
    r29_131 r29_531
    r29_160 r29_560
    r29_161 r29_561
    r33_005 r33_605
    
    thanks
    christine

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

    Re: Spaces as Symbols (2003 sp2)

    Copy a single box symbol to a cell, say A1. In another cell, enter the formula =CODE(A1). This should return the ASCII/ANSI code for the box character. Which value do you get? (From the text in your post, it appears to be 4, but that is unlikely to come from Access)

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Spaces as Symbols (2003 sp2)

    I do get a 4 returned on the formula. I tried the REPLACE and REPLACEB formulas also. No go on those. I attached an actual excel sample dump of the data so you could see it more clearly. thanks.
    thanks
    christine

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

    Re: Spaces as Symbols (2003 sp2)

    OK. The box at the end of the cells has code 3; I assume you want to remove this. You can run this little macro:
    <code>
    Sub ReplaceEm()
    Dim oCell As Range
    For Each oCell In ActiveSheet.UsedRange
    oCell.Value = Replace(oCell.Value, Chr(4), " ")
    oCell.Value = Replace(oCell.Value, Chr(3), "")
    Next oCell
    End Sub
    </code>
    If you want to replace the boxes with code 4 with a different character, modify the code accordingly.

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

    Re: Spaces as Symbols (2003 sp2)

    The box in the middle are 4's and the boxes on the end are to be 3's. You can eliminate them using the Substitute function

    =SUBSTITUTE(SUBSTITUTE(A1,CHAR(4)," "),CHAR(3),"")

  6. #6
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Spaces as Symbols (2003 sp2)

    The solutions worked. I see where I wasn't connecting properly. Thanks guys!
    thanks
    christine

Posting Permissions

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