How do I limit what text is displayed?

[Erik Millerd]

I have a cell linked to an Access database field. This field contains carrage
returns. I want to display only the data before the 1st carrage return. Is
there a way to do this? Perhaps useing the LEN, LEFT and other functions?
BTW this is in Excle and Access 2000.

[ScottO]

If the text you wanted to strip down was in A1, then you could use a formula
like
=LEFT(A1,FIND(CHAR(10),A1)-1)
You may be able to adjust the formula to suit your need.

BTW Char(10) is the equivalent of a shift+enter in Excel.

Rgds,
ScottO

"Erik Millerd" <[email protected]> wrote in message
news:[email protected]...
| I have a cell linked to an Access database field. This field contains
carrage
| returns. I want to display only the data before the 1st carrage return. Is
| there a way to do this? Perhaps useing the LEN, LEFT and other functions?
| BTW this is in Excle and Access 2000.

[Erik Millerd]

"ScottO" wrote:

> If the text you wanted to strip down was in A1, then you could use a formula
> like
> =LEFT(A1,FIND(CHAR(10),A1)-1)
> You may be able to adjust the formula to suit your need.
>
> BTW Char(10) is the equivalent of a shift+enter in Excel.
>
> Rgds,
> ScottO
>
> "Erik Millerd" <[email protected]> wrote in message
> news:[email protected]...
> | I have a cell linked to an Access database field. This field contains
> carrage
> | returns. I want to display only the data before the 1st carrage return. Is
> | there a way to do this? Perhaps useing the LEN, LEFT and other functions?
> | BTW this is in Excle and Access 2000.
>
>
> Thanks Scott!!
I have one other question. I put this in cell A2. This works great, but if
A1 does not have a CHR(10) a #Value error is returned. I think that an IF
function will work to say that IF A1 has a CHR(10) then perform the above
function, else , just = A1. Is this correct?

[ScottO]

Try this ...
=IF(ISERROR(FIND(CHAR(10),A1)),A1,LEFT(A1,FIND(CHAR(10),A1)-1))
Which in English translates (loosely) as "If you can't find a
Carriage Return in the text, then give me the text, otherwise find
where the Carriage Return is and give me the part of the text before
it".

Rgds,
ScottO

"Erik Millerd" <[email protected]> wrote in
message news:[email protected]...
|
|
| "ScottO" wrote:
|
| > If the text you wanted to strip down was in A1, then you could
use a formula
| > like
| > =LEFT(A1,FIND(CHAR(10),A1)-1)
| > You may be able to adjust the formula to suit your need.
| >
| > BTW Char(10) is the equivalent of a shift+enter in Excel.
| >
| > Rgds,
| > ScottO
| >
| > "Erik Millerd" <[email protected]> wrote in
message
| > news:[email protected]...
| > | I have a cell linked to an Access database field. This field
contains
| > carrage
| > | returns. I want to display only the data before the 1st carrage
return. Is
| > | there a way to do this? Perhaps useing the LEN, LEFT and other
functions?
| > | BTW this is in Excle and Access 2000.
| >
| >
| > Thanks Scott!!
| I have one other question. I put this in cell A2. This works great,
but if
| A1 does not have a CHR(10) a #Value error is returned. I think that
an IF
| function will work to say that IF A1 has a CHR(10) then perform the
above
| function, else , just = A1. Is this correct?

[Erik Millerd]

Thank you very much!!
I would never have figured that out as I haven't seen the ISERROR before.