How to remove carriage returns in Excel

[buzfar]

Hello;

I am using a mac, os 10.5.
Software is Excel 2008.

I have a ton of data that has carriage returns in it. I need to convert this into a tab delimited file, and the returns mess up the sheet. I need a way to remove the returns in excel as an .xls before I convert it.

Any solutions?

Answers much appreciated!!!!!!

 

[Gavona]

not sure about Excel 2008 but in 2003 I would:
Copy a return character
Edit, Replace... return with space

With lots of files simply record yourself doing this.


Gavin

 

[buzfar]

Gavin;

I have tried that and it does not work. I am not sure if this matters, but no "special character" is showing up, like the squares or what not. I have tried copy, paste into find/replace with no luck.

Thanks

 

[SkipVought]

What does ctr+* indicate is there?

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gavona]

Ok more detail:

1. In the edit view of the cell I have ALWAYS had success in highlighting the invisible return, Ctrl-C to copy to clipboard. Esc out of edit mode. Edit Replace, Ctrl-v to paste from clipboard......
2. The return character is normally Char(10) - you can code that into a macro.
3. Use =code(Mid(....) to discover an unknown character code.

Gavin

 

[buzfar]

SkipVought:

I get an error, saying "cannot show outline symbol because no outline exists on the active worksheet, do you want to create an outline". Choices are Cancel or OK. When I click OK. I get the error " cannot create an outline"

Gavona:

I did use the Ctl+C and Ctl+V, and it said nothing found.

As for the Char(10), how would I make that Macro?

For Use =code(Mid(....)? I am clueless as to what this is.

Thank you

 

[Gavona]

=Code(text) Returns a numeric code for the first character in a text string. The returned code corresponds to the character set used by your computer.
=MID(text,start_num,num_chars) returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.

=code(MID($A$1,n,1) will return the code for the nth character.

If you record a macro replacing a with a space you can then edit it to make it replace vhar(10) or whatever with a space.



Gavin

 

[buzfar]

Gavona:

I cannot even get the first replacement of the return... as for the explanations, thank you, but I am not familiar with coding or anything with macros. I am a formula kind of lady.

Any ideas? Or can you make what you stated earlier understandable to an idiot like myself?

Thank you

 

[AnotherHiggins]

No 'special character' will show up when you try to do a Find and Replace.

If you know for sure what character it is, you can just type in the code for that character into the FIND field of Find and Replace.

Carriage Return = 0013
Line Feed/New Line = 0010

So...[ul][li]Go to Edit > Replace (or [Ctrl] + [H])[/li]
[li]In the "Find What" field:[/li]
[ul][li]Hold down [Alt][/li]
[li]Using the keypad - Type in the appropriate code (either 0010 or 0013)[/li]
[/ul]
[li]In the "Replace With" field:[/li]
[ul][li]Type in whatever you like[/li]
[/ul][/ul]


[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.