Formatting Marks

B

BK

Using Windows XP-Pro and Office 2003

Is it possible to show/hide formatting marks in Excel? That option is
available in Word, but cannot seem to find it in Excel.

I have a client who has entered names and addresses in Excel to use as merge
data to labels via Word. She has entered the address information as two
lines in the same cell. Address is on first line, then she says she used
Alt+Enter to move to a second line for city, state, and zip information. (I
know!! I know!! I wish she had talked to me first, but the data base is
already created.)

I'd like to see the manual line break formatting so that I can try to make
this Excel data a little easier to merge onto labels via Word.
 
R

Ron Coderre

Try this to break the cells into 2 columns:

Make sure there is a blank column to the right of the cells with the line
breaks.
Select the single column of cells with the line breaks in it

<data><text-to-columns>
Check: Delimited......[Next]

Check: Other (uncheck any other boxes)
In the box next to Other.....
Hold down the [alt] key....type 0010..release the [alt] key
Click the [Finish] button

Note: ASCII code 0010 is the new line character

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
J

Jeff Standen

I don't think so, but you could use this to replace the line breaks with
pipes:

=SUBSTITUTE(F6,CHAR(10),"|")

And then use text to columns to split it based on the pipe delimiter.

Jeff
 
A

aidan.heritage

There isn't a command to show it, but it's just a carriage return
(paragraph mark in Word speak)
 
B

BK

I appreciate the advice.

So I guess you are telling me I cannot "show/hide" the formatting marks in
Excel like I can in Word?????




Ron Coderre said:
Try this to break the cells into 2 columns:

Make sure there is a blank column to the right of the cells with the line
breaks.
Select the single column of cells with the line breaks in it

<data><text-to-columns>
Check: Delimited......[Next]

Check: Other (uncheck any other boxes)
In the box next to Other.....
Hold down the [alt] key....type 0010..release the [alt] key
Click the [Finish] button

Note: ASCII code 0010 is the new line character

Does that help?
***********
Regards,
Ron

XL2002, WinXP


BK said:
Using Windows XP-Pro and Office 2003

Is it possible to show/hide formatting marks in Excel? That option is
available in Word, but cannot seem to find it in Excel.

I have a client who has entered names and addresses in Excel to use as
merge
data to labels via Word. She has entered the address information as two
lines in the same cell. Address is on first line, then she says she used
Alt+Enter to move to a second line for city, state, and zip information.
(I
know!! I know!! I wish she had talked to me first, but the data base is
already created.)

I'd like to see the manual line break formatting so that I can try to
make
this Excel data a little easier to merge onto labels via Word.
Click to expand...
Click to expand...
 
B

BK

When I open the "find/replace" dialog box, I cannot do a "find" for a manual
line break using the ^l that I use in Word. It says it cannot find what I
am searching for. Where would you want me to type the =substitute formula
you suggested?
 
R

Ron Coderre

Correct....Excel has no built-in mechanism for displaying non-printing
characters like MS Word has.

***********
Regards,
Ron

XL2002, WinXP


BK said:
I appreciate the advice.

So I guess you are telling me I cannot "show/hide" the formatting marks in
Excel like I can in Word?????




Ron Coderre said:
Try this to break the cells into 2 columns:

Make sure there is a blank column to the right of the cells with the line
breaks.
Select the single column of cells with the line breaks in it

<data><text-to-columns>
Check: Delimited......[Next]

Check: Other (uncheck any other boxes)
In the box next to Other.....
Hold down the [alt] key....type 0010..release the [alt] key
Click the [Finish] button

Note: ASCII code 0010 is the new line character

Does that help?
***********
Regards,
Ron

XL2002, WinXP


BK said:
Using Windows XP-Pro and Office 2003

Is it possible to show/hide formatting marks in Excel? That option is
available in Word, but cannot seem to find it in Excel.

I have a client who has entered names and addresses in Excel to use as
merge
data to labels via Word. She has entered the address information as two
lines in the same cell. Address is on first line, then she says she used
Alt+Enter to move to a second line for city, state, and zip information.
(I
know!! I know!! I wish she had talked to me first, but the data base is
already created.)

I'd like to see the manual line break formatting so that I can try to
make
this Excel data a little easier to merge onto labels via Word.
Click to expand...
Click to expand...
Click to expand...
 
A

aidan.heritage

I'm still not 100% sure why you need to alter the data - if it is in
that format, and is ending up on labels, that should be OK. BUT easy
replacement option - copy the cells. Paste into word. Fix the problem
in word. copy the cells again (they will be a table in word) then
paste BACK into Excel.
 
J

Jeff Standen

Put it in a column next to the one you want to split up. Or alternatively,
use Ron's solution, which is similar to mine but more elegant.

Jeff
 
D

Dave Peterson

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
 
B

BK

Actually, it's not a hard return or paragraph mark. It's Alt+Enter which
creates a new line in the same cell.
 
B

BK

Perfect!! Don't know why I didn't think of using Word to make the
adjustments. Thanks!!!
 
Top