How do I replace a BEL Character of 7 with a space
I have an extract from a Lotus Notes Database. When entering data into the
database, we used the enter key to separate paragraphs. When this data is placed into Excel, the carriage returns appear as either a BEL character of 7 or a DEL character of 127. Not quite sure, but it shows up as a square. I would like to remove all these characters and replace them with a space. If I use the Clean function, all of the characters (spaces) are removed, but this does not leave any spaces so words and paragrahs then become combined. I have tried a combination of the clean and substitute functions without any success. |
How do I replace a BEL Character of 7 with a space
Saved from a previous post:
Chip Pearson has a very nice addin that will help determine what that character(s) is: http://www.cpearson.com/excel/CellView.htm Since you do see a box, then you can either fix it via a helper cell or a macro: =substitute(a1,char(13),"") or =substitute(a1,char(13)," ") Replace 13 with the ASCII value you see in Chip's addin. Or you could use a macro (after using Chip's CellView addin): Option Explicit Sub cleanEmUp() Dim myBadChars As Variant Dim myGoodChars As Variant Dim iCtr As Long myBadChars = Array(Chr(10), Chr(13)) '<--What showed up in CellView? myGoodChars = Array(" "," ") '<--what's the new character? If UBound(myGoodChars) < UBound(myBadChars) Then MsgBox "Design error!" Exit Sub End If For iCtr = LBound(myBadChars) To UBound(myBadChars) ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _ Replacement:=myGoodChars(iCtr), _ LookAt:=xlPart, SearchOrder:=xlByRows, _ MatchCase:=False Next iCtr End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm ------- Sometimes those funny characters don't work in the edit|Find dialog. alt-0010 (or ctrl-j) (aka: alt-enters) work ok. char(13) has never worked for me. KBear wrote: I have an extract from a Lotus Notes Database. When entering data into the database, we used the enter key to separate paragraphs. When this data is placed into Excel, the carriage returns appear as either a BEL character of 7 or a DEL character of 127. Not quite sure, but it shows up as a square. I would like to remove all these characters and replace them with a space. If I use the Clean function, all of the characters (spaces) are removed, but this does not leave any spaces so words and paragrahs then become combined. I have tried a combination of the clean and substitute functions without any success. -- Dave Peterson |
All times are GMT +1. The time now is 01:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com