how to find replace text or symbol with carriage return

[jo]

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

 

[Ragdyer]

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
<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:
<Format> <Cells> <Alignment> tab,
And click in "Word Wrap", then <OK>.
--
HTH,

RD

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

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

return

 

[Rob]

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

 

[Gord Dibben]

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

 

[jo]

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]

Start>Run>charmap.exe

OR download Chip Pearson's Symbolizer Add-in

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


Gord Dibben Excel MVP

 

[jo]

how about other than symbols;

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

are these control codes or values.
thanks,

 

[Gord Dibben]

Jo

These are also control codes.

See here for most common.

http://www.asciitable.com/


Gord

 

[Robert AS]

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

 

[JE McGimpsey]

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

 

[Robert AS]

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