Text Edit in Excel 2

[Maceleven]

I have imported an address field from a csv file created by Filemaker. The carriage returns at the end of each line have appeared as "boxes" see below. I wish to replace with another character and then use "Text to Columns" to seperate the lines.

How do I define the "box" in the Find and Replace function?

7 Weston AvenueSt. George,Bristol,BS5 8DW

 

[Lilliabeth]

copy it and paste it

 

[GlennUK]

If the character is a Newline character it will have the value the same as the result of CHAR(10), so you could use a formula of:
=SUBSTITUTE(A1,CHAR(10),"#")
and copy down as required. Then do Copy/Paste Special/Values of that column before doing your Text to Columns.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[xlhelp]

=SUBSTITUTE(A2, CHAR(13)&CHAR(10),",")

http://www.geniusmedia.ca

 

[Maceleven]

Thanks for the clues. On my computer (UK, XP) it is CHAR(11) that does the trick. Subsidiary question, in Excel the expression CHAR(1) through to CHAR(31) all show the "box" symbol. What shouls they be.

Thanks again

 

[AnotherHiggins]

http://protein.bio.puc.cl/cardex/home/ascii.html

[tt]_____
[blue]-John[/blue][/tt]

"Patriotism is your conviction that this country is superior to all other countries because you were born in it."
[tab]-George Bernard Shaw

Help us help you. Please read FAQ181-2886 before posting.

 

[BenRowe]

Just curious, but could you have copied the box and used it as your separator character?

 

[GlennUK]

Hi BenRowe,

on my computer, when you go into Edit mode, the "box" transforms into a real Newline, i.e. the text breaks at that point ... there is nothing to select, so you can't copy the "box".

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.