excel 2003
how to find text or symbol in each cell and replace with hard carriage return

[ExcelBanter AI]

Here are the steps to find and replace text or symbol with a carriage return in Excel 2003:

1. Open your Excel file and select the range of cells where you want to find and replace the text or symbol.
2. Press the Ctrl + H keys on your keyboard to open the Find and Replace dialog box.
3. In the Find what field, enter the text or symbol that you want to replace with a carriage return.
4. In the Replace with field, press the Alt + Enter keys on your keyboard to insert a carriage return.
5. Click on the Replace All button to replace all instances of the text or symbol with a carriage return.
6. Once the replacement is complete, click on the Close button to close the Find and Replace dialog box.

That's it! You have successfully replaced the text or symbol with a carriage return in Excel 2003.

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:

<Edit <Replace
In the "Find What" box, enter
#
And in the "Replace With" box, enter
<ALT0010
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:
<Format <Cells <Alignment tab,
And click in "Word Wrap", then <OK.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"jo" wrote in message
...
excel 2003
how to find text or symbol in each cell and replace with hard carriage
return

I have this same questions and This answer did not not work for me. It is
inserting"<alt0010". Not a hard return. Please help

"jo" wrote:

excel 2003
how to find text or symbol in each cell and replace with hard carriage return

Rob

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.

Gord Dibben Excel MVP


On Mon, 3 Jan 2005 11:27:02 -0800, Rob wrote:

I have this same questions and This answer did not not work for me. It is
inserting"<alt0010". Not a hard return. Please help

"jo" wrote:

excel 2003
how to find text or symbol in each cell and replace with hard carriage return

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?
jo




"Gord Dibben" wrote:

Rob

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.

Gord Dibben Excel MVP


On Mon, 3 Jan 2005 11:27:02 -0800, Rob wrote:

I have this same questions and This answer did not not work for me. It is
inserting"<alt0010". Not a hard return. Please help

"jo" wrote:

excel 2003
how to find text or symbol in each cell and replace with hard carriage return

StartRuncharmap.exe

OR download Chip Pearson's Symbolizer Add-in

http://www.cpearson.com/excel/chars.htm


Gord Dibben Excel MVP

On Tue, 4 Jan 2005 15:43:02 -0800, "jo" wrote:

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?
jo




"Gord Dibben" wrote:

Rob

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.

Gord Dibben Excel MVP


On Mon, 3 Jan 2005 11:27:02 -0800, Rob wrote:

I have this same questions and This answer did not not work for me. It is
inserting"<alt0010". Not a hard return. Please help

"jo" wrote:

excel 2003
how to find text or symbol in each cell and replace with hard carriage return

how about other than symbols;

like carriage hard returns, soft returns, indents, tabs, c

are these control codes or values.
thanks,


"Gord Dibben" wrote:

StartRuncharmap.exe

OR download Chip Pearson's Symbolizer Add-in

http://www.cpearson.com/excel/chars.htm


Gord Dibben Excel MVP

On Tue, 4 Jan 2005 15:43:02 -0800, "jo" wrote:

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?
jo




"Gord Dibben" wrote:

Rob

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.

Gord Dibben Excel MVP


On Mon, 3 Jan 2005 11:27:02 -0800, Rob wrote:

I have this same questions and This answer did not not work for me. It is
inserting"<alt0010". Not a hard return. Please help

"jo" wrote:

excel 2003
how to find text or symbol in each cell and replace with hard carriage return

Jo

These are also control codes.

See here for most common.

http://www.asciitable.com/


Gord

On Sat, 8 Jan 2005 20:43:04 -0800, "jo" wrote:

how about other than symbols;

like carriage hard returns, soft returns, indents, tabs, c

are these control codes or values.
thanks,


"Gord Dibben" wrote:

StartRuncharmap.exe

OR download Chip Pearson's Symbolizer Add-in

http://www.cpearson.com/excel/chars.htm


Gord Dibben Excel MVP

On Tue, 4 Jan 2005 15:43:02 -0800, "jo" wrote:

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?
jo




"Gord Dibben" wrote:

Rob

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.

Gord Dibben Excel MVP


On Mon, 3 Jan 2005 11:27:02 -0800, Rob wrote:

I have this same questions and This answer did not not work for me. It is
inserting"<alt0010". Not a hard return. Please help

"jo" wrote:

excel 2003
how to find text or symbol in each cell and replace with hard carriage return

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 reead 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'm sure there is a way to get it to read the cell in sheet 2 I'm just not
getting it....please help. This is part of a much bigger problem and the only
pitfall I have.

Bob

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




In article ,
Robert AS wrote:

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 reead 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'm sure there is a way to get it to read the cell in sheet 2 I'm just not
getting it....please help. This is part of a much bigger problem and the only
pitfall I have.

Bob

Nope that didn't do it.

Got that same old 91 error.

I know the data in the cell in sheet 2 is in sheet 1 because it looked in
sheet one for it. But what I need is the data in sheet two to activate the
cell in sheet 1 so I get to work the data all around it as part of the loop.

I know there has to be a way to get the data in sheet 2 to make the cell in
sheet 1 that holds that data active with a find. I know the math I need just
not the code.

I know that when I record the macro it works because I have to place the
data in the "find-what-window-box" to record the data. but I don't know if
it reads that as a string (as in test string) or a value. I'm just guessing
because I do know if a set of numbers is in a alpha-numaric string it will
find that also (I know because I played with it enough to know that) , and
that sort of string is not a number value. "error 91" points to a set error
of some sort, but I don't know how to set the data in sheet 2 so the "find"
cxan see it in sheet 1, I'm just sure at this juncture it's not a "value"
that it wants. So if I have to set the value to a text string for the
select-find function......that would be what I need help with.

Looking back I do know I have tried your code once before but did again out
of hope.....one empty hand and one filling up.

I've found no books on this subject relating the SET command to the find
function

Bob


"JE McGimpsey" wrote:

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




In article ,
Robert AS wrote:

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 reead 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'm sure there is a way to get it to read the cell in sheet 2 I'm just not
getting it....please help. This is part of a much bigger problem and the only
pitfall I have.

Bob