limit characters returned a cell reference
 

I have a column that has several lines of text in it. Each row of text has a
soft return (Alt-Enter) to force certain text to go to the next line.

Now I want to do a cell reference that only shows the text on the first row.
I found the "Right", "Left" and "Mid" function, but those seem to only be
able to limit by the number of characters and the number of characters in
the first row of text varies greatly from cell to cell.

I thought I could combine the "Search" or "Find" function to look for the
soft return, return the position of the return and place it in the "Left"
function, but I can not figure out how to search for the soft return.

Any suggestions?

--
Lila

limit characters returned a cell reference
 

look for char(10) in your =search() or =find().



Lila wrote:

I have a column that has several lines of text in it. Each row of text has a
soft return (Alt-Enter) to force certain text to go to the next line.

Now I want to do a cell reference that only shows the text on the first row.
I found the "Right", "Left" and "Mid" function, but those seem to only be
able to limit by the number of characters and the number of characters in
the first row of text varies greatly from cell to cell.

I thought I could combine the "Search" or "Find" function to look for the
soft return, return the position of the return and place it in the "Left"
function, but I can not figure out how to search for the soft return.

Any suggestions?

--
Lila

--

Dave Peterson

limit characters returned a cell reference
 

Oh wait... I answered my own question. I just placed an actual soft return
in quotes in the Search function. It looks a bit odd, but it works!

