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.

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