Vertical Lines in Cells - Delimited???

Can't find the vertical line character anywhere to place in the find/replace.
Can delete from each cell in the formula bar, but then the column widths go
whacky.

"Dave Peterson" wrote:

> Edit|Replace???
>
> Challenger wrote:
> >
> > I have a spreadsheet that has been formatted from a Crystal Report. There is
> > a vertical text line at the right hand side of several of the cells. I want
> > a quick way to remove these lines so that the data can be copied and used in
> > formulas and macros.
>
> --
>
> Dave Peterson
>

> Can't find the vertical line character anywhere to place in the
> find/replace.

On my U.S. keyboard, it's on the right-most key in the "Q" row as the upper
("shifted") symbol. On the key-top it looks like a two-part line, but on
the screen it's a one-part "vertical line" character.


> Can delete from each cell in the formula bar, but then the column
> widths go whacky.

Not sure what your "wacky" looks like, or what you're trying to achieve,
but maybe replacing "vertical line" by a space character and using a
Courier font will help.

And on my USA keyboard, I have a (broken) vertical bar on the backslash key
(shift-backslash key). It's directly above the Enter key.

I don't know why the columnwidths would go wacky, though.

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.aspx

You may be able to use Edit|Replace to change the character--Some characters can
be entered by holding the alt-key and typing the hex number on the numeric
keypad. For example, alt-0010 (or ctrl-j) can be used for linefeeds. But I've
never been able to get alt-0013 to work for carriage returns.

Another alternative is to fix it via a formula:

=substitute(a1,char(##),"")
or
=substitute(a1,char(##)," ")

Replace ## 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(##), Chr(##)) '<--What showed up in CellView?

myGoodChars = Array(" ","") '<--what's the new character, "" for nothing?

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:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Challenger wrote:
>
> Can't find the vertical line character anywhere to place in the find/replace.
> Can delete from each cell in the formula bar, but then the column widths go
> whacky.
>
> "Dave Peterson" wrote:
>
> > Edit|Replace???
> >
> > Challenger wrote:
> > >
> > > I have a spreadsheet that has been formatted from a Crystal Report. There is
> > > a vertical text line at the right hand side of several of the cells. I want
> > > a quick way to remove these lines so that the data can be copied and used in
> > > formulas and macros.
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Thank you very much to both of you. I now know how to remove the vertical
lines and can write a macro to do that for me. I am trying to create macros
for copying cell ranges between two worksheets and workbooks. I have other
posts addressing the problems I'm having with that too. If you can help
there it would be very much appreciated.

Living and learning more than I want to?

"Dave Peterson" wrote:

> And on my USA keyboard, I have a (broken) vertical bar on the backslash key
> (shift-backslash key). It's directly above the Enter key.
>
> I don't know why the columnwidths would go wacky, though.
>
> 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.aspx
>
> You may be able to use Edit|Replace to change the character--Some characters can
> be entered by holding the alt-key and typing the hex number on the numeric
> keypad. For example, alt-0010 (or ctrl-j) can be used for linefeeds. But I've
> never been able to get alt-0013 to work for carriage returns.
>
> Another alternative is to fix it via a formula:
>
> =substitute(a1,char(##),"")
> or
> =substitute(a1,char(##)," ")
>
> Replace ## 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(##), Chr(##)) '<--What showed up in CellView?
>
> myGoodChars = Array(" ","") '<--what's the new character, "" for nothing?
>
> 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:
>
> Debra Dalgleish has some notes how to implement macros here:
> http://www.contextures.com/xlvba01.html
>
> David McRitchie has an intro to macros:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> Ron de Bruin's intro to macros:
> http://www.rondebruin.nl/code.htm
>
> (General, Regular and Standard modules all describe the same thing.)
>
> Challenger wrote:
> >
> > Can't find the vertical line character anywhere to place in the find/replace.
> > Can delete from each cell in the formula bar, but then the column widths go
> > whacky.
> >
> > "Dave Peterson" wrote:
> >
> > > Edit|Replace???
> > >
> > > Challenger wrote:
> > > >
> > > > I have a spreadsheet that has been formatted from a Crystal Report. There is
> > > > a vertical text line at the right hand side of several of the cells. I want
> > > > a quick way to remove these lines so that the data can be copied and used in
> > > > formulas and macros.
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>