How to delete a 'return' symbol in cell for data thats been import
 

When I download contact address information from Outlook to an XL file, many
times there appears a square 'box' character in the cell where either (a) a
comma would have been separating street from say, suite # in the same line or
(b) where a return would have been (street address <return city/state/zip)
in the address card. I have tried to search and replace to change or delete
the character, but I am unable to grab it, and thus have to manually adjust
each cell. A daunting task with 4000+ entries....

The box character only appears in the formula field bar -- when viewing the
cell in the sheet, it appears as two vertical lines (like a double lower case
L).

What is this character called how do I get rid of it??? Thx

How to delete a 'return' symbol in cell for data thats been import
 

The carriage returns are trying to 'wrap' text in cell where text wrapping is
not on. Try formatting the cell with wrapping text. NOTE: This will make your
rows larger, as the cell will now double in size to accomodate the data.
--
John C


"JaxHBT" wrote:

When I download contact address information from Outlook to an XL file, many
times there appears a square 'box' character in the cell where either (a) a
comma would have been separating street from say, suite # in the same line or
(b) where a return would have been (street address <return city/state/zip)
in the address card. I have tried to search and replace to change or delete
the character, but I am unable to grab it, and thus have to manually adjust
each cell. A daunting task with 4000+ entries....

The box character only appears in the formula field bar -- when viewing the
cell in the sheet, it appears as two vertical lines (like a double lower case
L).

What is this character called how do I get rid of it??? Thx

How to delete a 'return' symbol in cell for data thats been import
 

Use this =Clean(A1)

Maybe this will help you


"JaxHBT" wrote:

When I download contact address information from Outlook to an XL file, many
times there appears a square 'box' character in the cell where either (a) a
comma would have been separating street from say, suite # in the same line or
(b) where a return would have been (street address <return city/state/zip)
in the address card. I have tried to search and replace to change or delete
the character, but I am unable to grab it, and thus have to manually adjust
each cell. A daunting task with 4000+ entries....

The box character only appears in the formula field bar -- when viewing the
cell in the sheet, it appears as two vertical lines (like a double lower case
L).

What is this character called how do I get rid of it??? Thx

How to delete a 'return' symbol in cell for data thats been import
 

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

JaxHBT wrote:

When I download contact address information from Outlook to an XL file, many
times there appears a square 'box' character in the cell where either (a) a
comma would have been separating street from say, suite # in the same line or
(b) where a return would have been (street address <return city/state/zip)
in the address card. I have tried to search and replace to change or delete
the character, but I am unable to grab it, and thus have to manually adjust
each cell. A daunting task with 4000+ entries....

The box character only appears in the formula field bar -- when viewing the
cell in the sheet, it appears as two vertical lines (like a double lower case
L).

What is this character called how do I get rid of it??? Thx

--

Dave Peterson

How to delete a 'return' symbol in cell for data thats been im
 

Thx; however, that just made all the text appear and rows larger as you said,
but does not take out or change the "||" the character that is appearing.

"John C" wrote:

The carriage returns are trying to 'wrap' text in cell where text wrapping is
not on. Try formatting the cell with wrapping text. NOTE: This will make your
rows larger, as the cell will now double in size to accomodate the data.
--
John C


"JaxHBT" wrote:

When I download contact address information from Outlook to an XL file, many
times there appears a square 'box' character in the cell where either (a) a
comma would have been separating street from say, suite # in the same line or
(b) where a return would have been (street address <return city/state/zip)
in the address card. I have tried to search and replace to change or delete
the character, but I am unable to grab it, and thus have to manually adjust
each cell. A daunting task with 4000+ entries....

The box character only appears in the formula field bar -- when viewing the
cell in the sheet, it appears as two vertical lines (like a double lower case
L).

What is this character called how do I get rid of it??? Thx

How to delete a 'return' symbol in cell for data thats been im
 

thank you! will try those

"Dave Peterson" wrote:

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

JaxHBT wrote:

When I download contact address information from Outlook to an XL file, many
times there appears a square 'box' character in the cell where either (a) a
comma would have been separating street from say, suite # in the same line or
(b) where a return would have been (street address <return city/state/zip)
in the address card. I have tried to search and replace to change or delete
the character, but I am unable to grab it, and thus have to manually adjust
each cell. A daunting task with 4000+ entries....

The box character only appears in the formula field bar -- when viewing the
cell in the sheet, it appears as two vertical lines (like a double lower case
L).

What is this character called how do I get rid of it??? Thx

--

Dave Peterson