Remove Hard returns in Excel

[jannecum]

Using Excel '97 and I have Hard returns ([Alt] + {Enter]) and they show up as small squares in excel cells. I would like to search & replace them with "; " but I am not having any luck. I have tried to copy the little square and paste it into the search field but it will not copy... Any directions would be welcomed.

 

[GlennUK]

Do menu command Edit/Replace, and for the Find string hold down Alt and do 0010 on the numeric keypad ( nothing will show in the box, but carry on anyway ), and put "; " as the replacement string, and press Replace All.

Cheers, Glenn.

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

 

[jannecum]

Thank you Glenn - that seems to work pretty good. Where can I locate a list of code number combinations for this type of task?

Julie C

 

[AnotherHiggins]

You can Google for ASCII codes. But perhaps a better way is to use Excel itself to find out what particular character you are dealing with.

Lets say you have something that looks like this in a cell:
abcdef
ghijklm

To be sure what the "return" character is, press [F2] to enter the cell and use the arrow keys to count which character it is you want to replace.

In the above example, it is the 7th character.

So in a different cell, you can put this formula:
[tab][COLOR=blue white]=CODE(MID(A1,7,1))[/color]
which returns 10.

Now you know to use 0010 for the Find. If the formula had returned 13 (a carriage return), you would use 0013.

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

 

[jannecum]

Thanks John, That is very helpful.