=LEFT($E5,(SEARCH("
",E5)))

Now I just have to figure out if there is a way to nest it in an "index" and
"match" formula.


"Lila" wrote in message
...
I have a column that has several lines of text in it. Each row of text has
a
soft return (Alt-Enter) to force certain text to go to the next line.

Now I want to do a cell reference that only shows the text on the first
row.
I found the "Right", "Left" and "Mid" function, but those seem to only be
able to limit by the number of characters and the number of characters in
the first row of text varies greatly from cell to cell.

I thought I could combine the "Search" or "Find" function to look for the
soft return, return the position of the return and place it in the "Left"
function, but I can not figure out how to search for the soft return.

Any suggestions?

--
Lila

limit characters returned a cell reference
 

Is char(10) the code for the soft return?


"Dave Peterson" wrote in message
...
look for char(10) in your =search() or =find().



Lila wrote:

I have a column that has several lines of text in it. Each row of text
has a
soft return (Alt-Enter) to force certain text to go to the next line.

Now I want to do a cell reference that only shows the text on the first
row.
I found the "Right", "Left" and "Mid" function, but those seem to only
be
able to limit by the number of characters and the number of characters
in
the first row of text varies greatly from cell to cell.

I thought I could combine the "Search" or "Find" function to look for
the
soft return, return the position of the return and place it in the
"Left"
function, but I can not figure out how to search for the soft return.

Any suggestions?

--
Lila

--

Dave Peterson

limit characters returned a cell reference
 

It must be... because it works the great... and it looks better too.

=LEFT($E5,(SEARCH(CHAR(10),E5)))


"Lila" wrote in message
...
Is char(10) the code for the soft return?


"Dave Peterson" wrote in message
...
look for char(10) in your =search() or =find().



Lila wrote:

I have a column that has several lines of text in it. Each row of text
has a
soft return (Alt-Enter) to force certain text to go to the next line.

Now I want to do a cell reference that only shows the text on the
first
row.
I found the "Right", "Left" and "Mid" function, but those seem to only
be
able to limit by the number of characters and the number of characters
in
the first row of text varies greatly from cell to cell.

I thought I could combine the "Search" or "Find" function to look for
the
soft return, return the position of the return and place it in the
"Left"
function, but I can not figure out how to search for the soft return.

Any suggestions?

--
Lila

--

Dave Peterson

limit characters returned a cell reference
 

It's the same as alt-enter or linefeed, yep.

Lila wrote:

Is char(10) the code for the soft return?

"Dave Peterson" wrote in message
...
look for char(10) in your =search() or =find().



Lila wrote:

I have a column that has several lines of text in it. Each row of text
has a
soft return (Alt-Enter) to force certain text to go to the next line.

Now I want to do a cell reference that only shows the text on the first
row.
I found the "Right", "Left" and "Mid" function, but those seem to only
be
able to limit by the number of characters and the number of characters
in
the first row of text varies greatly from cell to cell.

I thought I could combine the "Search" or "Find" function to look for
the
soft return, return the position of the return and place it in the
"Left"
function, but I can not figure out how to search for the soft return.

Any suggestions?

--
Lila

--

Dave Peterson

--

Dave Peterson

limit characters returned a cell reference
 

You could use another cell to hold that value. And then use that cell in the
larger formula.

I use that technique when I build longer formulas.

Then if I used a helper cell (say A2) in my formula, I'll steal the formula for
A2 in the formula bar (don't include the = sign and maybe add ()'s around it).

Then select the cell with my "real" formula.

Everywhere I see A2, I'll replace with my copied formula.

I think John Walkenbach describes the same approach when building what he calls
Monster Formulas (in a couple of his books).

Lila wrote:

Oh wait... I answered my own question. I just placed an actual soft return
in quotes in the Search function. It looks a bit odd, but it works!

=LEFT($E5,(SEARCH("
",E5)))

Now I just have to figure out if there is a way to nest it in an "index" and
"match" formula.

"Lila" wrote in message
...
I have a column that has several lines of text in it. Each row of text has
a
soft return (Alt-Enter) to force certain text to go to the next line.

Now I want to do a cell reference that only shows the text on the first
row.
I found the "Right", "Left" and "Mid" function, but those seem to only be
able to limit by the number of characters and the number of characters in
the first row of text varies greatly from cell to cell.

I thought I could combine the "Search" or "Find" function to look for the
soft return, return the position of the return and place it in the "Left"
function, but I can not figure out how to search for the soft return.

Any suggestions?

--
Lila



--

Dave Peterson

limit characters returned a cell reference
 

Yep, that's what I did. I made one column that had the Left, Search
function, then another that had the Index, Match formula. I just like to
limit the amount of "dummy" data laying around (i.e. the column that has the
Left, Search function) because it looks so untidy... and so that people who
don't know what's going on will not accidentally delete it and ruin
everything.

In this workbook I actually have a sheet for all of the "functions" or stray
data like this, but because of the way the Index, Match formula works,
having the Left, Search function on a different sheet wouldn't work.

Of course that leads to "locking" etc. The problem with that is that this is
an inventory where people need to be able to add information to the bottom
of the spreadsheet so they need to "copy" the row and "paste" it so that all
of the formulas etc go with it and when the sheet is locked... it won't
allow copy and paste. Unless I'm missing something???

Lila

"Dave Peterson" wrote in message
...
You could use another cell to hold that value. And then use that cell in
the
larger formula.

I use that technique when I build longer formulas.

Then if I used a helper cell (say A2) in my formula, I'll steal the
formula for
A2 in the formula bar (don't include the = sign and maybe add ()'s around
it).

Then select the cell with my "real" formula.

Everywhere I see A2, I'll replace with my copied formula.

I think John Walkenbach describes the same approach when building what he
calls
Monster Formulas (in a couple of his books).

Lila wrote:

Oh wait... I answered my own question. I just placed an actual soft
return
in quotes in the Search function. It looks a bit odd, but it works!

=LEFT($E5,(SEARCH("
",E5)))

Now I just have to figure out if there is a way to nest it in an "index"
and
"match" formula.

"Lila" wrote in message
...
I have a column that has several lines of text in it. Each row of text
has
a
soft return (Alt-Enter) to force certain text to go to the next line.

Now I want to do a cell reference that only shows the text on the
first
row.
I found the "Right", "Left" and "Mid" function, but those seem to only
be
able to limit by the number of characters and the number of characters
in
the first row of text varies greatly from cell to cell.

I thought I could combine the "Search" or "Find" function to look for
the
soft return, return the position of the return and place it in the
"Left"
function, but I can not figure out how to search for the soft return.

Any suggestions?

--
Lila



--

Dave Peterson

limit characters returned a cell reference
 

Maybe you could provide a macro that would unprotect the worksheet, do the
insert (and copy over formulas), then reprotect the worksheet.

David McRitchie has some code to insert rows and copy formulas at:
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
look for: InsertRowsAndFillFormulas

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm



Lila wrote:

Yep, that's what I did. I made one column that had the Left, Search
function, then another that had the Index, Match formula. I just like to
limit the amount of "dummy" data laying around (i.e. the column that has the
Left, Search function) because it looks so untidy... and so that people who
don't know what's going on will not accidentally delete it and ruin
everything.

In this workbook I actually have a sheet for all of the "functions" or stray
data like this, but because of the way the Index, Match formula works,
having the Left, Search function on a different sheet wouldn't work.

Of course that leads to "locking" etc. The problem with that is that this is
an inventory where people need to be able to add information to the bottom
of the spreadsheet so they need to "copy" the row and "paste" it so that all
of the formulas etc go with it and when the sheet is locked... it won't
allow copy and paste. Unless I'm missing something???

Lila

"Dave Peterson" wrote in message
...
You could use another cell to hold that value. And then use that cell in
the
larger formula.

I use that technique when I build longer formulas.

Then if I used a helper cell (say A2) in my formula, I'll steal the
formula for
A2 in the formula bar (don't include the = sign and maybe add ()'s around
it).

Then select the cell with my "real" formula.

Everywhere I see A2, I'll replace with my copied formula.

I think John Walkenbach describes the same approach when building what he
calls
Monster Formulas (in a couple of his books).

Lila wrote:

Oh wait... I answered my own question. I just placed an actual soft
return
in quotes in the Search function. It looks a bit odd, but it works!

=LEFT($E5,(SEARCH("
",E5)))

Now I just have to figure out if there is a way to nest it in an "index"
and
"match" formula.

"Lila" wrote in message
...
I have a column that has several lines of text in it. Each row of text
has
a
soft return (Alt-Enter) to force certain text to go to the next line.

Now I want to do a cell reference that only shows the text on the
first
row.
I found the "Right", "Left" and "Mid" function, but those seem to only
be
able to limit by the number of characters and the number of characters
in
the first row of text varies greatly from cell to cell.

I thought I could combine the "Search" or "Find" function to look for
the
soft return, return the position of the return and place it in the
"Left"
function, but I can not figure out how to search for the soft return.

Any suggestions?

--
Lila



--

Dave Peterson

--

Dave Peterson