Ron

That's brilliant - i will give it a go

Thanks

Nev


"Ron Rosenfeld" wrote:

On Sun, 24 May 2009 01:57:04 -0700, Nev wrote:

That's great Thanks. If I wanted to get the 1st two line or second line
only, how would that work?

Thanks

Nev

Glad to help.

For the 1st two lines, you need to look for the second <lf character. The
SUBSTITUTE worksheet function allows you to specify the instance of a
character.

So =SUBSTITUTE(A1,CHAR(10),CHAR(1),2) would substitute a CHAR(1) for the <lf.
Then you just look for that:

=LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1,CHAR(10),CHAR( 1),2))-1)

To get just the second line, you use the MID function and go from the first
<lf to your substituted CHAR(1).

So something like:

=MID(A1,FIND(CHAR(10),A1)+1,FIND(CHAR(1),
SUBSTITUTE(A1,CHAR(10),CHAR(1),2))-FIND(CHAR(10),A1)-1)

I find it simpler to use either Longre's free morefunc.xll add-in and the
regular expression formula:

=REGEX.MID(A1,".+",<LineNumber)

for work like this.

The .xll will not work on strings longer than 255 characters, in which case I
would use a "home grown" VBA routine that does pretty much the same thing.

--ron