Finding replace text or symbol with carriage return in Excel

[DEvOTeEL]

I am using Microsoft Excel 2003. I need to how to find text or symbol in each cell and replace with hard carriage return.

[QUENBY]

You could use edit and replace. Say that you wanted to replace the pound sign (#) with the hard line break (CHAR(10)). First, select the cells containing the data to be changed, then: click on Edit > Replace. In the "Find What" box, enter # and in the "Replace With" box, enter <ALT>0010. Using the numbers on the num keypad, *not* the numbers under the function keys. You will not see anything displayed in the "Replace With" box, But click "Replace All". You should see your data wrapped in the cell. If not, go to: click on Format> Cells > Alignment > tab and click in "Word Wrap", then > OK.

[GALIENA]

I have this same questions and This answer did not not work for me. It is inserting"<alt>0010". Not a hard return. When replacing with: hold the ALT key and type 0010 on the NumPad(gray keys on the right). You won't see anything typed in the box, but it is there.

[DanielaA]

Where can I find a list defining all the key codes / values like ALT 0010 = carriage return. I assume they will be the same for all MS office apps.

[GaganjyotTechie]

When I record a macro with the find function I get this :

Selection.Find(What:="874", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

What I need to do is replace the "874" with a value on sheet 2 and find the value in sheet 1. But I don't know how to do that. I've tried about everything I know and I get nothing that works. Things like Sheets("Sheet2").Range("A1").value calling it MyFindValue. Nothing been at this for weeks now. How can I get the macro to reread a value in sheet 2 to activate the same value in sheet 1? If I can get that to work I can get the rest of it to work. The macro is recorded as relative. I am sure there is a way to get it to read the cell in sheet 2 I am just no getting it please help. This is part of a much bigger problem and the only pitfall I have.

[Essien]

If you're sure that the value exists in the selection, you can use something like:

Selection.Find( _
What:=Sheets("Sheet2").Range("A1").Value, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:= False).Activate

However, if it doesn't, you'll get an error. You can use a variable to check if it exists:

Dim rFound As Range
Set rFound = Selection.Find( _
What:=Sheets("Sheet2").Range("A1").Value, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:= False)
If rFound Is Nothing Then
MsgBox """" & Sheets("Sheet2").Range("A1").Value & _
""" was not found."
Else
rFound.Activate
End If