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

| 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]

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

| 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

message |
|
| "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
| >
message
| > | > | 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.