Re: removing the "ALT+ENTER"?

Bob Phillips

unread,

Nov 14, 2006, 4:42:16 AM11/14/06

to

=SUBSTITUTE(A1,CHAR(10),"")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"salvo00" <sal...@discussions.microsoft.com> wrote in message
news:8DC1728A-67E8-48AC...@microsoft.com...
> How can I remove the "ALT+ENTER - line break" from a list of cells ?

salvo00

unread,

Nov 14, 2006, 6:04:02 AM11/14/06

to

Thank you Bob.
The formula replaces the line-break with a small square.
I don't want that symbol. I just want a space and all the text in 1 line.
Is there a solution?
Salvo

Bob Phillips

unread,

Nov 14, 2006, 6:45:57 AM11/14/06

to

Not for me it doesn't, are you sure it is Alt-Enter?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"salvo00" <sal...@discussions.microsoft.com> wrote in message

news:22AD6446-C37A-4F2A...@microsoft.com...

Dave Peterson

unread,

Nov 14, 2006, 9:20:45 AM11/14/06

to

Maybe it's not an alt-enter.

I'd try this next:
=SUBSTITUTE(A1,CHAR(13),"")

If that doesn't work...

Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.htm

Then you can use that info in the correct formula.

--

Dave Peterson

salvo00

unread,

Nov 14, 2006, 11:24:02 AM11/14/06

to

Thanks.
Unfortunately it is not an alt+enter.
After testing both char(10 or 13) I always get at the end of the phrase a
small square (cube).
The cells have been exported from the MS-Access.
In each cell there were 3,4 phrases. I'm trying to get a non-formatted text
(no line breaks) but it seems impossible.
Salvo

Dave Peterson

unread,

Nov 14, 2006, 11:28:18 AM11/14/06

to

What did you find from Chip's addin?

--

Dave Peterson

Bob Phillips

unread,

Nov 14, 2006, 11:39:55 AM11/14/06

to

Do a =CODE(MID(A1,4,1))

to get the character code (change 4 to where it actually is)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"salvo00" <sal...@discussions.microsoft.com> wrote in message

news:B82C80D4-D87D-4FE0...@microsoft.com...

salvo00

unread,

Nov 14, 2006, 11:57:02 AM11/14/06

to

This small cube is also presented as a vertical line ( a bold capital I).

Dave Peterson

unread,

Nov 14, 2006, 1:34:06 PM11/14/06

to

Was was the code for that character--using Chip's addin will tell you.

--

Dave Peterson

Gord Dibben

unread,

Nov 14, 2006, 4:10:20 PM11/14/06

to

Maybe a "pipe" from pipe-delimited.

Code 0124

Gord Dibben MS Excel MVP

On Tue, 14 Nov 2006 08:57:02 -0800, salvo00 <sal...@discussions.microsoft.com>
wrote:

salvo00

unread,

Nov 15, 2006, 2:46:01 AM11/15/06

to

I found out that there are several characters codes: 10, 13, 32
Doing =SUBSTITUTE with all characters (i have found) solves the problem.
I am not sure yet but I am testing it ..
Thanks very much for all your help.

Bob Phillips

unread,

Nov 15, 2006, 6:50:33 AM11/15/06

to

32 is a space isn't it, probably not a good idea to remove that.

Also, in case you don't know, you can embed the subs

=SUBSTITUTE(SUBSTITUTE(A1,CHAR(10),""),CHAR(13),"")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"salvo00" <sal...@discussions.microsoft.com> wrote in message

news:900E1676-A79C-4351...@microsoft.com...