Spaces as Symbols (2003 sp2)

Topic

New Reply

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

Reply | Quote

Replies

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)

Reply | Quote

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.

Reply | Quote

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:

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

If you want to replace the boxes with code 4 with a different character, modify the code accordingly.

Reply | Quote

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),””)

Reply | Quote

The solutions worked. I see where I wasn’t connecting properly. Thanks guys!

Reply | Quote